Convert the "Previous Recipients" database file into a text-tab file

Scripting Forums Code Exchange
#1

the list of previous recipients in Mail.app is saved in a sqlite3 database in ~/Library/Application Support/AddressBook
Sometimes it could be helpful to save the contents into a text file on desktop.
So my first attempt was:

set MailRecents to quoted form of POSIX path of ((path to application support from user domain as text) & "AddressBook:MailRecents-v4.abcdmr")
set a to paragraphs of (do shell script "sqlite3 " & MailRecents & " 'select * from ZABCDMAILRECENT'")
set dbase to {}
set TID to text item delimiters
repeat with i in a
	set text item delimiters to "|"
	set t to text items of i
	set text item delimiters to tab
	set end of dbase to t as text
end repeat
set text item delimiters to return
set dbase to dbase as text
set text item delimiters to TID
set targetFile to ((path to desktop as text) & "MailRecents-v4.txt")
set ff to open for access file targetFile with write permission
write dbase to ff
close access ff

after some “investigations” I found a shorter and much faster version

do shell script "sqlite3 " & quoted form of POSIX path of ((path to application support from user domain as text) & "AddressBook:MailRecents-v4.abcdmr") & " 'select * from ZABCDMAILRECENT' | tr '|' \\\\t > ~/Desktop/MailRecents-v4.txt"
#2

You can do it all from within the sqlite3 command. It caters for output as tab delimited text. The command becomes:

sqlite3 -separator ‘\t’ ‘select * from ZABCDMAILRECENT’ > ~/Desktop/MailRecents-v4.txt

and if you want the field headers included:

sqlite3 -headers on -separator ‘\t’ ‘select * from ZABCDMAILRECENT’ > ~/Desktop/MailRecents-v4.txt

and if you just want the email addresses without the other fields:

sqlite3 ‘select ZEMAILNORMALIZED from ZABCDMAILRECENT’ > ~/Desktop/MailRecents-v4.txt

You can of course wrap this in a do shell script to call from an AppleScript script.

Tom
BareFeet

#3

I take it you did not test any of your lines??

I say this as when I tried to test the

It did not work.

You need to omit the “on” part

do shell script "sqlite3  -header  -separator '	'  " & quoted form of POSIX path of ((path to application support from user domain as text) & "AddressBook:MailRecents-v4.abcdmr") & " 'select * from ZABCDMAILRECENT' > ~/Desktop/MailRecents-v4.txt
"

Note: -separator ’ ’ is -separator ‘\t’ , just AS converts it to a TAB

#4

Hi there this is great, is their a way to run this exact concept on my entire inbox?

Thanks for the help.

Miles