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: https://www.juliensobczak.com/write/2016/12/26/anki-scripting.html
https://github.com/ankidroid/Anki-Android/wiki/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] 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)
