Importing externally created schedule from a text file

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…

I also posted this question here:

The ReMemorize add-on might be helpful.

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.

1 Like

Thank you a lot. Could you tell which format to use for imported dates, so I could import the Due dates into a custom field?

I assume it is YYYY-MM-DD, like 2020-08-29

It’s epoch time with seconds. If you don’t want a specific time you can use the midnight as reference.

1 Like

Thank you. I used the formula from this answer to convert the dates from google spreadsheet format into Epoch time:

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


1 Like

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?

My Anki is 2.1.32

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.

Done. You can download the deck here.

I recorded the process (38 minutes). I may ask you for permission to share the video publicly, otherwise I will send the video privately.


Thank you very much! You can publish the video for all to see.

1 Like

Here is the video:


Hi Guillem!

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 used the following articles the learn the database structure:

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]
-- [2]
-- [3]
-- [4]
-- [5]
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
		substr(str, 0, instr(str, char(0x1f))),
		substr(str, instr(str, char(0x1f))+1),
		FROM split WHERE str!=''
	SELECT 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

1 Like

Absolutely brilliant ! Just 1 day off is pretty good, perhaps that is due to leap years. I am not that versed in SQL to end with such a query.

You can paste the solution in the subredit post as well, for future google hits.

Note is time to enjoy Anki :wink:


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.