Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

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

chris001

chris001

macrumors regular
Original poster
Jan 31, 2008
130
0
Vancouver
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
 
Y

Yooner

macrumors newbie
Feb 15, 2011
2
0
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/
 
M

muddywaters

macrumors newbie
Jun 5, 2013
2
0
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
 
J

JorWesFlow

macrumors newbie
Aug 17, 2008
20
0
Los Angeles
muddywaters said:
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...
Click to expand...

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
 
M

muddywaters

macrumors newbie
Jun 5, 2013
2
0
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.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom