PRS-T1 Extract quotes from reader

[damaki]

Hi people!
I just made this smallish piece of code to extract the quotes from a PRS-T1. It has to be run on cygwin or in a whatever OS terminal.

Just change the path to your sqlite3 binary and run this from your "READER" mount point. It creates a "quotes.txt" with all your quotes.
It could probably be improved with details about the quotes' pages but I only intended to get the full text quotes.

Enjoy!

#!/bin/bash
OUTPUT_FILE='quotes.txt'
SQLITE='./sqlite3.exe'
AWK='awk'

#add the UTF-8 BOM to the generated file
echo -ne '\xEF\xBB\xBF' > $OUTPUT_FILE
$SQLITE Sony_Reader/database/books.db 'select b.author, b.title, a.marked_text FROM annotation a INNER JOIN books b on a.content_id=b._id' | \
$AWK -F '|' '{ print "--------------------\nAuthor: "$1"\nTitle: "$2"\n--------------------\n"$3"\n--------------------" }'| \
unix2dos >> $OUTPUT_FILE

[edit:]removed useless iconv command

[ewandeep]

Wonderful!!!

For very lazy people, version for MAcOS:


#!/bin/bash -vx
OUTPUT_FILE='quotes.txt'
SQLITE='sqlite3'
AWK='awk'
ICONV='iconv'

#add the UTF-8 BOM to the generated file
echo -ne '\xEF\xBB\xBF' > $OUTPUT_FILE
$SQLITE Sony_Reader/database/books.db 'select b.author, b.title, a.marked_text FROM annotation a INNER JOIN books b on a.content_id=b._id' | \
$AWK -F '|' '{ print "--------------------\nAuthor: "$1"\nTitle: "$2"\n--------------------\n"$3"\n--------------------" }'| \
$ICONV -t UTF-8 -f UTF-8 >> $OUTPUT_FILE

[damaki]

By the way, iconv should not be required as I figured out how to add the BOM manually (and that iconv was totally useless for that).

This should be the proper piece of code :

#!/bin/bash
OUTPUT_FILE='quotes.txt'
SQLITE='./sqlite3.exe'
AWK='awk'

#add the UTF-8 BOM to the generated file
echo -ne '\xEF\xBB\xBF' > $OUTPUT_FILE
$SQLITE Sony_Reader/database/books.db 'select b.author, b.title, a.marked_text FROM annotation a INNER JOIN books b on a.content_id=b._id' | \
$AWK -F '|' '{ print "--------------------\nAuthor: "$1"\nTitle: "$2"\n--------------------\n"$3"\n--------------------" }'| \
unix2dos >> $OUTPUT_FILE

[Abtacha]

Cool, I haven't used the quote feature of the T1 yet, but it's nice to know that you don't need to sync with the Sony software to get them.

Thanks and K+ for that.

Do you think there is any way of integrating that into Calibre, as an extra plugin maybe?

[damaki]

Originally Posted by Abtacha

Cool, I haven't used the quote feature of the T1 yet, but it's nice to know that you don't need to sync with the Sony software to get them.

Thanks and K+ for that.

Do you think there is any way of integrating that into Calibre, as an extra plugin maybe?

Probably. Sqlite is quite a standard database and it should't be too hard to get inspiration from the Odyssey calibre driver which can already export quotes. But I'd rather build a small standalone app that I can leave it on my T1 and use it without Calibre. I cannot really see the point in Calibre integration as it has no quote database, as far as I know.

[opitzs]

You would still be able to fill in a custom field... That would be quite nice.

[fei-d]

I just tried to do this for my book on my W7 PC....

Unfortunately, I failed to do this.

Can somebody pleasre give the not so technophile people an elaborate example as to how to use this (with directories).

I tried to put this line of code into the cmd-console, but I just did not know where and what exactly to put in for my sd-card with the database.

So can somebody please help me?

[log]

fei-d, you might have to wait until someone converts this to batch if you're not up for the task, otherwise if you want to try to make it work you will need to install cygwin, make sure you have following packages selected: bash, awk, sqlite3

[fei-d]

So, I installed cygwin and pasted the line of code from the OP. But I just get the error that "-bash: ./sqlite3.exe: No such file or directory".

Obviously, I did not specify the correct path to my database with the highlights. So where should I put the database or what path should I specify. Where should I change the code?

[opitzs]

Try changing
SQLITE='./sqlite3.exe'
to
SQLITE='sqlite3'

That should work in cygwin (Linux here, so I can't test this, sorry)

[log]

fei-d also, make sure you have sqlite3 selected in the package manager of cygwin I don't think it's included by default.

You don't have to reinstall, if you you re-run setup.exe and point it to the same path of your previous install (it should have already remembered what you did last time) make sure the sqlite3 package is installed.

[fei-d]

So I checked again if I had installed sqlite3 and even installed it again...

Opitzs's advice seemed to help. Now cygwin at least runs the code.

But now I get another error: "Error: unable to open database "Sony_Reader/database/books.db": unable to open database file"

This is really getting frustrating...

I tried the following code:

#!/bin/bash
OUTPUT_FILE='quotes.txt'
SQLITE='sqlite3'
AWK='awk'

#add the UTF-8 BOM to the generated file
echo -ne '\xEF\xBB\xBF' > $OUTPUT_FILE
$SQLITE Sony_Reader/database/books.db 'select b.author, b.title, a.marked_text FROM annotation a INNER JOIN books b on a.content_id=b._id' | \
$AWK -F '|' '{ print "--------------------\nAuthor: "$1"\nTitle: "$2"\n--------------------\n"$3"\n--------------------" }'| \
unix2dos >> $OUTPUT_FILE

[opitzs]

Did you first change the directory to
cd /cygdrives/<whatever drive letter your reader has>

The script opens Sony_Reader/database/books.db without any drive assignment, this only works, if you are there already, otherwise it won't find the path...

[fei-d]

Thanks a lot!!! It finally does work.

I am very gratefule to you two (opitzs and log) for taking your time with me and helping me.

In the end my last problem was to go to the right drive and execute the code there.

But now there seems to be a limit at how much is extracted, because it only extracted up to 200 characters; longer highlighted sections were not completely extracted....

Though one can live with this it is still annoying. Maybe this is more of a problem with the T1 than the code itself. Then again, the ebooks I use are DRM-free and should not cause such problems for the T1.

[j0534ng31]

Originally Posted by fei-d

But now there seems to be a limit at how much is extracted, because it only extracted up to 200 characters; longer highlighted sections were not completely extracted....

Can you test if this works as you want? (I can't because I have not received my T1 yet...)

#!/bin/bash
OUTPUT_FILE='quotes.txt'
SQLITE='./sqlite3.exe'
AWK='awk'

#add the UTF-8 BOM to the generated file
echo -ne '\xEF\xBB\xBF' > $OUTPUT_FILE
$SQLITE Sony_Reader/database/books.db 'select b.author, b.title, a.marked_text FROM annotation a INNER JOIN books b on a.content_id=b._id' | \
$AWK -F '|' '{ print "--------------------\nAuthor: "$1"\nTitle: "$2"\n--------------------"; $1=""; $2=""; print $0"\n--------------------" }'| \
unix2dos >> $OUTPUT_FILE