Saturday, August 7, 2010

FileMaker- Quick OS X Address Book importing without plugins

In this article I will show you how to import your OS X Address Book contacts quickly without plugins.

When it comes to Address Book, developers usually choose two methods.
1) AppleScript looping- It's slow for many contacts.
2) Plugins- They are not free.

I'd like to share another method, which is basically 2-3 lines of code.

What you will need:

1, perform applescript command
2, one import command
3, sql knowledge

How?

Address Book stores its data in an sqlite database, which can be found at the $Home/Library/Application Support/Address Book folder.

OS X can access this using the built-in sqlite3 command. (Reference here).

With this sqlite3, we just simply execute an sql command to get the address book data exported to a tab separated file to a temporary path (Perform Applescript- Do shell script)
Then we'll just import is using the Import Records script step.
(See the attached demo file for working scripts)

SQL:
You have to build your own SQL commands to use this technique. There are many GUI SQLite browsers (even Firefox plugins!). Browse through the address book tables, and explore them. That's exactly what I did.
Also you can do a search on "OS X address book sqlite" to find sample sql scripts to experiment with.

I attached a sample file which has two sample scripts.
The first script imports just the first name, last name, middle name, company data.
The second one imports the contact's phone number, mobile and email address as well.



You can download a really simple demonstration from here . (right click- save as).
Feel free to modify the scripts, sql statements!


Note: I didn't spend much time to optimize the code. You should quit Address Book before exporting it's data. If you experiment with this technique, please do a backup of your address book db.

The attached sample may need to be modified if your address book database file is somewhere else.


- Adam


The material on this document is offered as is. There is no representation or warranty, expressed or implied, nor does any other contributor to this post. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

24 comments:

  1. One major problem. Multiline addresses. The returns in the fields themselves will will be seen as line returns in the tab delimited file. Been trying to find a way around this but haven't been able yet.

    ReplyDelete
  2. What I would suggest you is to use "replace" to change new line characters to something else, then replace it back in fm after importings.
    Or ignore new lines, change them to spaces.
    Select .... replace(addresssfield,'/n',' ') as address_new ..etc

    The new line character may be /n or x'0D' i think, but you should check it.

    http://www.sqlite.org/lang_corefunc.html
    search for replace syntax

    ReplyDelete
  3. Great, and inspiring stuff!
    I wonder if this could work as well on the iPhone with Filemaker Go?

    ReplyDelete
  4. No, it won't work.
    iOS won't let you run sqlite3 and export it this way.

    ReplyDelete
  5. Hello,
    I just tried your sample file with FMP 10Adv under Lion. Nothing happens when I click on the import buttons. Looking with the Script Debugger and data viewer I get an errot 100 ; the variable for the temporary path returns "/Lion/private/var/folders/wc/5z3qjcq93pdcvz2fxf69jr7h0000gn/T/Cleanup At Startup/S10/abook.tab"
    Can you help me to make your sample worka ?
    Noël

    ReplyDelete
  6. Hi Noel,

    Lion + Mountain lion has a new address book db path.

    $HOME/Library/App*Sup*/Add*B*/Sources/*/Add*B*-v22.abcddb

    instead of
    $HOME/Library/App*Sup*/Add*B*/Add*B*-v22.abcddb

    Please change those, and you're good to go.
    Adam

    ReplyDelete
  7. Hello Adam,
    Thanks for your replay.I'll try it in a moment but I have 2 questions more !
    a) Since I posted I also tried under v11 and it worked ! Is there a differcence between these versions ?
    b) I'd like to get also address in my fmp dev : how must I modify your calculated string !
    Regards

    ReplyDelete
  8. Hi

    I don't use FM 12, but i don' think that's the problem. The address book db path depends on the OS, not FM.
    To load addresses, you have to modify the sql, addresses are in the ZABCDPOSTALADDRESS table.
    Fields are for example, ZCITY,ZCOUNTRYNAME,ZSTREET,ZZIPCODE, etc, if you rename the abcddb file to an sqlite file, you can view the table structure with an sqlite viewer.

    So the modified sql can be something like this:

    select ZFIRSTNAME,ZMIDDLENAME,ZLASTNAME,ZORGANIZATION,ZJOBTITLE,ZCITY,ZCOUNTRYNAME,ZSTREET from ZABCDRECORD left join ZABCDPOSTALADDRESS on ZABCDPOSTALADDRESS.ZOWNER=ZABCDRECORD.Z_PK;

    you need sql knowledge to modify these request but they are not that hard. ABCDRecord.Z_PK is the main key.
    ZOWNER in the related table is the key to the ABCDRecord.

    If you need phone numbers, email addresses as well, the second sample needs to be modified.
    There you need the insert:
    ,ZCITY,ZCOUNTRYNAME,ZSTREET ..... from ZABCDRecord left join ZABCDPOSTALADDRESS on ZABCDPOSTALADDRESS.ZOWNER=ZABCDRECORD.Z_PK

    after the " as mobilenum from ZABCDRecord" text I think.
    so ....as mobilenum, zcity,zcoutryname,zstreet from ZABCDRecord left join ....etc

    I hope it helps.

    ReplyDelete
  9. Adam,
    It will certainly be helpful. But first I must solve (or not if impossible) the question I asked before : make your sample run under FileMaker Pro Advanced v10 I have license for !
    I tried it on a friend computer under Lion too and both v11 and v12 and with the same lot of extensions : it's OK.

    Don't understand what happens !
    Noël

    ReplyDelete
  10. Before you answer just a precision after installing on my machine v11 of my friend

    Under v11 the $$path receive the following value :

    /Lion/private/var/folders/wc/5z3qjcq93pdcvz2fxf69jr7h0000gn/T/S10/abook.tab

    Under v10 it is
    /Lion/private/var/folders/wc/5z3qjcq93pdcvz2fxf69jr7h0000gn/T/Cleanup At Startup/S10/abook.tab

    and the value returns error 100 (file not found) of course !

    Why so much hatred ? :-)

    ReplyDelete
  11. I can reproducate the problem with FM 10.
    FM11 no problem, FM10 same machine problem.
    The problem may be with the temporary path.
    Try to change it to a Get(DesktopPath) instead of Get(TemporaryPath), and maybe delete the temp file after importing.

    ReplyDelete
  12. Bingo Adam !!!!
    It works now fine.
    Thanks for your help and the sharing of this trick.
    Noêl

    ReplyDelete
  13. Horrible night! I tried to join your codes into the script to get back the addresses, but really unsuccessfully! I have no knowledge of sql.
    If you have one day the opportunity occasion to add a button "ImportAddress Book WithPhoneEmailAddresses" in your example it will be an appreciated gift !
    But after my breakfastI am again going to look at it!
    Noël

    ReplyDelete
  14. You can download the new address import sample here:

    http://dropcanvas.com/d0pd5

    ReplyDelete
  15. Hello !
    Like Snoopy I can say "I'm happy" and thank you more...
    I can also say "I'm stupid" : I had modified after the first select...
    Regards
    Noël (but you are Father Christmas !)

    ReplyDelete
  16. Hello,
    It's time for me to shutdown my computer... But a last question came : would it be possible to pass as parameter the name of a group created in the addressbook to limit the import to its content : Friends, Family, Pupils...
    But perhaps it's to want to have your cake and eat it !
    Noël

    ReplyDelete
  17. Hello Adam,
    As you didn't relay to my previous interrogation, I think that it is not possible...
    But would it be possible to get with all the informations (name, phone, mail, zip...) the name of the group in which is the contact ? If it was possible a script in FMP deleting thos not desired would be a solution...
    Thanks for your interest
    Noël

    ReplyDelete
    Replies
    1. Hi Noel,

      do you have the files from Adam? He deleted them accidentally... Can you put them on a sharing site.

      TIA
      Laurent

      Delete
  18. Sorry, I have to make a living, and have other things to do,
    It's not that easy to get the group name, but here you are, new sample, group names are separated with a , char.
    The card group is always there.

    Link:
    http://dropcanvas.com/gefjg

    ReplyDelete
    Replies
    1. Hello Adam,

      I did try the two downloads but the canvas links had expired ....
      Any chances you could upload them to a more permanent place?

      Thanks,
      Laurent

      Delete
  19. Sorry for disturbing !
    I'll try to find through the structure of the addressbook and perhaps I'll be able to modify alone.
    Best Regards

    ReplyDelete
  20. Hello Adam,

    I did try the two downloads but the canvas links had expired ....
    Any chances you could upload them to a more permanent place?

    Thanks,
    Fabio

    ReplyDelete
  21. Sorry, i don't have those file, I accidentally deleted them.
    Try to ask Noel, hopefully he has them.

    ReplyDelete
  22. I'm excited to try out my new FileMaker. I just bought the OS X Address Book, so this guide will help me know how to import it without plugins. This will be so helpful so that I don't have to worry about dealing with annoying plugins.
    Bill Li | http://www.zerobluetech.com/custom-filemaker-development/

    ReplyDelete