Fixing Knowclip .apkg files: one more thing

Anki

(N.B. A much-improved version of this script is published in a later post)

Fixing the Knowclip note files as I described previously, it turns out, is only half of the fix with the broken .apkg files. You also need to fix the cards table. Why? Same reason. The rows are number sequentially from 1. But since Anki uses the card id field as the date added, the added field is always wrong. Again, the fix is simple:

#!/usr/bin/env python3

import sqlite3
import time
import datetime

db_path = '/path/to/knowclip/generated/apkg/collection.anki2'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

q = 'UPDATE cards SET id = nid + 10'
cursor.execute(q)
conn.commit()
conn.close()

Here’s an improved version of the previous script, one that incorporates changes to both the notes and the cards tables.

#!/usr/bin/env python3

import sqlite3
import time
import datetime

# start date for new cards
dt = input("Creation date 2021-04-05: ")
(year,month,day) = [int(x) for x in dt.split('-')]
tm = input("Creation time 05:00: ")
(hr, minute) = [int(x) for x in tm.split(':')]
new_date = datetime.datetime(year, month, day, hr, minute)
new_epoch = int(new_date.timestamp() * 1000)
print(f'Will begin create date with epoch ms {new_epoch}.')

db_path = '/path/to/knowclip/generated/apkg/collection.anki2'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
q = 'SELECT id FROM notes'
cursor.execute(q)
note_rows = cursor.fetchall()
note_chg_sql_cmds = []
for note_row in note_rows:
	qn = f'UPDATE notes SET id = {new_epoch} WHERE id = {note_row[0]}'
	note_chg_sql_cmds.append(qn)
	qc = f'UPDATE cards SET nid = {new_epoch} WHERE nid = {note_row[0]}'
	new_epoch = new_epoch + 100
	cursor.execute(qc)
conn.commit()
qc = f'UPDATE cards SET id = nid +10'
cursor.execute(qc)
conn.commit()
for sql in note_chg_sql_cmds:
	cursor.execute(sql)


conn.commit()
conn.close()

Fixing Knowclip Anki apkg creation dates

Anki

(N.B. A much-improved version of this script is published in a later post)

Language learners who want to develop their listening comprehension skills often turn to YouTube for videos that feature native language content. Often these videos have subtitles in the original language. A handful of applications allow users to take these videos along with their subtitles and chop them up into sentence-length bites that are suitable for Anki cards. Once such application is Knowclip. Indeed for macOS users, it’s one of the few viable options.1

As expected, as of version 0.10.2-beta, it has a few rough edges, one of which is that notes it generates have the incorrect date. All of the creation dates are 1969-12-31, which of course is the zero date in the Unix epoch date-keeping world. As far as I can tell, it doesn’t cause in problems once the Knowclip-generated apkg file is imported into Anki, but it’s an irritating bug.

To fix this issue, it’s important to recognize that an apkg file is just a regular zip file in disguise. So the first step is to rename the file that Knowclip generates to something.zip, then decompress it. Inside the decompressed directory, you’ll see a number of file, including a collection.anki2 file. That’s the SQLite file we’ll be targeting.

We’ll need to correct both the notes and cards tables. For convenience, we can do this in a script:

#!/usr/bin/env python3

import sqlite3
import time
import datetime

# start date for new cards
new_date = datetime.datetime(2021,4,5,5,0)
new_epoch = int(new_date.timestamp() * 1000)
print(f'Will begin create date with epoch ms {new_epoch}.')

db_path = '/path/to/knowclip/generated/apkg/collection.anki2'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
q = 'SELECT id FROM notes'
cursor.execute(q)
note_rows = cursor.fetchall()
note_chg_sql_cmds = []
for note_row in note_rows:
	qn = f'UPDATE notes SET id = {new_epoch} WHERE id = {note_row[0]}'
	note_chg_sql_cmds.append(qn)
	qc = f'UPDATE cards SET nid = {new_epoch} WHERE nid = {note_row[0]}'
	new_epoch = new_epoch + 100
	cursor.execute(qc)
conn.commit()
for sql in note_chg_sql_cmds:
	cursor.execute(sql)
conn.commit()
conn.close()

Just run this script against the collection.anki2 file before importing, and you’ll have the correct date inside of Anki.

  1. subs2srs is frequently featured and offers similar features, but it Windows-only. ↩︎