Export Contacts from Apple Address Book Database

Question

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

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?

Philippe · Accepted Answer · 2023-03-04T12:55:22.057

1

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

edited Mar 04 '23 at 12:55

answered Mar 02 '23 at 23:52

Philippe

20,025
2
23
32

No dice. I still get multiple formats in the `FULL NUMBER` column – Goldfish Mar 03 '23 at 01:33
Are you sure you put `.mode json` as in my answer ? – Philippe Mar 03 '23 at 01:36
yeah, sorry. the JSON output works fine, but my result is `{"FIRST NAME":"Sari","LAST NAME":"Younan","FULL NUMBER":"72********"}, {"FIRST NAME":"Ni**","LAST NAME":"Ze***","FULL NUMBER":"+1 (***) 760-****"}, {"FIRST NAME":"P****r","LAST NAME":"****em","FULL NUMBER":"(***) ***-****"},` Note the different formats for the phone number. Know any way to only get integers for that? – Goldfish Mar 03 '23 at 03:51

score 0 · Answer 2 · answered Mar 04 '23 at 02:01

There's not a good way to manipulate the data in the fields form the query. Instead. Here is some python to solve it cleaner than the bash above

def get_address_book(address_book_location):
    conn = sqlite3.connect(address_book_location)
    cursor = conn.cursor()

    cursor.execute("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")
    result_set = cursor.fetchall()

    json_output = json.dumps([{"FIRST NAME": t[0], "LAST NAME": t[1], "FULL NUMBER": t[2]} for t in result_set])
    json_list = json.loads(json_output)
    conn.close()

    for obj in json_list:
        # Get the phone number from the object
        phone = obj["FULL NUMBER"]
        if phone is None:
            continue
        # Remove all non-numeric characters from the phone number
        phone = "".join([c for c in phone if c.isnumeric()])
        #if the phone number is 10 digits, add "+1" to the beginning, if it's 11 digits, add "+"
        if len(phone) == 10:
            phone = "+1" + phone
        elif len(phone) == 11:
            phone = "+" + phone
        # Add the phone number to the object
        obj["NUMBERCLEAN"] = phone
        

    new_json_output = json.dumps(json_list)
    return new_json_output

Export Contacts from Apple Address Book Database

2 Answers2