How do I export notes (annotation) to my computer?

[kwokhin]

Hello guys, I'm very frustrated as I've been working on exporting my notes to the computer.
http://www.albertopettarin.it/exportnotes.html
I tried the above method but it says:
"provided file is not a valid KoboReader.sqlite db"



http://netlaze.blogspot.hk/2012/10/h...ions-from.html
Then I tried this method. I have all the four files but when I clicked the work.bat, it just flashed and nothing happened.

HOW CAN I EXPORT MY NOTES?
I wouldn't have asked for help if I had not tried to figure it out first. But I really couldn't do it. PLEASE HELP!

[PeterT]

Where did you copy the files from your Kobo too?

[DNSB]

Originally Posted by kwokhin

HOW CAN I EXPORT MY NOTES?
I wouldn't have asked for help if I had not tried to figure it out first. But I really couldn't do it. PLEASE HELP!

I took a quick look at the method you were using and with some minor changes, it worked for me.

work.cmd (same as work.bat, the %~dp0 just points to directory file is run from)

%~dp0sqlite3.exe KoboReader.sqlite <extr_annotations.txt

extr_annotations.txt (modified to insert \r\n instead of rn for new line and use annotations.txt instead of annotations.doc)

.output annotations.txt
.separator \r\n
 SELECT VolumeID, Text, Annotation
 FROM Bookmark;
.exit

I copied sqlite3.exe and the sqlite database from my H2O into the directory and after running, I had the annotations.txt file.

OTOH, my personal preference for extracting annotations which was also somewheres on Mobileread a while back used a single batch file with a rather long command line to do the extract from the database on the Kobo itself, doing some cleanup on the results and leaving them in the same directory as the .cmd file in a file called annotations.html. Basically, just need extr_annotations.cmd and the sqlite3 executable in a directory and the Kobo ereader connected (for me, the internal storage mounts as drive E: so if yours mounts in a different spot, you'll need to change that ( E:\.kobo to whatever:\.kobo ). The pause at the end of the batch file just keeps the DOS box open so I can see any error messages.

extr_annotations.cmd

%~dp0SQLite3.exe "E:\.kobo\KoboReader.sqlite" "select (case when datecreated=maxdate then ('<b><i><u>' || replace(volumeid,'file:///mnt/onboard/','') || '</u></i></b><br />') else '' end), max(datecreated, coalesce(datemodified, 0)), text, coalesce(annotation, '') || '<br />' from ( select *, (select max(datecreated) from bookmark where volumeid=bm.volumeid and (text<>'' or annotation<>'')) maxdate from bookmark bm where (text<>'' or annotation<>'') order by maxdate desc, volumeid, datecreated desc )" > "%~dp0annotations.html"

pause

[kwokhin]

Originally Posted by PeterT

Where did you copy the files from your Kobo too?

I copied my KoboReader.sqlite from .Kobo

[PeterT]

But where did you put them on the PC? The command you have typed says look in the current directory c:\python27 for them.

[kwokhin]

Originally Posted by DNSB

I took a quick look at the method you were using and with some minor changes, it worked for me.

work.cmd (same as work.bat, the %~dp0 just points to directory file is run from)

%~dp0sqlite3.exe KoboReader.sqlite <extr_annotations.txt

extr_annotations.txt (modified to insert \r\n instead of rn for new line and use annotations.txt instead of annotations.doc)

.output annotations.txt
.separator \r\n
 SELECT VolumeID, Text, Annotation
 FROM Bookmark;
.exit

I copied sqlite3.exe and the sqlite database from my H2O into the directory and after running, I had the annotations.txt file.

OTOH, my personal preference for extracting annotations which was also somewheres on Mobileread a while back used a single batch file with a rather long command line to do the extract from the database on the Kobo itself, doing some cleanup on the results and leaving them in the same directory as the .cmd file in a file called annotations.html name. Basically, just need extr_annotations.cmd and the sqlite3 executable in a directory and the Kobo ereader connected (for me, the internal storage mounts as drive E: so if yours mounts in a different spot, you'll need to change that ( E:\.kobo to whatever:\.kobo ). The pause at the end of the batch file just keeps the DOS box open so I can see any error messages.

extr_annotations.cmd

%~dp0SQLite3.exe "E:\.kobo\KoboReader.sqlite" "select (case when datecreated=maxdate then ('<b><i><u>' || replace(volumeid,'file:///mnt/onboard/','') || '</u></i></b><br />') else '' end), max(datecreated, coalesce(datemodified, 0)), text, coalesce(annotation, '') || '<br />' from ( select *, (select max(datecreated) from bookmark where volumeid=bm.volumeid and (text<>'' or annotation<>'')) maxdate from bookmark bm where (text<>'' or annotation<>'') order by maxdate desc, volumeid, datecreated desc )" > "%~dp0annotations.html"

pause

Update:
I shut down my device and turn it on. I followed the steps again and all my notes are exported! Thank you But how do I remove all the |2015-11-29T15:10:28Z| at the beginning of my notes?
================================================== =====
THANK YOU SO MUCH DAVID! The second method works! On my Kobo, I have 202 notes but but I only have 4 notes exported. How so? I am sure I have the latest KoboReader.sqlite as the last modified time is yesterday.
Thanks in advance again!

[kwokhin]

Originally Posted by PeterT

But where did you put them on the PC? The command you have typed says look in the current directory c:\python27 for them.

Update. IT WORKS!!! I don't know why. I just rebooted my device and pull the new(same file size) KoboReader.sqlite and suddenly everything is solved. I'm so gald yet confused haha
================================================== =
I put the KoboReader.sqlite and export_Kobo_notes together in c:\python27.

[kwokhin]

For those who want to export notes but not quite sure how to do it as suggested by David ( it's not that the explanation is unclear, it's just that I have zero experience with this kind of stuff).

1. Go to http://www.sqlite.org/download.html and download the following zip:
(Sorry I only know how to do it for Windows)
Precompiled Binaries for Windows
sqlite-shell-win32-x86-3090200.zip
(364.10 KiB)

2. Unzip it and go to the folder you just unzipped

3. Inside the folder, create a New Text Document (don't rename it yet)

4. Open the New Text Document and copy the following:

%~dp0SQLite3.exe "E:\.kobo\KoboReader.sqlite" "select (case when datecreated=maxdate then ('<b><i><u>' || replace(volumeid,'file:///mnt/onboard/','') || '</u></i></b><br />') else '' end), max(datecreated, coalesce(datemodified, 0)), text, coalesce(annotation, '') || '<br />' from ( select *, (select max(datecreated) from bookmark where volumeid=bm.volumeid and (text<>'' or annotation<>'')) maxdate from bookmark bm where (text<>'' or annotation<>'') order by maxdate desc, volumeid, datecreated desc )" > "%~dp0annotations.html"

pause

[/QUOTE]

Be careful with the directory. Match them as below:


5. Click save as and name it extr_annotations.cmd. Plug in your Kobo device.
Make sure you have the following files. I think the New Text Document is not necessary.

7. click extr_annotations and you're done!!

8. There will be a new annotations file

[DNSB]

Good to hear you got it working.

[kwokhin]

Originally Posted by DNSB

Good to hear you got it working.

Thank you so much for your help!! I really appreciate it!
Just one more question, how do I remove all the |2015-11-29T15:10:28Z| at the beginning of my notes?

[meeera]

Doesn't the Kobo Utilities plugin for Calibre do this?

[DNSB]

Originally Posted by kwokhin

Thank you so much for your help!! I really appreciate it!
Just one more question, how do I remove all the |2015-11-29T15:10:28Z| at the beginning of my notes?

I've never bothered to remove that datestamp since my major use for annotations is correcting errors. If I wanted to, I would probably use Notepad++ with it's regular expression search/replace feature.

As an example to replace a string such as |2015*| in Notepad++, I would use \|2015(.*?)\| as the search string with a blank replace (the backslashes escape the | characters to prevent them being considered special characters).

One note is that I am typing this out of memory. Any possible errors are regretted.

[DNSB]

Originally Posted by meeera

Doesn't the Kobo Utilities plugin for Calibre do this?

The last time I tried the backup annotation file option, I got no books with annotation files despite having just made annotations for the 7 ebooks I was attempting to do the backup for so it appears the annotation files are not created for ebooks on the external uSD card. I don't store books on the internal storage so extraction from the database is the route I take.

[davidfor]

I don't suppose I have posted an overview of the methods for getting annotations from the Kobo devices. I would just point to the last time I posted this, but looking at it, it is missing some options and details.

Firstly, I an not happy with any of these methods. They all have limitations or need extra work or something. But, I also don't use any of them much. Most annotations I make are to mark errors in a book or something I want to look up later. Once I have fixed the problem or looked up the info, I delete the annotation. I know that some people want permanent annotations, but I haven't seen anything that comes close to a highlighter pen and a paper book.

Enough preamble, on to the methods...

Firstly, there is the method already discussed: run some SQL against the database and save the output. If you have any SQL skills, this is simple and can be easily customised. There have been also been several scripts posted here that do the same thing.

On the device, you can add "ExportHighlights=true" into the FeatureSettings section of the config file. With this, the long press menu of the book will have an extra option, "Export highlights" (or something like this). Using this will prompt you for a file name and export the annotations to that file in the root directory of the device.

If you are a calibre user, there is "Fetch annotations" (or something like that) on the device menu. This appends the annotations to the comments field of the metadata in the calibre library. It shows the highlighted text plus the annotation.

Again for calibre users, there is the Annotation plugin. Like the built-in calibre function, this fetches the annotations, but it can put them into a custom column. It is also handles them better if you use multiple devices or change them in the calibre library. This supports Kobo and Kindle devices plus some ereader apps. This plugin was created by someone else, but I am maintaining it.

As meera said, my Kobo Utilities plugin has a annotations function. It is basically the calibre "Fetch annotations" but written to a dialog that you can copy the text from. I wrote this before the Annotations plugin existed and do not recommend this. But, if I remove it, someone is sure to complain.

tshering wrote a nice little tool for Windows that extracts the annotations. I don't think it has been updated for a while, but the way annotations are stored hasn't changed, so it should still work.

Lastly, if the epub is on the main memory of the device, there is a file with the annotations in it. This is produced by the Adobe RMSDK side of the reader. It is in the directory "Digital Editions/Annotations". The rest of the path matches the path to the epub and the file has the extension "annot". This is an XML file that can easily be read to see the annotations. And with a little care, can be copied to the PC to use with ADE.

[tshering]

This is the correct link to the nice little tool. And here you can find a screenshot of it.