How to convert "AddressBook.sqlitedb" to CSV or similar?

Discussion in 'iPhone' started by chris001, Mar 28, 2012.

  1. macrumors regular

    chris001

    Joined:
    Jan 31, 2008
    Location:
    Vancouver
    #1
    Hey guys,

    So I updated my sisters iphone to the newest iOS.
    I backed it up and everything... The contacts did not sync. (fakkking apple) Apparently this is a common problem.

    Now I downloaded iphone backup extractor and I now have a "AddressBook.sqlitedb" file. but, i don't know how to convert that to CSV or put it on the iphone. My sisters phone is NOT jailbroken so I can't use those jail broken options.

    And "iPhone backup Extractor" has limited functions unless you buy a full license...

    Can someone help me convert the AddressBook.sqlitedb to CSV?

    thanks
     
  2. macrumors newbie

    Joined:
    Feb 15, 2011
    #2
    fix!

    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.

    http://yogeshd.blog.com/2011/04/10/viewexport-contacts-from-iphone-addressbook-sqlitedb-file/
     
  3. macrumors newbie

    Joined:
    Jun 5, 2013
    #3
    Update on the query

    Thanks! I found the post useful, but the query generates multiple contacts (one for each value found in the ABMultiValue table). I'm a SQL DBA, so I thought I'd update the query to generate one row (thus all information for the contact in one row) with information for certain fields. I noted which fields corresponded to specific values (i.e label 1, property 3 in ABMultiValue is mobile#). Note that the ABMultiValue table is joined each time with an outer join for each type of value you need to select.

    Hope this helps some - the query follows:

    select ABPerson.prefix, ABPerson.first,ABPerson.last, c.value as MobilePhone, h.value as HomePhone, he.value as HomeEmail, w.value as WorkPhone, we.value as WorkEmail
    from ABPerson left outer join ABMultiValue c on c.record_id = ABPerson.ROWID and c.label = 1 and c.property= 3
    left outer join ABMultiValue h on h.record_id = ABPerson.ROWID and h.label = 2 and h.property = 3
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 2 and he.property = 4
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 4 and w.property = 3
    left outer join ABMultiValue we on we.record_id = ABPerson.ROWID and we.label = 4 and we.property = 4
     
  4. macrumors 6502

    Joined:
    Aug 22, 2011
    #4
    This is useful...
     
  5. macrumors newbie

    Joined:
    Aug 17, 2008
    #5
    MuddyWaters, your query worked great! But I noticed I was missing some emails and phone numbers. I found that I had more "labels" per "property" than you had used in your query. I don't know a thing about SQL, but I tried to add additional labels by separating with commas (first code block below) and also tried by a different way (in the second code block below) but it didn't work for either attempt. I got error messages. I'm sure there's a proper way to do it. Can you help?

    Code:
    select ABPerson.prefix, ABPerson.first,ABPerson.last, c.value as MobilePhone, h.value as HomePhone, he.value as HomeEmail, w.value as WorkPhone, we.value as WorkEmail
    from ABPerson left outer join ABMultiValue c on c.record_id = ABPerson.ROWID and c.label = 1,3,5,9,10,12 and c.property= 3
    left outer join ABMultiValue h on h.record_id = ABPerson.ROWID and h.label = 2 and h.property = 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 4 and w.property = 3
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 1,2,3,5,6,8 and he.property = 4
    left outer join ABMultiValue we on we.record_id = ABPerson.ROWID and we.label = 4 and we.property = 4

    Code:
    select ABPerson.prefix, ABPerson.first,ABPerson.last, c.value as MobilePhone, h.value as HomePhone, he.value as HomeEmail, w.value as WorkPhone, we.value as WorkEmail
    from ABPerson left outer join ABMultiValue c on c.record_id = ABPerson.ROWID and c.label = 1 and c.property= 3 
    left outer join ABMultiValue h on h.record_id = ABPerson.ROWID and h.label = 2 and h.property = 3
    left outer join ABMultiValue c on c.record_id = ABPerson.ROWID and c.label = 3 and c.property= 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 4 and w.property = 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 5 and w.property = 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 9 and w.property = 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 10 and w.property = 3
    left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 12 and w.property = 3
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 1 and he.property = 4
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 2 and he.property = 4
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 3 and he.property = 4
    left outer join ABMultiValue we on we.record_id = ABPerson.ROWID and we.label = 4 and we.property = 4
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 5 and he.property = 4
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 6 and he.property = 4
    left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 8 and he.property = 4
     
  6. macrumors newbie

    Joined:
    Jun 5, 2013
    #6
    Sorry, I did not get your reply

    Hi Jorvesflow,

    Sorry I did not get your reply. I assume that by this time, you have your issue resolved. I did review your SQL and found several problems there. If you want to discuss those further we can do that. I'll check my preferences here to ensure that I'm receiving Emails when someone replies.
     

Share This Page