This is a continuation from this post about creating your own ANKI SRS card deck for learning a foreign language. The next part is here

Step 5. Create the flashcard data

** UPDATE: I created an open-source app for Mac/Windows that downloads the sounds without having to write and execute scripts. The app is available here. It greatly simplifies the process of downloading the voice files and creating data for ANKI. If you are interested in making flashcard data, I highly recommend looking at that app rather than doing the steps here in google Spreadsheet.

Before going any further we should look over what structure the cards will be. This relates to what objectives are required. My personal objectives are as follows.
1. Be able to pronounce a word in the foreign language
2. Be able to write the pinyin for the new word and select the correct character from a list (writing on a computer or mobile phone)
3. Be able to recognise the word for reading
4. Be able to recognise the word for listening.

To complete these objectives, I decided upon the Basic card (Front facing plus Back facing) with the following contents for 3 types of flash cards:


Card Type 1. English -> Chinese 2. Chinese -> English 3. Listening
Front Face English word Chinese Traditional
Chinese Simplified
“LISTEN” text
Play sound
Back Face Pinyin
Chinese Traditional
Chinese Simplified
Play sound
Play sound
Chinese Traditional
Chinese Simplified
Play sound


Each word in the vocabulary list will equate to 3 flashcards. Each card will relate to the same mp3 file played. The sound should be played every time the back face is revealed, but only on the front face too if its a listening card.

I used the csv import tool in ANKI 2.0 so we need to make csv files. This is done by creating cell entries in the spreadsheet.

here is CSV data of an example of 3 flashcards relating to one vocabulary. This will ultimately end up in one cell in the spreadsheet.

Elevator ;[sound:電梯.mp3] 電梯<BR>电梯<BR>Diàntī
Listen [sound:電梯.mp3];電梯<BR>Diàntī<BR>Elevator [sound:電梯.mp3]

Each line relates to one card. instead of Commas separating the values as the CSV name suggests, I am using a semicolon (;) Each line has a semicolon, on the left side is the front face data, on the right is the back face. a carriage return (new line) represents a different card.
<BR> is HTML code for breakline, or new line. ANKI allows HTML code in the cards so multi-line cards are allowed. Different fonts and colours can also be used, if required, I chose to stick with the defaults.
Finally, the [sound:電梯.mp3] text is included. This is is a tag that ANKI recognises to play a media file from its collection. When viewing the card in the application, this text is hidden.

Go to the spreadsheet and make a new column title CSV. Go to the next line and fill in the following formula:

=CONCATENATE(A2," ;", "[sound:",E2,".mp3] ",C2,"<BR>",D2,"<BR>",B2, CHAR(10), C2,"<BR>",D2,";",B2,"<BR>",A2, "[sound:",E2,".mp3]", CHAR(10)," Listen"," [sound:",E2,".mp3];",C2,"<BR>",B2,"<BR>",A2," [sound:",F2,".mp3] ")

This appears quite complex, but lets break down what’s happening here. The CONCATENATE command appends text strings together to create a final text string for the cell. we are using the cells from the same row to build the string. you can also see CHAR(10). This is a carriage return code that adds a new line inside the cell text.

After validating this cell contents, fill down the cells in this column to every vocabulary in your list.

This data can now be copied out into a file for ANKI to read in. Highlight the CSV cells and copy it. Open up a new file in TextWrangler and enter the following header


Now paste CSV data in to the file. Save this file as ankidata.txt
The first line is needed as a template for ANKI. This tells anki that the rest of the data will only use 2 fields per line. you could add more for different fields later, but for now I’ll keep it simple

I found that a whole load of extra speech marks appeared when I copied the cell data to Text Wrangler. They can be removed by selecting all the text then doing a search and replace for ” to empty text.

Now you have created the CSV text data that ANKI can read. The final step is to import this into ANKI and build the new deck.


The next part is here.

Share Button