How to convert "AddressBook.sqlitedb" to CSV or similar? - MacRumors Forums
Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > iPhone, iPod and iPad > iPhone

Reply
 
Thread Tools Search this Thread Display Modes
Old Mar 28, 2012, 09:22 PM   #1
chris001
macrumors regular
 
Join Date: Jan 2008
Location: Vancouver
How to convert "AddressBook.sqlitedb" to CSV or similar?

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
chris001 is offline   0 Reply With Quote
Old Apr 11, 2012, 06:29 PM   #2
Yooner
macrumors newbie
 
Join Date: Feb 2011
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/v...sqlitedb-file/
Yooner is offline   0 Reply With Quote
Old Jun 5, 2013, 10:56 PM   #3
muddywaters
macrumors newbie
 
Join Date: Jun 2013
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
muddywaters is offline   1 Reply With Quote
Old Jun 5, 2013, 11:48 PM   #4
Enjoylife1788
macrumors 6502
 
Join Date: Aug 2011
This is useful...
Enjoylife1788 is offline   0 Reply With Quote
Old Aug 28, 2013, 08:40 PM   #5
JorWesFlow
macrumors newbie
 
Join Date: Aug 2008
Quote:
Originally Posted by muddywaters View Post
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...
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
JorWesFlow is offline   0 Reply With Quote
Old Nov 16, 2013, 02:44 PM   #6
muddywaters
macrumors newbie
 
Join Date: Jun 2013
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.
muddywaters is offline   0 Reply With Quote

Reply
MacRumors Forums > iPhone, iPod and iPad > iPhone

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Is there a game similar to "Sort or 'Splode" on iOS? (From Super Mario 64 DS) Siderz iPhone and iPod touch Apps 8 Jun 12, 2014 05:27 PM
""There is not enough memory or disk space to convert this document." SamEB Mac Applications and Mac App Store 0 Nov 14, 2013 07:48 AM
"Folders" or something similar for Homesharing Movies and TV Show Seasons (Ripped) shyam09 Apple TV and Home Theater 11 Mar 5, 2013 05:51 PM
Start a new tab similar to "iOS blog" and "Mac blog" but make it "IPhone Leaks?" Dewroo Site and Forum Feedback 2 Aug 23, 2012 09:47 AM
Cydia tweak similar to the Mac app "caffeine"? mrsir2009 Jailbreaks and iOS Hacks 3 Jul 26, 2012 09:37 AM

Forum Jump

All times are GMT -5. The time now is 02:00 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC