Work home contact integration: Outlook to Google to OS X Address Book

One of the secrets learned on the wrinkly path is that simple solutions are very hard.

Obviously, not hard to describe or apply. They're hard to invent, because there are so many solutions, simple and complex alike, and most of them aren't good.

Lately, I've been starting to tackle a gnarly problem - managing my Contacts (address book). It's a big problem ...

Gordon's Tech: Project Contacts: Integration across iPhone, Google and whatever

... There are a lot of variables in the mix, from endpoints to transition tools. Gmail Contacts, Google Apps domain shared contacts, Google Contacts API and extended Contact Kind, OS X Address Book [1], iPhone Contacts.app, Microsoft Exchange Server, ActiveSync, SpanningSync, FileMaker Pro, Bento, MobileMe, gSyncIt, iTunes sync, Google iPhone Sync, vCal, hCard, GData, OpenSocial, Windows Live, Facebook and Outlook among others. (Yes, several others.)...

... Yes, this one is the big Kahuna. Big enough that, for this project, I’ve created a new Gordon’s Tech Label of “Contacts” to help me as I work through all the options.

In the meantime, there’s one big bit of good news. Contacts don’t change as much as Calendaring events. I can get by with manual export/import for quite some time...

It took me quite a bit of thinking and playing with the variables to come up with that last sentence. I explored a lot of very complex approaches, only to be stymied by problems with data structures and a lack of adequate OS X desktop tools.

Eventually I decided to scope the problem strictly to creating a pool of Contacts in OS X Address Book that would sync to my iPhone, and a slightly different pool of Contacts in my Gmail account. Once I did that, the problem became a lot simpler. I now have 1,600 Contacts on my iPhone.

A diagram would help here, but I don't have time to make one up, so here's a skeleton of what I'm doing. It's all manual, but I only do it every few weeks, because (this is the key), Contacts don't change as much as most things.

• Exchange Server/Outlook -> Export CSV file -> Google Gmail import into a "group" called "Work Contacts". Since this is a simple one way export I clean out this group before the import. I do this every few weeks.
• Gmail export -> vCal file -> OS X Address Book. When you do this Address Book adds items to the main group, but it highlights those just imported. You can then carefully drag them into a "group" called "Work Contacts". Incidentally, OS X Address Book duplicate management is very sophisticated -- the best I've yet seen.
  

I think I'm going to discover some other interesting nuances. In particular Google Apps now provide an intriguing pooled Contacts collection that may facilitate a family Contacts pool.

Still, this is substantial progress for me.

Update: BTW, the default vCal behavior for OS X Address Book does not export photos or notes. You must turn these on in options. The vCal from Google does export notes and photos. Address Book imports the notes, but not the photos -- or at least not during a merge/update (this may be a bug).

Update 2/18/09: Oops. Corporate exchange servers often use x.500 email addresses (aka EX format, for "exchange") internally. Those aren't useful to Google or my iPhone. I recall older Palm sync solutions substituted net-standard email addresses during sync; I need something like that. So there's still some work to do. I'm not the only one dealing with this ...

Update 2/19/09: Inspecting the output file I see that Outlook 2007 exports CSV file emails in this form:

E-mail address E-mail type E-Mail Display Name
fredflintstone@bedrock.zz SMTP Fred Flintstone
/o=Megacorp/ou=North America/cn=Recipients/cn=xdf415 EX Barney Rubble (Barnie.Rubble@Megacorp.com)

So the x.500 (EX) addresses contain an employee identifier ('cn') that maps to a current email address which is stored in parentheses in the E-Mail Display Name field. The SMTP style addresses do not display the email address in the E-Mail Display Name field.

So really, we want Google to be smarter about importing. It ought to "know" to pull the email address out of the [E-Mail Display Name] column when [E-mail type] = "SMTP".

This is rather annoying.

Someone must have written a utility that reads in this export file and post-processes it to extract the SMTP address and store it in the E-mail address column for EX type rows. I'll have to look for it. Meanwhile I'll ask Google to fix their import processing.

Update 2/19/09: I tried linking from Access 2007 to Outlook 2007, but this produces a very peculiar view of email addresses. The old data model problem no doubt -- the UI for doing this looks very old, so I suspect this function is really obsolete. I then exported as Access 2003 from Outlook 2007 and got something similar to the CSV file export. On inspection, however, it became obvious that Outlook's storage of SMTP equivalents for EX email addresses is pretty erratic. Indeed, it looks like even the EX addresses stored in Outlook (vs. on Exchange server) are flaky. So I'm beginning to understand why Google doesn't want to try to get clever with processing the CSV files -- the email addresses are unreliable.

I feel so sorry for programmers who try to work with this stuff. Outlook is really decrepit.

I'll need to think about this a bit more ...

Update 5/19/09: Major progress. Among other things, I learn that if you copy .OST Contacts to a .PST file Outlook resolves the X.500 EX addresses to SMTP addresses.