Introduction: How to Export Kindle Highlights (Personal Documents Included)

By Blog
About: Necessity is the mother of invention. | youtube.com/alansartlog | patreon.com/alansartlog

This was originally a blog post of mine. I realized I was writing a lot of diy posts that were suited for making into instructables so I thought I'd republish the posts here. You can read the original posts at my blog here. The instructable have been edited slightly to fit better here. This one specifically doesn't have any pictures at all because I didn't think they were needed given the steps might vary so much.

Intro

I love highlighting in books, makes finding favorite passages easier. But I also do a lot of research so I need that feature. Unfortunately there aren't that many devices/apps capable of syncing and exporting highlights. I don't know why not. One would think this would be a really obvious feature in any app that allows hghlighting. Apparently not. For example, the only way I can really get highlights from a PDF is by going through Goodreader. Acrobat can't do this!

And for ebooks, the Kindle can't do this either! I don't know why. It's ridiculous. That, not being able to access personal docs on the Windows Kindle app, and the fact that you can't scroll in the Kindle app have always made me want to ditch it, but no other app can sync with my Kindle... sigh. I've considered getting a different ereader brand next time (the Keyboard is seriously outdated at this point) but none have good sync options.

Technically with Amazon you can sort of export highlights if you bought the books from them (it's easy to access them in a way that lets you copy), but not your personal documents. If you try to search for how to do this most of the guides are about this method which is useless for personal documents. There is a way to export your personal document highlights from your kindle itself and I'll go over it quickly, but I don't know if it works with newer kindles. The new method I found is as far as I know almost undocumented and will work from any iOS device, possibly even Android.

Step 1: ​From Your Kindle

There is of course the Clippings.txt but this file is ONLY updated if you make a highlight from your kindle, which makes it useless if you read anywhere else as well. It's also a mess going through it.

The better method is getting them from the .MBP files in your Kindle. Highlights are stored inside these alongside your books with each file corresponding to a book. You can copy them over to your computer and then use this little program, MBP Reader, (place it in the same folder as your highlights) to extract them. Just double-click and it'll create a bunch of txt files containing the highlights for each book.

Now according to the people that created the MBP reader, Amazon changed the file type in response to this.Why???!!! and no before you ask you can't copy an entire book like this, there's a clipping limit still in place. But on my Kindle Keyboard this still works. Maybe they only did this on the newer ones? I don't know. But this is one way to get your highlights. And for the most part I'd do it this way if you can because it's slightly easier and the clipping limit, although still there, is larger. You'll know if you hit it because the text file will just have a number and no highlights.

Step 2: ​From Your IPad/iPhone (possibly Android As Well)

UPDATE: The following (for the ipad/iphone and the last step/suggestions) is outdated. The Kindle App (version 4.17 + on iOS, 4.22 + for Android) FINALLY has an export option. Just go into the notes for the book and in the top right there's a share icon (little box with an arrow) which lets you email yourself all your notes and highlights (and does not limit them or anything if they're personal docs).

NOTE: This has a clipping limit. In fact the clipping is smaller than what's even shown on the notes summary screen of the iPad. It seems like it's 50 words like the copyright limit (that's about 3 lines in the iPad notes summary screen). Getting your clippings from the .MBP files allows for quite a bit more, hence why I prefer it.

There are times when using the first method is impossible though.

For example, I recently wanted to read a very large book (it had lots of images) on my iPad. It was a .mobi and making it into a PDF ruined the formatting. You can't email a book over 25mb to your Kindle address because of Gmail's file limit. I've tried different email services that claimed to be able to send big files (if anybody knows one that works, leave a link), but eventually I gave up and manually transferred the book through iTunes onto the Kindle app. The thing is this won't sync the highlights so even if I put the book in my Kindle Keyboard the .MBP file won't be generated. I thought well, come to worse, I would just use the fool proof third method described below, but I really didn't want to by the time I'd read the book because I'd highlighted a lot of stuff.

I searched around and apparently according to this Stack Exchange question the Kindle App used to keep an SQlite file called AnnotationStorage. This is literally the only place I've ever seen a proper answer to this problem and it's not even the first answer. Also the average user probably has no idea what an SQL Database even is and nobody else seems to have bothered to detail the process. So I thought I'd do it.

Step 3: ​From Your IPad/iPhone - Steps 1-2

Okay first we'll need to get the files. You'll need some way to access your iPad/iPhone device's internal files. This is a piece of cake if you're jailbroken (iFile). If you're not there's supposedly desktop applications that do allow you to see App folder but I haven't tried them (the Stack Exchange answer mentions iFunBox Classic).

Depending on your iOS version the Applications folder will be in different places. Now I imagine this file probably also exists on Android, but I don't have an android device to try. If anybody knows, leave a comment.

  • Pre iOS8 in /private/var/mobile/Applications and it's easy to find the Kindle folder.
  • Post iOS8 it's a nightmare. You'll have to go to /private/var/mobile/Containers/Data/Application and then you'll see a bunch of folders with different numbers. I believe this number changes occasionally. You could still attempt to bookmark it though, but it's likely you're going to have to do this every single time. It might be easier to just know the approximate size of the folder.
  • You're going to have to go one by one and open then go into the folders to determine which is the Kindle app.If you've manually transferred a book it's easier to check the APPNUMBER/Documents/ of each app as you'll immediately see the .MOBI for your book there. Otherwise check all APPNUMBER/Library folders, the Kindle one should contain folders named ACX, AmazonADs, etc. and the AnnotationStorage will be there.

Step 4: ​From Your IPad/iPhone - Steps 3-6

Now if you're not there already navigate to /Library in the Kindle folder. Here like I said you'll find the AnnotationStorage file (ignore the other AnnotationStorage -smh and -wal files). With iFile you can just select it, zip it, and email it to yourself to get it on your computer. Or you can use an app or SSH, but get it onto your computer somehow.

Now you'll need an SQLite reader. There are many (the Stack Exchange answer mentioned DBBrowser) I used a firefox Add-on called SQLite Manager because I already had it and the search function is better. DB Browser doesn't seem to be able to search for fragments of a cell.

Rename the file to AnnotationStorage.sqlite.

Open it with whatever program you want. You're going to want to to Tables > ZANNOTATION and switch the view so you can see the table. In SQLite Manager that's just the Browse & Search tab. It's going to look kind of like an Excel sheet. The important columns are ZRAWBOOKID. If you sort by this, you'll sort by book. If you can't find your book you can search ZUSERTEXT for a phrase in a highlight then get the book ID from that highlight and search by the book ID. Another important column is ZRAWANNOTATIONTYPE if you just want to get your notes or something.

Step 5: ​From Your IPad/iPhone - Steps 7-8

To export the data you need to highlight the rows you want then Right Click > Copy as CVS (MSExcel Compatible). Or you can run an SQL Query to get just the columns you want then copy from that. In SQLite Manager just go to Execute SQL, paste your preferred query and hit Run SQL. Make sure there's at least a space between each line or format it as shown. Either should work.

Here's a basic query that will get everything and order it by book then the location.

Select * From ZANNOTATION

Group By ZRAWSTART

Order By ZRAWBOOKID ASC

You can then paste this anywhere, that is, the formatting will be weird if you tried to paste it into Word, but the information is there. I would recommend Excel or Google Sheets (free) as they will arrange the data in columns automatically. You can then manipulate that however you want. I'm still not sure if the BookIDs are the same for all devices or even if they stay the same. If they do though, you could also keep a list of which book is which and then later batch replace the book IDs with the proper titles. I'll do a tutorial the next time I need to do this once I know if the IDs stay the same.

Alternatively you can mess around with the queries a bit to get just the info you want and copy that. You can use any of the following example templates or edit them slightly to your needs.

Here's something simpler that's easier to understand than the raw export.

Select ZRAWBOOKID , ZRAWANNOTATIONTYPE, ZUSERTEXT

From ZANNOTATION

Group By ZRAWSTART

Order By ZRAWBOOKID ASC

This will get all your highlights, show three columns (Book, Annotation Type, and the Annotation) and sort them by book, then within that by the location of the highlight. Note that ZRAWSTART (you can add that as a column if you want to see it) is not the Location number in your kindle. That info doesn't seem to be available or is somehow extrapolated from this by the app).

To add or remove columns. Just add a comma. The basic format is:

Select COLUMN1, COLUM2, COLUMN3

From TABLE

Table is ZANNOTATION in this case.

You can also filter by book ID in the SQL Query. The following just gets the Annotation Type, the Annotation, and then orders it by the location.

Select ZRAWANNOTATIONTYPE, ZUSERTEXT

From ZANNOTATION

Where ZRAWBOOKID="InsertBookIDw/Quotations"

Order By ZRAWSTART ASC

And for just getting highlights out and into Word or Notepad, you can use something like this, then they just need to be separated on individual lines. If you first paste this into Excel or Google Sheets then copy the column from there to Notepad or Word it will separate each highlight into a line for you but it will also get rid of quotation marks. I don't mind, but some might prefer them.

Select ZUSERTEXT

From ZANNOTATION

Where ZRAWANNOTATIONTYPE="highlight"

And ZRAWBOOKID="InsertBookIDw/Quotations"

Order By ZRAWSTART ASC

And to get your notes:

Select ZUSERTEXT

From ZANNOTATION

Where ZRAWANNOTATIONTYPE="note"

And ZRAWBOOKID="InsertBookIDw/Quotations"

Order By ZRAWSTART ASC

Anyways I hope you found my guide useful, maybe learned a bit of SQL. I'm not an expert at this but if you need some help with a query just leave a comment.

Step 6: Backup Foolproof Option

In case Amazon ever changes the way they do this (which I doubt), there will of course always be this method on older devices, but even on newer ones you can take a screenshot of your notes then OCR them. It's a pain, but the option is available. You can also use this for the longer highlights that won't export completely.