Recently I had a need to get Address Book information from an imaged Mac without having that computer up and running. Since I didn’t have the ability to do it on the running Mac I decided to try to reverse engineer the SQLite database that stores the AddressBook information. Below is what I put together.
The Address Book
The AddressBook database in my case is stored at:
First location
/Users/<user>/Library/Application Support/AddressBook/AddressBook-v22.abcddb and
Second location
/Users/<user>/Library/Application Support/AddressBook/<GUID Folder>/AddressBook-v22.abcddb
In my test I went with the Second location because the file was several megabytes in size compared to the First location that was a few hundred kilobytes. The migration.log mentioned iCloud so it’s likely larger because it’s syncing from iCloud and more data is good of course!
The SQLite 3 database: “AddressBook-v22.abcddb”
The Query
SELECT DISTINCT
ZABCDRECORD.ZFIRSTNAME [FIRST NAME],
ZABCDRECORD.ZLASTNAME [LAST NAME],
ZABCDRECORD.ZMAIDENNAME [MAIDEN NAME],
ZABCDRECORD.ZMIDDLENAME [MIDDLE NAME],
ZABCDRECORD.ZNICKNAME [NICKNAME],
ZABCDRECORD.ZSUFFIX [SUFFIX],
ZABCDRECORD.ZTITLE [TITLE],
ZABCDRECORD.ZJOBTITLE [JOB TITLE],
ZABCDRECORD.ZORGANIZATION [ORGANIZATION],
ZABCDRECORD.ZLASTSAVEDVERSION [LAST SAVED VERSION],
ZABCDRECORD.ZSYNCANCHOR [SYNC ANCHOR],
ZABCDPOSTALADDRESS.ZCITY [CITY],
ZABCDPOSTALADDRESS.ZSTATE [STATE],
ZABCDPOSTALADDRESS.ZSTREET [STREET],
ZABCDPOSTALADDRESS.ZZIPCODE [ZIPCODE],
ZABCDPOSTALADDRESS.ZCOUNTRYCODE [COUNTRY CODE],
ZABCDPOSTALADDRESS.ZCOUNTRYNAME [COUNTRY NAME],
ZABCDNOTE.ZTEXT [TEXT (NOTE)],
ZABCDNOTE.ZRICHTEXTDATA [RICH TEXT DATA (NOTE BLOB)],
ZABCDPHONENUMBER.ZFULLNUMBER [`FULL NUMBER`],
ZABCDPHONENUMBER.ZAREACODE [AREACODE],
ZABCDPHONENUMBER.ZEXTENSION [EXTENSION],
ZABCDPHONENUMBER.ZLABEL [LABEL (PHONE NUMBER)],
ZABCDPHONENUMBER.ZISPRIMARY [ISPRIMARY (PHONE NUMBER)],
ZABCDPHONENUMBER.ZORDERINGINDEX [ORDERINGINDEX (PHONE NUMBER)],
ZABCDRELATEDNAME.ZNAME As ‘Related Name’,
ZABCDRELATEDNAME.ZLABEL As ‘Related Name – Label’,
ZABCDURLADDRESS.ZURL As ‘URL’
FROM ZABCDRECORD
LEFT JOIN ZABCDPOSTALADDRESS on ZABCDRECORD.Z_PK = ZABCDPOSTALADDRESS.ZOWNER
LEFT JOIN ZABCDNOTE ON ZABCDRECORD.Z_PK = ZABCDNOTE.ZCONTACT
LEFT JOIN ZABCDPHONENUMBER ON ZABCDRECORD.Z_PK = ZABCDPHONENUMBER.ZOWNER
LEFT JOIN ZABCDRELATEDNAME ON ZABCDRECORD.Z_PK = ZABCDRELATEDNAME.ZOWNER
LEFT JOIN ZABCDURLADDRESS ON ZABCDRECORD.Z_PK = ZABCDURLADDRESS.ZOWNER
ORDER BY
ZABCDRECORD.ZLASTNAME ,
ZABCDRECORD.ZFIRSTNAME ,
ZABCDPHONENUMBER.ZORDERINGINDEX ASC
I’ve updated the query to alias the column names to make them more readable and easier to tell what they belong to, such as a phone number or a note. Since multiple tables are being linked together you do get duplication of records but you can always change things up to make it output in the way that you need.
Wrap-Up
Using the query you get the basic contact information including Notes, Address, Phone numbers and Related (relative) information. The database structure could vary from Mac OS version so you may need to do a little tweaking in some cases.
I hope you find it useful. If you find bugs or issues please post a comment so I can update it!