Just puzzled through this over the past few days and finally figured it out. Going to share the barebones instructions here and if there is any interest, I can go into more details later.
if {[catch {
lassign $argv fileName contents
set fd [open "C:/tmp/a/$fileName" a+]
puts $fd $contents
close $fd
} res]} {
return "error: $res"
} else {
return "ok"
}
Create dir c:\tmp\a
Run query:
select saveToFile(timestamp || '.md',text) as res from
(select createdat, strftime('%Y-%m-%d %H.%M.%S',((CreatedAt-504911232000000000)/10000000)-11644473600,'unixepoch') as timestamp, group_concat(value,char(10)) as text
from Note, json_tree("Note".lastServerVersion, '$.document') as tree
where key='text'
group by createdat)
error…
I successfully exported English characters, but they were repeated three times, and the timestamp was lost, and the other languages were garbled, could you give me some advice?
Gladly!
I also ran into the ‘repeated three times’ thing and I gave up on troubleshooting it so I just added a
‘’’
LIMIT 165
‘’’
to the end of the SQL query (where I knew I had 165 notes).
I think you should be able to just run
‘’’
SELECT *
‘’’
by itself to see how many notes you have.
In retrospect, I think the repeating may be because of characters in notes that don’t escape properly, but it wasn’t worth fully troubleshooting.
Can you elaborate more on the timestamps piece? I’m away from my laptop at the moment but I can dig back into my notes a little later and share more specifics of the process.
Thanks you, its output is like this,
Non-alphabetic data are just spitting out “?”
(about timestamps is my description problem, the script is not a problem, it performs the function,)
I don’t know how to get sqlite to spit out the file in the appropriate chartset,
Clearly sticky notes is storing them right, so I wonder at which point they aren’t being converted properly.
I should be able to give you a few sample queries to help pinpoint the problem.
select CreatedAt, group_concat(value,char(10))
from Note, json_tree("Note".lastServerVersion, '$.document') as tree
where key='text'
group by createdat
to see if those characters are surviving the json parsing.