Merging Anki notes and keeping scheduling data
Since learning about Anki’s selective card generation, I want to merge all the “definition” notes that I have with the “translation” notes for the same Spanish terms. I don’t want to lose the scheduling – the learning history – that I’ve already built up for the existing cards.
Anki doesn’t have a a built-in way to support this.
Samuel Allain already published a solution for merging notes and keeping historical data on the Anki forum. It’s quite technical, working directly with the Anki database and its debug console. Here I explore the ideas discussed there to figure out how to do it for myself.
Take Anki offline
Close Anki’s session with Ankiweb before doing anything else. This disables automatic synchronization, meaning any mistakes you make won’t be propagated. We can enable it again later by giving Anki the username and password again.
Make a backup and a working copy
Make a backup copy of the Anki data files. We won’t touch this again unless something goes badly wrong.
cp -r ~/.local/share/Anki2 ~/tmp mv ~/tmp/Anki2 ~/tmp/Anki2Backup
Make another copy to work on. We’ll make changes here and later sync them if it goes well.
cp -r ~/.local/share/Anki2 ~/tmp mv ~/tmp/Anki2 ~/tmp/Working
Launch Anki from the working copy in safe mode. Safe mode stops Anki pushing the changes to AnkiWeb. Without it you might as well keep editing the main local copy.
anki --safemode --base ~/tmp/Working
Open the card explorer and note the card count.
Add a placeholder note like something with “DELETE ME” in the fields.
Open the card explorer and check that the card count has incremented.
Open Anki normally.
Open the card explorer and check that the card count has not incremented here. Thus you have demonstrated that changes to the working copy do not affect the main local copy.
Using the Anki debug console
Launch Anki from the working copy in safe mode.
anki --safemode --base ~/tmp/Working
Open the debug console. On a Spanish keyboard the key combination is CTRL+SHIFT+:.
The debug console is a sort of Python REPL with some objects already in the environment. Unlike the stock Python REPL, you need to use the print function to see the state of an object.
>>> print("Hello, world!"); Hello, world! >>> mw <no output> >>> print(dir()) ['bcard', 'card', 'frm', 'mw', 'pp', 'pprint', 'self', 'text', 'traceback']
Refer to the source code to find out what the objects and types are.
Confirm we are using the working copy.
>>> print(mw.col.path) /home/isme/tmp/Working/Usuario 1/collection.anki2
But what are we actually looking for in the database? It’s not very comfortable to query it in the debug console, so let’s try somthing else.
Inspecting the Anki data
Anki has a concept of user profiles to store notes per user. I have just one user automatically called “Usuario 1”.
cd ~/tmp/Working/Usuario\ 1/
collection.anki2 is the main sqlite database. You can query it using the sqlite CLI.
$ sqlite3 file:collection.anki2?mode=ro SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .tables FIELDS config notes templates android_metadata deck_config notetypes cards decks revlog col graves tags
The note type ID for the definitions is
sqlite> .mode line sqlite> SELECT * FROM notetypes; [...] id = 1612340248158 name = Definiciones españolas [...]
You have to read all the data to get that. It’s not possible to filter the rows because of a collation error.
sqlite> SELECT * FROM notetypes WHERE name = ''; Error: no such collation sequence: unicase
Filter the notes by type using the mid column.
sqlite> SELECT * FROM notes WHERE mid = 1612340248158 LIMIT 3; id = 1611903132404 guid = Brv^C mid = 1612340248158 mod = 1614030977 usn = 0 tags = una_ola_de_solidaridad flds = pueblo Potemkinexpresión referida a algo muy bien presentado que disimula su desastroso estado real sfld = pueblo Potemkin csum = 4260017649 flags = 0 data = id = 1612340404947 guid = pkE8$`Q=h/ mid = 1612340248158 mod = 1614030977 usn = 0 tags = meteorología flds = anticiclónUn <b>anticiclón</b> es una zona amosférica de alta presión, en la cual la presión atmosférica es superior a la del aire que lo rodea. sfld = anticiclón csum = 1997138271 flags = 0 data = id = 1612451226547 guid = ]2mv1 mid = 1612340248158 mod = 1614030977 usn = 0 tags = flds = echar un kikitener sexo improvisadohttps://coolturize.com/origen-expresion-echar-un-polvo sfld = echar un kiki csum = 1364802529 flags = 0 data =
All the fields are stored in a single column called flds. How is it formatted?
See Anki Database Structure documentation.
the values of the fields in this note. separated by 0x1f (31) character.
This makes it quite complex in SQL to copy a single field from the definition note to the main translation note.
Either I can just do that part manually in the explorer (boo!) or find another way to do it using a plugin or another API.
Looks like it can be done using the Advanced Copy Fields add-on.
There are 106 definitions with fields to copy.
sqlite> SELECT COUNT(*) FROM notes WHERE mid = 1612340248158; COUNT(*) = 106
There are also 106 cards with schedules to copy.
sqlite> SELECT COUNT(*) FROM cards WHERE nid IN (SELECT id FROM notes WHERE mid = 1612340248158); COUNT(*) = 106
After making the new cards we need to pair each old and new card to copy the schedule from old to new. Since the original term is the same on each card (or at least it should be!) we can search in the card explorer for that term to see the cards.
You can use a filter like this to find pairs for a term:
("note:Español-Inglés" AND "card:es->en" AND "Término Español:anticiclón") OR ("note:Definiciones españolas" AND "card:Definición" AND "Término:anticiclón")
It turns out that Samuel Allain even developed a plugin for this part too, with the snappy title of Transfer scheduling data from one card to another.
So now it looks like the process is:
- Identify each note pair
- Copy the field from the definition note to the translation note using Advanced Copy Fields add-on.
- Identify the new card pair
- Copy the schedule from the old definition card to the new definition card using the Transfer scheduling data add-on.
- Delete the definition note
Let’s try it on one definition note to see how it works.
Trying it out