View/Export contacts from iPhone AddressBook.sqlitedb file

Recently I updated my iOS to 4.3.1. I tried replacing the AddressBook.sqlitedb file on iPhone to restore my contacts, but it doesn’t seem to work. I guess there is some bug in iOS 4.3.1 and it might be fixed in 4.3.2. But, I need my contacts back. So I did some research, and here is how you can get the contacts in a CSV file.

1. Open AddressBook database

Open Firefox and install the SQLite Manager add-on for Firefox, restarted firefox. Goto Web Developer->SQLite Manager. On SQLite Manager window click the open icon and selected AddressBook.sqlitedb.

2. Open the Execute SQL tab and execute the following query

select ABPerson.prefix, ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID

if you want to get more details;, e.g, notes, birthday, etc., use the following query

select ABPerson.prefix,ABPerson.suffix, ABPerson.first,ABPerson.middle,ABPerson.last, ABMultiValue.value, ABPerson.note, ABPerson.nickname, ABPerson.organization, ABPerson.department, ABPerson.jobtitle, ABPerson.birthday from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID

And wollah, there are all your contacts.

3. Hit the Actions button and choose Save Result(CSV) to file

there you have all your contacts in a CSV.

41 Responses to “View/Export contacts from iPhone AddressBook.sqlitedb file”

  1. Nice one, worked for me !

  2. The article you wrote is really nice.

  3. good one, thanks!

  4. Big thumbs up – thanks!

  5. Im running sql manager on mac, after i exectute the file.
    i select export andd save as csv.
    But it asks me choose a table to export, which 1 shoid i select??

  6. Worked exelent! Now i got all my lost contacts back to my iPhone!
    Thanks!!!

  7. That does it for the contacts, but can you also help of how to extract the photos from the addressbookimages.sqlitedb ?
    I saw that the photos table has a data colum that stores a blob.
    But I don’t know how to extract it to a file, or better … How to extract all the blobs to different files.
    It’s probably jpg or png or someother photo format.

    Thanks !

  8. Love it!! Lost iPhone and needed my contacts back.. Thanks a lot!!

  9. You are my hero!!

  10. Does anyone know what query to enter to retrieve calendar items? Thanks!

  11. Thanks a lot, this is the best solution.

  12. Thank you, this worked perfectly even though I have no experience with SQL. I had trouble finding a way to get these contacts because most programs were either unable to interface with IOS 5, or else they weren’t freeware.

    Thank you so much!

  13. This worked PERFECTLY! Any chance you’re able to figure out the querry for “Calendar.sqlitedb” to do a similar export for an iPhone calendar? THANKYOUTHANKYOUTHANKYOU!

  14. you are my hero

  15. PLZ HELP.

    i opened the address book.sqlitedb.

    CLICKED EXECUTE AND WROTE

    “select ABPerson.prefix, ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID”

    IT SAYS “file is encrypted or is not a database”

  16. The querry works with SQLite (for mac) too.

    1. Open the adressbook.sqlitedb with SQLite
    2. Go to execute tab
    3. Copy the querry from here to there

    4. See all your contacts

    :)

  17. hi there

    is any one help me how to slect, I am sort of lost dont know how to slect

  18. “select ABPerson.prefix, ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID” is a SQL statement (command). You can run this statement in the SQLite Manager add-on for Firefox plugin described in the post.

  19. hey guys thanks for help, but there is problem, i can see my contacts in excel format but can not recover contacts in here, any idea why this happenin?

  20. You will have to manually add all your contacts to your iphone again. There may be a software to import contacts from csv. Do some research; google is your friend.

  21. i would have done that if i can get the correct format where i can see number against name, But when i open this file in excel it come on with coded language and number and name are all over.

  22. Open the file in notepad if you are having issues with excel. Although excel can very well handle csv files but you might have to specify word separator when opening the file (which is a comma in case of csv files). Again read about csv files if you have any doubts and google opening csv files in excel.

  23. Very good explanation. Unfortunately this method only recovers:
    First Name
    Last Name and
    telephone numbers

    With some knowledge of sql (which I don’t have), it is also possible to recover more data from the address book.
    In particular, I was also looking to recover:
    - addresses
    - Notes (in address book)
    - prefix
    - suffix
    - Organisation
    etc

    also, I created some custom label for different categories of telephone number: example “mobile – USA” and “home – USA” and “mobile – UK” etc…

    Anybody has an idea of how to make a complete recover of all data from the address book?
    thanks in advance!

  24. Hi Michael, updated post with a query to get the extra details you require. Hope this helps.

  25. Hi Yogesh,
    wow, what a quick response. This is great – thank you very much!

    I have a couple more questions:

    1) the code you added created multiple records (i mean multiple lines) for each contact person. For example, if a contact has multiple phone numbers, I get:

    Contact 1, value [phone number 1]
    Contact 1, value [phone number 2]
    Contact 1, value [phone number 3]
    Contact 1, value [phone number 4]

    Is there a way of having:
    contact 1, phone number 1, phone number 2, phone number 3, and phone number 4

    all on the same line?

    2) I had created some custom labels for my phone numbers (example: “home – US”, “mobile – UK”, “work – direct”, “mobile – private”, etc…)
    I found these labels very useful. From what I can see, that actual names of the fields are stored in table called: “ABMultiValueLabel” and they link to field “label” in ABMultiValue table.

    Is there a way of adding these label to the extracted data?

    3)Also had some addresses.
    From what I can see, these addresses are stored in the table called “ABMultiValueEntry”, under the field “value”.

    In the same table, the field “key” defines the sort of value in the field “value”, and links to table “ABMultiEntryValueKey”.

    Finally, in the same table (“ABMultiValueEntry”), the field “parent id” link to field called “UID” in table “ABMultiValue” (?)

    In table “ABMultiValue”, I am not sure what the fields called “property” and identifier” are for.

    Again, is it possible to also extract this data?

    thank you very much in advance if you have time to have a look!

    PS: here is a schema of the structure of the data (altough I don’t think it is complete):
    http://www.fukudat.com/wiki/ja/iPhone_Tips/AddressBook%E3%81%AE%E8%A7%A3%E6%9E%90/FW_3.1.2/%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E6%A7%8B%E9%80%A0

    PPS:
    final question (much less important):
    Apart from my main address book, I also had 2 groups. Data from these groups are not being extracted. Is there a way to also extract this data?
    If not, no worries, as there is little data here, and I had it backed-up on Outlook.

  26. Hi Yogesh,
    super, what a quick response. This is great – thank you very much!

  27. I have a couple more questions though:

    1) the code you added created multiple records (i mean multiple lines) for each contact person. For example, if a contact has multiple phone numbers, I get:

    Contact 1, value [phone number 1]
    Contact 1, value [phone number 2]
    Contact 1, value [phone number 3]
    Contact 1, value [phone number 4]

    Is there a way of having:
    contact 1, phone number 1, phone number 2, phone number 3, and phone number 4

    all on the same line?

    2) I had created some custom labels for my phone numbers (example: “home – US”, “mobile – UK”, “work – direct”, “mobile – private”, etc…)
    I found these labels very useful. From what I can see, that actual names of the fields are stored in table called: “ABMultiValueLabel” and they link to field “label” in ABMultiValue table.

    Is there a way of adding these label to the extracted data?

    3)Also had some addresses.
    From what I can see, these addresses are stored in the table called “ABMultiValueEntry”, under the field “value”.

    In the same table, the field “key” defines the sort of value in the field “value”, and links to table “ABMultiEntryValueKey”.

    Finally, in the same table (“ABMultiValueEntry”), the field “parent id” link to field called “UID” in table “ABMultiValue” (?)

    In table “ABMultiValue”, I am not sure what the fields called “property” and identifier” are for.

    Again, is it possible to also extract this data?

    thank you very much in advance if you have time to have a look!

    PS: here is a schema of the structure of the data (altough I don’t think it is complete):
    http://www.fukudat.com/wiki/ja/iPhone_Tips/AddressBook%E3%81%AE%E8%A7%A3%E6%9E%90/FW_3.1.2/%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E6%A7%8B%E9%80%A0

    PPS:
    final question (much less important):
    Apart from my main address book, I also had 2 groups. Data from these groups are not being extracted. Is there a way to also extract this data?
    If not, no worries, as there is little data here, and I had it backed-up on Outlook.

  28. Thanks so so much for this! After that wretched iPhone update I had to find a way to get my contacts back and this worked spectacularly!

  29. I’ve imported all my contacts but unfortunately the mobile facebook app added a value to all my contacts: webpage. Now when I import all my contacts have a phone number listed FB://profile9485223. Or something like that.

    How would I create a different value so it doesn’t mistake those fields for a phone number

  30. Wolla? Lol… it’s voila.

    thanks for the article though

  31. Yogesh- thanks for all of the above. I am not very tech-savvy so perhaps you can help me. I synced my iphone 4 this morning and accidentally synced my contacts database which erased manually entered contacts on my phone with an (empty) database from my Mac.

    I tried to restore the phone from backup, but it seemed that I’d hit sync one more time and the latest backup already had removed the contacts.

    I then went online and found an iphone extractor for the mac, and (by guesswork), extracted the first sync that I’d executed this morning, and am assuming that this sync would have the entire contacts database, before it was wiped out.

    I’m now trying to open the file using your instructions above. I’ve never used firefox and am unable to get started- I’ve downloaded the SQLite Manager for Firefox, but after this, I don’t know how to “Goto Web Developer->SQLite Manager. On SQLite Manager window click the open icon and selected AddressBook.sqlitedb.”. How do I go to Web Developer?

    Appreciate the help!

  32. Install SQLManager plugin for firefox (Open the file with firefox). Restart firefox.

    Now SQLiteManager should be in the Tools menu of firefox.

  33. You are my savior

  34. Thanks so much Yogesh,
    But i have a couple of problems:
    1. when I open the AddressBook.sqlitedb file, there are not so many tables etc.
    but when I rename the file as .txt, I see all the tables (ABPerson , ….)
    2. when i execute the query on this renamed file, it works but there are nothing in the feilds; theu=y are empty. I am sure that the contacts are still in the file (when I open the file in notepad, I can search and find the names of my contacts so I am sure they are still there).

    Is it possible for you that I email you the file and you convert it for me?

    Thanks for your help…

  35. The steps are clearly given in the post. I don’t think I would be able to get anything else if you are getting empty fields. Try renaming the file as .sqlite. I doubt this would make any difference…but you can give it a try.

  36. any idea how to tell if the number is a mobile/home/other/iPhone ?

    select * from ABMultiValueLabel; shows the i18n values, but i dont see a way to link that with ABPerson or ABMultiValue.. do you ?

  37. Thanks :D

  38. Hi, I have a question. I tried to sync my contacts with google account but suddenly my contacts were deleted and i don’t have the contacts on my iPhone 4 or on my gmail. After that i copied the adressbook file via iFile on my computer and tried the steps mentioned above but i only get the last 5 contacts that i saved after my contacts were deleted. Is there any possibility the other contacts to be in this file because it is 2.2 MB large, and if yes can i get a little help here.
    Thanks :)

  39. All contacts were deleted from the sqlite db file on sync. So unfortunately there is no way to recover those contacts from the file. It is always a good idea to regularly grab a copy of the addressbook sqlite file.

  40. Hey Yogesh,

    I followed your (awesome!) explanations, managed everything ok,
    it does save a .csv, but that is empty and has 0 Bytes.
    the sqlite.db has about 200 kb so there must be contacts in there.
    Any idea what my mistake might be?

    Thanks so much!

  41. Hi Ann,

    Do you see any contacts in sqlite manager after you execute the query? If you do, maybe there is something wrong with the export feature of sqlite manager. Try exporting to a different format. If not, there are no contacts in the db file.

    Hope this helps.

Leave a Reply