Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Quickly extract all email addresses from Address Book Apps
To quickly (as compared to using nested loops in AppleScript) extract all email addresses from the Address Book, you can tap into Address Book's SQLite database using the command line (in Terminal):
sqlite3 ~/Library/Application\ Support/AddressBook/AddressBook-v22.abcddb "select ZADDRESSNORMALIZED from ZABCDEMAILADDRESS;"
If you want to alphabetize and remove duplicates:
sqlite3 ~/Library/Application\ Support/AddressBook/AddressBook-v22.abcddb "select ZADDRESSNORMALIZED from ZABCDEMAILADDRESS;" | sort | uniq
Please note: This works in Leopard, and should work in Tiger too. However, the database filename looks like it could change at any time (and may be different in Tiger). Also, the commands above "normalize" the email addresses (i.e. they convert all characters to lower case; this does not affect your ability to use them because eMail addresses are not case sensitive). If you do not want the email addresses normalized, substitute ZADDRESS for ZADDRESSNORMALIZED:
sqlite3 ~/Library/Application\ Support/AddressBook/AddressBook-v22.abcddb "select ZADDRESS from ZABCDEMAILADDRESS;"
If you want to alphabetize and remove duplicates, you need to add a few additional parameters to sort and uniq, so they will ignore case differences:
sqlite3 ~/Library/Application\ Support/AddressBook/AddressBook-v22.abcddb "select ZADDRESS from ZABCDEMAILADDRESS;" | sort -f | uniq -i
To save the output of any line above to a text file, append > /path/to/save/to/filename.txt to the command.

[robg adds: These commands worked as described when I tested them. If you cd to the directory first, of course, you can shorten the commands considerably, as you can leave out the full path, and just include the filename -- i.e. sqlite3 AddressBook-v22.abcddb "select ZADDRESS from ZABCDEMAILADDRESS;" | sort -f | uniq -i.]
    •    
  • Currently 2.36 / 5
  You rated: 1 / 5 (11 votes cast)
 
[29,809 views]  

Quickly extract all email addresses from Address Book | 6 comments | Create New Account
Click here to return to the 'Quickly extract all email addresses from Address Book' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Quickly extract all email addresses from Address Book
Authored by: unforeseen:X11 on Aug 28, '08 10:00:49AM
No need to use 'sort' and 'unique' if you have sqlite3 at hand. ;)

sqlite3 ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb "select DISTINCT(ZADDRESS) from ZABCDEMAILADDRESS ORDER BY ZADDRESS COLLATE NOCASE ASC;"

---
this is not the sig you`re looking for.

[ Reply to This | # ]

Quickly extract all email addresses from Address Book
Authored by: tobyvoss on Aug 29, '08 12:53:02AM
in tiger, sqlite3 says SQL error: file is encrypted or is not a database when used on the file AddressBook.data

[ Reply to This | # ]
Quickly extract all email addresses from Address Book
Authored by: S Barman on Aug 29, '08 09:04:58PM

Does anyone know how to find the column names in any of the Addressbook or Calendar files?



[ Reply to This | # ]
Quickly extract all email addresses from Address Book
Authored by: popguru on Aug 30, '08 04:23:53PM
The sqlite3 command .tables will show a list of tables, and .schema TABLENAME will show the SQL CREATE statement for that table. e.g.
sqlite3 ~/Library/Application\ddressBook/AddressBook-v22.abcddb '.schema ZABCDEMAILADDRESS'
...returns...
CREATE TABLE ZABCDEMAILADDRESS ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZORDERINGINDEX INTEGER, ZISPRIMARY INTEGER, ZOWNER INTEGER, Z19_OWNER INTEGER, ZISPRIVATE INTEGER, ZADDRESS VARCHAR, ZADDRESSNORMALIZED VARCHAR, ZUNIQUEID VARCHAR, ZLABEL VARCHAR );
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESSNORMALIZED_INDEX ON ZABCDEMAILADDRESS (ZADDRESSNORMALIZED);
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESS_INDEX ON ZABCDEMAILADDRESS (ZADDRESS);
CREATE INDEX ZABCDEMAILADDRESS_ZOWNER_INDEX ON ZABCDEMAILADDRESS (ZOWNER);
Edited on Feb 01, '10 06:16:56AM by robg


[ Reply to This | # ]
Quickly extract all email addresses from Address Book
Authored by: franc3sco on Dec 29, '08 02:18:22PM

The Automator action "Get Contact Information" now allows you to easily do this, and much more.



[ Reply to This | # ]
Quickly extract all email addresses from Address Book
Authored by: mikaelbook on Jan 12, '09 03:50:21AM

I would like to use this method, but n macosx 10.4.11 the file AddressBook-v22.abcddb is empty (0 bytes). What is the name of the database one should open with sqlite3 ?



[ Reply to This | # ]