Import Outlook Contacts to Google Contacts

There is a lot of instructions on the Web, but none truly worked for me (either incomplete or blatantly wrong). Here’s how I did it:

What you’ll need

• A spreadsheet (I used Excel 2011 out of lazyness; I should have tried Google Spreadsheet).
• A Grep-capable text editor (EditPadPro on Windows (Notepad++ is not that great), Coda on Mac (TextWrangler should be OK too; it is just pretty old interface-wise)).

Preparation

First, save your Outlook contact as a CSV file (I’ll assume you either know or will find out). Then open it up in your text editor. And let the fun begin.

Before any exhaustive editing, we have to make this CSV file readable. This start by configuring the text editor properly, by having soft wrap off and line numbering on.

Then, we will start working on the file itself. We want to make sure one entry is exactly equal to one (hard) line. Why do we have to check? Because postal addresses line usually come with carriage returns. This is all well and good when reading an actual business card, but when editing a CSV file, this is just plain tough. Even worse, it will make your spreadsheet program (see later) going crazy with the file. Plus, Google will know when to have the carriage return back after import. So, remove any extraneous carriage return. In the end, one entry should equal one hard line. Double-check this, it may be pretty tough to check on a long CSV file (180 addresses for me).

Filtering

OK, now we have a passable CSV file to work on. Time for the real thing. Two are of particular importance and should not be oversighted:

• Remove the first column (name) because it is unusable and its informations are available elsewhere; the first column is just a concatenation of other columns with no added value (well, it is possible to add some specific value, but most people won’t do it and that’s good for us they don’t).
• Rename the column Name Prefix into Title. I have yet to see someone correctly inputing Mr. here; most people use this field for job’s title. If it doesn’t show up in Outlook, it does a lot in Google Contacts. Trust me, you don't want job’s titles in lieu of name prefixes. Associate manager for Europe Bertrand duGué is simply unreadable. Bertrand duGué (Associate manager for Europe) is much, much better! If you don’t believe me, trust your boss to tell you!

These are the two most important things to consider for mere importing. The rest will be about optimising.

Optimisation

Did you notice how people don’t know how to write? Looks like their caps-lock is locked. Full caps reigns supreme…

Here comes the spreadsheet. Its main advantages are formulæ (I’m sure Grep gurus may go without a spreadsheet, but I can’t) and… tadam! proper column display (it’s hard to figure out columns of text only with semi-colons as separators).

Add columns at the right of each one you want to sanitize and use the =PROPERCASE(A1) function (change A1 accordingly, of course); yes, I know, PROPERCASE is not perfect; that’s what manual editing if for. As for the e-mail field, I used the =LOWERCASE(A1) formula instead.

While we’re a it, we can also mass change various typos (like the ones PROPERCASE generated). Some spreadsheet programs may allow regular expressions during this process. Excel doesn't, so I just switched back to my text editor. I made sure all mails and phones where moved from other to work, I removed the parenthesis around the leading phone digits (this code works for French formatting: \(([0-9]{2})\)) and did some other things (mostly manual there). Anyway, this is your chance for batch editing.

Conclusion

Scrap the name column and rename Name prefix as Title. If you do that, your import should work well (at least with an Outlook 2007 CSV export). The rest is just taking advantage of being in a editing process. Your customer would probably not care for the rest as long as the first two part are done well.

Of course, it won’t work the first time. You’ll have to do it several times until it becomes second nature. This may look like an easy thing, but it took me five hours to have it work. Hopefully, now that you have this tutorial, you’ll import your file much faster, right?