#1

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.

(Win 10)

  • Find sqlite database
C:\Users\(USERNAME)\AppData\Local\Packages\Microsoft.MicrosoftStickyNotes_8wekyb3d8bbwe\LocalState
  • Copy plum.db to temp folder
  • Install SQLiteStudio
  • Load plum.db
  • Enter script in function editor
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)
  • Copy output files to vault

Hat tip Googie for their answer to this 7 year old stackoverflow question.

3 Likes
#2

Happy with how this turned out. Learned a lot along the way:

  • Brushed up on SQL
  • How to parse JSON inside a SQL table
  • How to convert LDAP time to unix time
    • (found a bug in the ms time implementation too)
  • Tracked down a tcl script for sqlite to spit out files
#3

I’m cross-posting this to Meta - Migration Workflows thanks!

1 Like
Meta - Migration Workflows
#4

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?

#5

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.

#6

error 2022-01-31 084501
error 2022-01-31 0845011158×1090 15.3 KB

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,

#7

If you do

SELECT *

in sqlite, do the proper characters show up?

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.

#8

You can also try this

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.