Search code examples
macossqlitecontacts

Export Contacts from Apple Address Book Database

Apple stores Address Book Data in a local sqlite3 databse. I want to export the name and number in a clean and consistent format for all contacts

The database is located in: /Users/kellygold/Library/Application\ Support/AddressBook/Sources/<RANDOMSTRING>/AddressBook-v22.abcddb where RANDOMSTRING is a generated file name

Inside the DB there is a table ZABCDPHONENUMBER with field ZFULLNUMBER but the values are not stored consistently

ZFULLNUMBER

NOTE the different formats: (111) 222-3333, +12223334444, 1112223333, +1 (222) 333-4444. Some numbers appear in only one format, some numbers have multiple rows with multiple formats for same contact

Query to produce this:

SELECT DISTINCT
    ZABCDRECORD.ZFIRSTNAME [FIRST NAME],
    ZABCDRECORD.ZLASTNAME [LAST NAME],
    ZABCDPHONENUMBER.ZFULLNUMBER [FULL NUMBER]
FROM
    ZABCDRECORD
    LEFT JOIN ZABCDPHONENUMBER ON ZABCDRECORD.Z_PK = ZABCDPHONENUMBER.ZOWNER
ORDER BY
    ZABCDRECORD.ZLASTNAME,
    ZABCDRECORD.ZFIRSTNAME,
    ZABCDPHONENUMBER.ZORDERINGINDEX ASC

Desired output flat with preceeding country code optional:

FIRST NAME, LAST NAME, FULL NUMBER
asdf, fghj, 2223334444
bbbb, cccc, 12223334444

Currently I process the data by exporting the SQLITE query as JSON and running this very hacky solution cat adbExport.json| jq '.[] | select(.["FULL NUMBER"] != null)' | sed 's/+//g'\ | sed 's/ //g' | sed 's/-//g' | sed 's/(//g'| sed 's/)//g' | jq '{FIRSTNAME: .FIRSTNAME, LASTNAME: .LASTNAME, FULLNUMBER: ("+1"+ .FULLNUMBER)}' | sed 's/+11/+1/g' > cleanContacts.json

This produces valid JSON which I can use. (data obfuscated for security)

...
{
  "FIRSTNAME": "AnXXX",
  "LASTNAME": "ZuckXXX",
  "FULLNUMBER": "2068901111"
}
{
  "FIRSTNAME": "Nick",
  "LASTNAME": "fromHay",
  "FULLNUMBER": "262443XXXX"
}
...

How can I do this directly from the Database query?

Solution

  • Try this :

    #!/usr/bin/env bash

sqlite3 ~/Library/"Application Support"/AddressBook/Sources/*/AddressBook-v22.abcddb<<EOF
.mode json
SELECT DISTINCT
    ZABCDRECORD.ZFIRSTNAME [FIRST NAME],
    ZABCDRECORD.ZLASTNAME [LAST NAME],
    ZABCDPHONENUMBER.ZFULLNUMBER [FULL NUMBER]
FROM
    ZABCDRECORD
    LEFT JOIN ZABCDPHONENUMBER ON ZABCDRECORD.Z_PK = ZABCDPHONENUMBER.ZOWNER
ORDER BY
    ZABCDRECORD.ZLASTNAME,
    ZABCDRECORD.ZFIRSTNAME,
    ZABCDPHONENUMBER.ZORDERINGINDEX ASC;
EOF

    The result I get is :

    [{"FIRST NAME":null,"LAST NAME":null,"FULL NUMBER":null},
{"FIRST NAME":"Philippe","LAST NAME":"surname","FULL NUMBER":"+188888888"}]

    Update

    You list of commands can be done with a single call of jq :

    jq '.[]["FULL NUMBER"] |= gsub("[ ()+]";"")' adbExport.json

    You can also do it in SQL with an extension : replace a part of a string with REGEXP in sqlite3