Using Microsoft Excel to Import Into Anki

What is Anki?

http://ankisrs.net/

“Anki is a program which makes remembering things easy. Because it’s a lot more efficient than traditional study methods, you can either greatly decrease your time spent studying, or greatly increase the amount you learn.”

The program is just under 23 MB in size. Download and install…then download an Anki Deck.

Where can I get Anki Decks in French?

Decks can be very large (i.e. 100 MB) if they contain audio, and well worth downloading. Double-click the Deck in Explorer and the Anki program will pick it up and process it.

https://ankiweb.net/shared/decks/french

What if I want to convert my Excel spreadsheet to an Anki Deck?

Big Picture: You convert from Excel via text files, run through Notepad to convert from UTF-16 to the UTF-8 format that is used by Anki. You do the opposite when you go the other way from Anki to Excel.

Personally, I prefer Excel, but one of the great things about Anki Decks is that you can convert it into Excel in minutes. You just have to know one thing:

Anki encodes in UTF-8, and Microsoft Excel encodes files as UTF-16. For those that need to know what the difference between these two formats are, go here:

http://stackoverflow.com/questions/496321/utf8-utf16-and-utf32

Otherwise, all that you need to know is that you will need a program that converts from Excel’s UTF-16 to Anki’s UTF-8, and visa-versa….and that program is one you probably already have. It’s Notepad.

This process will walk you through being able to make/edit files in Excel and then getting that file into a format that can be imported into Anki.

What you need:

  • Microsoft Excel
  • Notepad (it is a standard installation with Windows.  NOTE:  Not Wordpad)
  • (If you are modifying an existing Anki deck, then you need….) Your Anki deck in a *.txt file.

Using Excel to modify an existing deck:

There are some situations where you may want to use Excel to modify an existing deck.  To do this you must first export your deck as a .txt file.  You do this by:

  • From the Decks screen in Anki.  (File > Export).
  • For the export format, select “Notes in Plain Text.”
  • Select the deck you want.
  • Decide if you want a column for tags or not.  When modifying an existing deck you generally want to keep tags.
  • This will export the deck onto your desktop as a .txt file.

To open it in Excel.

  • First, open Excel.
  • Then File > Open (browse for the file).
  • To find the file, you have to allow Excel to be able to open *.txt files.  There is drop down menu where you need to change from “All Excel Files” to “Text Files (*.prn, *.txt, *.csv)”
  • When you find the file, select it and open it.
  • This will open a Text Import Wizard.  The questions asked by the importer depend on the data in your file, but here are some common questions.
    • You want the “Delimited” option checked.
    • For “File Origin” you need to have “65001: Unicode (UTF-8)”
    • Click “Next”
    • The Delimiter is a “Tab”
    • Text qualifier is a quote mark ( “ )
    • Treat consecutive delimiters as one should NOT be checked.
    • Click “Next”
    • Column data format should be “Text” NOT “General”
    • Click “Finish”
  • Then you are able to edit/modify/add to the file. (You will often need to adjust the column width to be able to best view your data).
  • Realize that each column represents one of the fields from your note type.  So, if you have two fields, you’ll have two columns, plus the third one for tags.
  • When you are done editing and want to import back into Anki, jump to the “Importing an Excel File into Anki” section below.

Using a new/existing Excel file, to import new data into Anki:

Sometimes you might have an existing spreadsheet, full of data, that you want to turn into Anki flashcards. OR you might want to create new material, but for whatever reason, you might feel you can create the data more quickly in a spreadsheet.

In either situation, you need to:

  • Remember that each column will be mapped to a “field” to your Anki Note type.  So make sure that each column has the same type of data in it.
    • For example, the first column might be a column for an English Word.  The second column might be for the same word in a second language.  You could also have additional columns for sample sentences, links for audio files, etc.
  • I find it easiest if I have already designed the Note Type in Anki.  In that Note Type, I will set up the fields that I want.  Then when I import from the text file, I have a Note Type that I can import into.  I can easily map the columns to the fields.
  • When you are done editing and want to import into Anki, then read the next section.

Exporting an Excel File and Importing it into Anki:

When your file is ready, and you want to import it into Anki, you have to carefully do these steps.

  • In Excel, File > Save As (look for “other formats” and from the “Save as type” dropdown menu, select “Unicode Text  *.txt”
  • I usually save the file to my desktop because it’s easy to find that *.txt file
  • Then open Notepad (a different program found within Windows)
  • File > Open
  • Then File > Save As
  • You MUST change the “Encoding” on the dropdown menu from “Unicode” using the dropdown menu to “UTF-8” (it’s usually all the way on the bottom)
  • Click “Save”
  • Your file is now in “UTF-8” format and ready to import.

Important:  If you are including html in your import, there are some other steps that you need to do to make sure your data is imported properly, but that is different than an “UTF-8” issue.  Information about this and other things can be found at:

http://ankisrs.net/docs/manual.html#importing.

I recommend that you read that section before importing even simple files.

Big Picture: You convert from Excel via text files, run through Notepad to convert from UTF-16 to the UTF-8 format that is used by Anki. You do the opposite when you go the other way from Anki to Excel.

Flashcards are a great tool for language learning, but if they are done well, they are even better. How do you make a good flashcard?

http://fluent-forever.com/create-better-flashcards/#.U-5lPfldW8E

Others show you how to do things well…like Mac Users with Anki:

http://fluent-forever.com/the-method/spaced-repetition/#.U-5k__ldW8E

Advertisements

6 thoughts on “Using Microsoft Excel to Import Into Anki

  1. Michael Givan January 3, 2015 / 11:56 am

    I had no luck with conversion when coming from the OSX version of Word. Instead, the note cards I used via Cram.com were exported to Google Drive and from there downloaded as a CSV with perfect compatibility with Anki. Cram has a great database but does not offer the spaced repetition of Anki; this solution gives me the best of both worlds.

  2. Saso Kuncic (@sasokuncic) January 14, 2015 / 8:35 am

    I’m attending Learn How to Learn MOOC on Coursera and found Anki as useful tool to remember more. This post is really useful to help me create my own decks.
    Thank you.

    PS1 Could you provide a simple csv sample document (or just reference to the location on the internet)?
    PS2 Did you ever use Google Sheets to create Anki deck?

  3. Mose — Tipsy Pilgrim March 6, 2015 / 7:24 am

    I used google spreadsheets and downloaded the file as a “comma-separated values” file; I then imported it to Anki with absolutely no problem. Just took a minute or two total. Now that I realized how easy this is, I’ll probably create all of my new cards as google spreadsheets first.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s