I have been doing space repetitions manually using a google spreadsheet. I have columns “Current Interval” and “Next repetition date” and I calculated next repetition dates manually by primitive formulas of my own. Now I have imported my material into Anki, but not the scheduling information. I would like to be able to import at least the next repetition dates, i.e. to reschedule each question in the Anki deck according to my spreadsheet’s column.
I need this to avoid thousands of unnecessary repetitions of the material I already know. Manual rescheduling is too much work for me. I hoped there was a feature or add-on to do it automatically, in bulk…
Thank you, but unless it could reschedule in bulk on the basis of values of a custom field, with dates imported from external spreadsheet, I do not see how I can use it…
you could accomplish setting the corresponding due date for each card with precision of date and time by editing the database externally with a single SQL query if you have the due date as a field. It’s not strictly the best apprach, as you will have young or mature cards with zero reviews and you may see weird graphs or stats until you review the cards at least once.
Feel free to DM me with your spreedsheet or collection.anki2 file and I’d do it for you.
Thank you very much. It should work. I have to repeat it for several people who use the same collection. I could send you the files but I am sure I could do it myself. If you gave me links/instructions how to execute SQL queries, what tables and fields to change, etc, it would help to speed up my work. It would be nice if you told me also the format for dates to use in the imported file.
Ok, then I think the best solution is recording a video guide with a sample (dummy) spreadsheet. So privacy issues involved, as queries and stuff will be the same. I will reply here once I am done, let’s say within a week.
Once I have the dates and intervals imported into custom Anki fields, I can send my collection to you, if it helps to you to prepare the instructions, so I can repeat the procedure for me and for the other users of this collection
Hi,
I imported the scheduling data into the following fields: importedDueDate - the date of the next repetition in Linux epoch time format; importedInterval - the current interval of repetitions in days; importedPosition - the position in the queue of new cards, integer number.
The note type is “Baby Sign”.
Then I exported my collection into file named collection-2020-08-30@15-42-08.colpkg.
I unchecked the option “include media” when I was doing the export. Should I have checked it?
I believe it is enough to export only one deck, not the whole collection. So I exported only the deck I need to reschedule. Here is the collection.anki2 file of that deck.
Meanwhile I will try to make the changes myself. I would do it much faster if I knew what changes I need to make in terms of tables and fields…
For now I managed to open the collection.anki2 file with sqlite and start sqllite command line.
Thank you very much for the help you already provided.
I did it slightly differently than that on the video. Instead of using Excel I decided to go the hard way and wrote an SQL query to do the job. Another difference is that I imported the due dates, which were in the past (overdue dates) as they are. So they stayed overdue for the same amount after the import as they were before the import. Some dates still changed after the import, but not much, the maximum error was ±1 day (see the second screenshot). I assumed it is because of truncating partial days and/or because Anki starts its day at some other time than midnight.
I posted also a relevant answer on a question about SQLite, which I reused to write the SQL query:
Below there is the SQL query I wrote to update the schedule and some screenshots confirming the results of the import.
I would like to credit @guillempalausalva for his very helpful hints and video. The only thing I did was to write the query, but the whole process was described in the video. I am new to Anki and without @guillempalausalva’s help I would have been stuck countless times, without knowing tens of small know hows and trying to figure out how to do a simplest thing.
-- This code updates scheduling data in my collection with imported external schedule
-- References:
-- [1] https://forums.ankiweb.net/t/importing-externally-created-schedule-from-a-text-file/2767/17
-- [2] https://www.juliensobczak.com/write/2016/12/26/anki-scripting.html
-- [3] https://github.com/ankidroid/Anki-Android/wiki/Database-Structure-
-- [4] https://www.vivekkalyan.com/splitting-comma-seperated-fields-sqlite
-- [5] https://stackoverflow.com/questions/39648820/how-to-split-delimited-values-in-a-sqlite-column-into-multiple-columns/63766791#63766791
--
WITH importedNoteData(nid,WordEng,iDue_e,iInt,iPos,crt,iDue_d) as (
--------------------------------------------------
-- Select my imported fields by splitting flds field values into separate columns
-- Inspired by [4],[5]
WITH RECURSIVE split(id, flds, str, myColNum) AS (
SELECT id, '', flds||char(0x1f),0 FROM notes
UNION ALL SELECT
id,
substr(str, 0, instr(str, char(0x1f))),
substr(str, instr(str, char(0x1f))+1),
myColNum+1
FROM split WHERE str!=''
)
SELECT notes.id as nid,
-- The custom fields from my "Baby sign" notes, imported by me previously {
WordEng, -- word in english
iDue_e, -- ImportedDueDate in Linux epoch seconds,
iInt, -- importedInterval
iPos, -- importedPosition
-- }
crt, -- collection creation time [2],[3]
(iDue_e - crt)/(60*60*24) as iDue_d -- importedDueDate, converted in days since crt
FROM notes,col
LEFT JOIN (SELECT id, flds AS WordEng FROM split WHERE myColNum=3) as split3 USING(id)
LEFT JOIN (SELECT id, flds AS iDue_e FROM split WHERE myColNum=7) as split7 USING(id)
LEFT JOIN (SELECT id, flds AS iInt FROM split WHERE myColNum=8) as split8 USING(id)
LEFT JOIN (SELECT id, flds AS iPos FROM split WHERE myColNum=9) as split9 USING(id)
)
update cards
SET due = (SELECT iDue_d FROM importedNoteData WHERE importedNoteData.nid = cards.nid),
ivl = (SELECT iInt FROM importedNoteData WHERE importedNoteData.nid = cards.nid);
-- SELECT * from importedNoteData
-- ORDER BY CAST(iPos AS NUMBER)
Hi Guillem, Your video appears to have been removed. Is it possible to reinstate it? I sense that you might have attempted the external schedule import using Excel. If so, this may be the more appealing process for those not inclined to learn the SQLite approach.
