RSS

Extracting highlights from iBooks (IPad or iPhone)

28 Aug

NOTE: This post is outdated , Click here to view updated post 

Hi guys if you use iBooks on Ipad or iphone than you also highlight some important text while reading  ,and you wondered how good it can be to extract only highlighted part of that book which you want to revise in future ………

I wondered same I would like to demonstrate a way to extract your all your highlighted text to a word file.

(NOTE: If you are using iBOOKS 1.5 or later than method is bit different , please have a look in comments for more info )

so what we have

What we are going to achieve

Software required

  • iPhone backup extractor
  • Sqllite database browser
  • Microsoft office(Word and Excel)
Steps
  1.  Connect your iPhone or iPad with iTunes , so ITunes will have backup of it
  2. once you have backup finished close iTunes 
  3. open iPhone backup extractor and select device you want get highlighted bookmarks from and click on “Expert mode” button on bottom right
              4.  in Backup details select com.apple.iBooks and click on Extract selected  

5. select a folder you want to save extracted data and click ok, in my example it’s ibooks project

6. Now in that folder go to ibooks Project ->Application->com.apple.iBooks->Documents, you can see files shown in following pic
7. Open SqlLite Database Browser click on open database (see in following pic)
8. Now select file iBooks_revision-two.sqlite in Documents folder
9. Once file is open go to Browse data tab
10. select Table ZBKBOOKMARK from table drop down
11. once your are on ZBKBOOKMARK table go to File menu and Export Table as CSV file.
12. Save that CSV and open it
13. you can delete all columns except ZTEXTUALCONTENT 
14. You can filter that column to remove blank cells
15.  Copy whole column and paste to Word with Use Destination style which will give you this table form
16. Format it by giving it numbers and title
if you are a geek and want to go in depth you can go through ZBKBOOKMARK table and copy highlights with with particular Book it belongs to ,so in final form you can have it distributed Book wise as this are highlighted from all books in iBooks.
Advertisements
 
38 Comments

Posted by on August 28, 2011 in Gadgets, Solutions

 

38 responses to “Extracting highlights from iBooks (IPad or iPhone)

  1. sagar

    October 11, 2011 at 2:23 pm

    This post is an awesome. But what if I want to make the above steps as an application (windows or mac based) where I am giving input the “backup folder ” of iBooks and finally I get the list of Bookmarks. I am sure that such application cant me made running on ipad itself as we cant have access for iBooks back up folder there.
    Plz share your opinions

     
    • parthiv777

      October 11, 2011 at 8:51 pm

      Hi Sagar glad that u liked it, i thought of such possibilities

      Scenarios
      1. iPad based app which can fetch iBooks bookmarks : – Now apple is having very strict moderation before letting any app on there app store where they won’t like is some other app is messing with data of there own app iBooks
      2. windows or mac based software : – I am not sure how to extaract sqllite database file from backup without help of any software (so if there is any opensource software than some think can be thought), once u got sqllite dbase file than u can code rest as it’s just database file u hook in a software to extract data out.

      i was thinking to write apple for including such feature in iBooks sooner or later they will adopt it..
      With iCloud your bookmarks(highlights ) of iBooks get sync with cloud so if they have some api access there they we can built a window or mac based software for it……

       
      • Mark Anthony Cianfrani

        December 22, 2011 at 7:29 am

        Hey! Great work around. Not being able to export highlights has been my biggest (and only) complaint with the iPad.

        I too was thinking about how awesome this would be as an app….and while Apple has very strict sandboxing guidelines, I’m wondering if an app can be written exclusively for jailbroken devices (they have root access and bypass the sandbox).

        I know very little about anything, but I just found out something interesting to research

         
      • parthiv777

        December 22, 2011 at 8:35 am

        hi Mark i want to write a app for that now but not sure about how to extract iphone backup without app (i want some open source app to extract sqlite databases from iphone backup), I tried to contact creator of http://supercrazyawesome.com/ (iphone extractor) to maker his app opensource for greater good but didn’t got response so now I am thinking that i should write a app for jailbroke devices …i bought mac few weeks back do u have any knowledge or ideas about this ??

         
      • Mark Anthony Cianfrani

        December 22, 2011 at 8:41 am

        It actually looks really easy. The iphone sdk makes parsing sqlite very easily. Shoot me an email if u wanna talk more. Im definitely looking to buikd this – markacianf at gmail dot com

         
  2. Brian

    October 15, 2011 at 1:24 am

    I tried this last night and it does not seem to work. I received an error from the extractor saying the back up was either encrypted (not true) or not valid. I will contact the Writers of the extractor and let Them know but also wanted to give a note of caution.

     
    • parthiv777

      October 15, 2011 at 9:42 am

      hi Brian , looks like you are on iTunes 10.5 and you have selected to encrypt backup for your device (see in picture, try to open it in new tab if it’s not clear to u), if it’s not so u should contact backup extractor tool’s developers…
      backup encrypt ios 5

       
  3. Brian

    October 17, 2011 at 2:55 am

    Yeah. No. That box is definitely not checked. I did write to the Authors of the extractor but have not received a reply yet.

     
  4. sagar

    October 19, 2011 at 2:59 pm

    Hi Parthiv. I am getting more curious. Doing it another way (as I dont have access for any iDevice currently), I got iTunes back of iBooks from one of my friend. the extension is .ipa and when renamed to .zip, i can view inside folder structure. How to extract this ipa file using iPhone Backup Extractor?

     
    • parthiv777

      October 19, 2011 at 8:36 pm

      .ipa files is iphone firmware(ios software update file from Apple) file that’s not iphone backup file, there is not any data of your highlighted bookmarks in it …..it’s like once u install it to your ios device than u use ibooks and highlight some text than and only than above shown way make any sense……

       
  5. Brian

    October 20, 2011 at 2:02 pm

    I found a different extractor which works with iOS 5; similar name, though. It is at a web site http://supercrazyawesome.com/

     
    • parthiv777

      October 20, 2011 at 2:51 pm

      nice one Brian….thanks for sharing ….

       
  6. Selcuk Demiray

    January 16, 2012 at 5:37 am

    Thanks for this blog. But I think something changed with the 1.5 version of iBooks. Because I cannot find the iBooks_revision-two.sqlite file anymore, instead there is a file called AEAnnotation_v10312011_1727_local.sqlite in the Application\com.apple.iBooks\Documents\storeFiles . I am afraid the above method should be reviewed again.

     
    • parthiv777

      January 16, 2012 at 7:33 pm

      Hi Selcuk this one is for old version of iBooks , as Apple has done some changes recently to ibooks so it’s different now i will drill it again in spare time and reblog it probably….

       
      • parthiv777

        January 21, 2012 at 8:17 pm

        HI Guys if you are using new version of iBooks than there is little change in sqlite file (database file) which we are using to extract bookmarks…
        Previously (including in above shown method) we used file name : iBooks_revision-two.sqlite
        new version have file name : AEAnnotation_v10312011_1727_local.sqlite
        following are few pics which makes it more clear

        Which is in Application\com.apple.iBooks\Documents\storeFiles (Courtesy Selcuk Demiray )
        when you open AEAnnotation_v10312011_1727_local.sqlite file please choose table ZAEAnnotation and you will find all highlights there ….and you can continue with above shown steps for rest of part..

         
  7. tedsvo

    January 22, 2012 at 7:46 am

    Parthiv,

    This is huge, and something I’ve been looking for for months. Please let me know if there’s anything I can do to move along the development of some kind of script or app to do this in a less time intensive way.

    As an aside, if you do want to group them by book I believe “ZANNOTATIONASSETID” is the relevant variable, though you’ll have to figure out for yourself which ID corresponds to which book.

    Thanks again!

    Teddy

     
    • parthiv777

      January 22, 2012 at 8:08 pm

      i pretty much figured out those id where i can fetch and print highlights according to books and chapters, but now we have 2 separate database to deal with (as 1 database file have books’ details and other have this highlights), but it was for previous ibooks version so now i have to spare some time to analyse new database structure ,even i am also thinking to make a script or little app for this purpose ,even Mark cianfrani (He had commented above) also thinking to make app so a normal non IT person can also fetch his highlights….
      Now if you have noticed Apple have introduced ITextBooks which will run though iBooks and have a functionality to fetch your highlights as cards …maybe they will bring it for normal books to i guess…

       
  8. Selcuk Demiray

    January 22, 2012 at 8:11 am

    Hi Parthiv,
    Thank you for the update.
    Here in John Lavagnino’s page: https://plus.google.com/u/0/104572288221923289065/posts/Pj5ui9mc3Vm
    there are some sql commands which work successfully with sqlite3 command line utility.
    I have tested both version 1.5 and 2.0 of iBooks and results are satisfactory.
    Regards.
    Selcuk

     
    • parthiv777

      January 22, 2012 at 8:11 pm

      hi thats interesting stuff… thanks for sharing Selcuk…

       
  9. John

    February 19, 2012 at 9:05 am

    Hi! Thanks a lot! But i deleted my books from ipad2, ibooks 1.3.2, ios 4.3.5 when I installed ios 5.0.1.

    Now I have ios 5.0.1, ibooks 2.0.1 and I have my books somewhere on my pc. Can I put my old books in ibooks 2.0.1 and restore highlighted bookmarks for each books on ipad?

    Or can I installed ibooks 1.3.2, put my books in. Delete iBooks_revision-two.sqlite-journal and iBooks_revision-two.sqlite on ipad with ifile. And copy my old iBooks_revision-two.sqlite-journal and iBooks_revision-two.sqlite. Can I restore my highlighted bookmarks for each books on ipad in such way?

    The problem is I have only two files iBooks_revision-two.sqlite-journal and iBooks_revision-two.sqlite in backup. Somewhere I had read that people must have iBooks.plist and iBooksData2.plist containing highlighted bookmarks for each books.

     
    • parthiv777

      February 19, 2012 at 11:03 am

      Hi John since ibooks version 1.5 there are two separate sqlite file for storing iBooks data
      one stores database of books in you library
      and other stores highlights (previously it was all in one file iBooks_revision-two.sqlite )
      so it would be difficult to go by replacing sqlite files in ibooks back-end …
      i am not sure about plist files(i haven’t tried… )
      what i can suggest is if u can jailbreak and go back to your previous backup(4.3) with ibooks highlights…
      then upgrade to ios 5 don’t upgrade to ibooks 2.0 at this stage …once you are in ios5 you can put on option to sync highlights via icloud ,once it’s in icloud you can upgrade or delete book but your highlights stays ….otherwise you can analyse ibooks 2.0’s two sqlite file and compare and copy some data from iBooks 1.3’s sqlite file….

       
  10. John

    February 19, 2012 at 12:56 pm

    parthiv777, ios 5 deleted all my media on ipad. Well, it’s my fault because of the fact that I thought that media is only video and music, photos. I didn’t do full 28 gb backup. But I did simple backup. ios5 deleted my books in ibooks 1.3.2 on ipad. Then I myself upgrade ibooks 1.3.2 to 2.0.1. And restore ipad as a new device for jailbreaking.

    Now i don’t have ibooks at all, but I want to install 1.3.2 (i have this file on pc) Then I want to put my books (i have them on pc as well) in ibooks 1.3.2 via itunes. And then replace iBooks_revision-two.sqlite-journal and iBooks_revision-two.sqlite on ipad.

    What do you think about it?

    And another moment. I might have to rename booknames on ipad in /var/mobile/Media/Books/. According to iBooks_revision-two.sqlite, they exist on ipad with different names like 765GHF565.epub. You said you could upgrade or deleted book but your highlights stayed. Don’t u think that different names is random names? Newly added books might have another names and highlights might not appear.

    Thanks in advance!

     
    • parthiv777

      February 19, 2012 at 1:35 pm

      first of all don’t mess with books name there, they are with some random number names but ibooks gave it those names and put it in sqllite database (iBooks_revision-two.sqlite), in this case there is little hope that you will get your highlights data back….
      if u have old backup than just restore ibooks sqlite file there open it with sqlite browser and check can u see any highlights data there or not ?
      if yes than go ahead (may be do full restore from that backup on your phone which can get u your books and bookmarks )

      otherwise u have to start over…

       
  11. William Rand

    March 28, 2012 at 12:05 am

    App? If anyone knows of an app that will do this-extract highlights from ibook so they can be placed in a pages doc, please let me know. William

     
    • parthiv777

      March 31, 2012 at 3:06 am

      Hi William i alteady made a tool for this , where u upload your sqlite files and it will fetch and display your highlights on next page in your broswer so u can copy it from there…it will also generate epub ebook of your highlights.. I will host it when i m back from my holidays …..

       
      • tedsvo

        March 31, 2012 at 3:12 am

        Please let me know when you do!

         
  12. parthiv777

    May 2, 2012 at 3:44 pm

    Alright guys wait is over i made this online tool to extract highlights from .sqlite files…
    although it’s in beta mode but works alright

    check it out :
    http://118.139.182.210/EEP/

    you have to upload those two sqlite files and on next page it will give you highlights on next page, but one surprise would be , it also can generate .epub eBook file(an ebook of your marked highlights) so you can read your highlights back in iPad ….have fun …. and feel free to put any feedbacks in comment below…
    thanks…

     
  13. Selcuk Demiray

    May 3, 2012 at 6:56 am

    Hi Parthiv, Thanks for your efforts to create such a useful online tool. I tried it, however it was difficult for me to compare the result with my commandline output. Because too many highlights exist in my books. However I believe some highlights are missing in your results. By the way, you know if you highlight a sentence in a paragraph, sqlite db both contains the sentence (or word) you highlighted and the entire paragraph in different columns. I think you output the entire paragraph column. My personal choice would be the only highlighted sentence (or word). I need extra time to evaluate further. See you.

     
    • parthiv777

      May 3, 2012 at 9:42 am

      HI Selcuk thanks for feedback, i gone through the query i am using i am using ZANNOTATIONREPRESENTATIVETEXT instead of ZANNOTATIONSELECTEDTEXT , so thats why it’s showing whole paragraph than highlighted text only. i will change this

      about missing highlights can you please share query you are using on command line, so i can implement it in this program to get right results.

       
      • Selcuk Demiray

        May 3, 2012 at 6:40 pm

        Parthiv, Here is the content of my command file:

        attach database ‘iBooks_v10252011_2152.sqlite’ as books;
        attach database ‘AEAnnotation_v10312011_1727_local.sqlite’ as annot;
        .separator “|”
        .output output.txt
        select ZBOOKTITLE, ZANNOTATIONSELECTEDTEXT, ZANNOTATIONNOTE from annot.ZAEANNOTATION, books.ZBKBOOKINFO where (books.ZBKBOOKINFO.ZBOOKUNIQUEID = annot.ZAEANNOTATION.ZANNOTATIONASSETID) and (annot.ZAEANNOTATION.ZANNOTATIONTYPE = 2) and (annot.ZAEANNOTATION.ZANNOTATIONDELETED = 0) order by books.ZBKBOOKINFO.ZSORTTITLE, annot.ZAEANNOTATION.ZPLABSOLUTEPHYSICALLOCATION;
        .quit

         
  14. parthiv777

    May 4, 2012 at 11:06 am

    hi Selcuk thanks again for sharing this query , i updated it’s query….

     
  15. Selcuk Demiray

    May 6, 2012 at 1:34 am

    Excellent result. Much better than mine. The Epub output is very handy. Can I bookmark the above IP address to use it in the future?

     
    • parthiv777

      May 6, 2012 at 10:49 am

      thanks Selcuk it couldn’t be better without above query you shared …
      sure u can bookmark it, anyways whenever i have time i will buy a domain and make it a proper website…..

       
  16. .Reid. (@ReidThis)

    May 10, 2012 at 9:35 am

    This is what I have been looking for for MONTHS!!! Thank you thank you thank you to all involved!!!

    May I suggest creating another blog with these new additions? This may make it easier to find on the internet (I know others will be interested). That way others will not have to read all the way to the bottom to see all the progress you have made! Some might see the initial complexity, and not read the way to the bottom.

    Thank you thank you thank you!!!

     
    • parthiv777

      May 10, 2012 at 11:02 am

      I am glad that u liked it Reid , I purchased a domain name export-ibooks-highlights.com and soon going to blog new post for directions …..

       
  17. parthiv777

    May 11, 2012 at 5:49 pm

     
  18. adagioparacuerdas

    September 2, 2012 at 3:25 pm

    thank you very much, parthiv777

     
    • parthiv777

      September 3, 2012 at 10:44 am

      You are welcome Franco …

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: