Importing Excel-based Phone Lists into Outlook

How many Excel-based phone lists do you have at your work place? In this day and age, where the average mobile phone can hold thousands of contacts, it makes sense (to me anyway!) to have all these phone numbers stored in the mobile.

I've recently bought an iPhone which even caters for multiple Outlook Contact folders. This allowed me to import different business units' phone lists as different Outlook Contact folders.

Here's a HOWTO I wrote for work's wiki, but I thought I'd share it with the wider community. Note we use Outlook / Excel 2003 so I'm unsure if the same procedure would work with older/newer versions of the applications.

A. Prepare the Excel for Importing into Outlook.

There are a few things that need to be done:

1. If the phone numbers in Excel are extensions only, you'd want to construct the full number so that your mobile has the full number, rather than just the extension. If your office has the same prefix for all of the extension, then you can add an additional column and use the CONCATENATE formula to combine the prefix with extension. E.g. if the extension numbers are in column 'E', and the office phone prefix is "03 8788", then we can have the formula as "CONCATENATE("03 8788 ", E2)".
   
   For my office there are different prefix for different extensions. So I added an additional column next to the Extension column, sort the data via the Extension column, and populated the correct prefix for each type of extension. The example formula above then becomes "CONCATENATE(D2, " ", E2)".
   
   
2. Map the fields. Outlook expects certain field names. The common ones I'd use are: "Last Name", "First Name", "Job Title", "Business Phone", "Mobile Phone", "Notes", and "Company". You can manually map the fields during import, however to do so automatically, you can just spend 5 seconds rename the fields to these exact names. Fields not matched will not be imported automatically.
   
   
3. Name the phone list range. Basically highlight the phone list area, and go to Insert->Name->Define to give the range a name. Note as per previous paragraph fields not matched will not be imported automatically. I call my range "Contacts"
   

That's it! Save the file and it's ready to be imported into Outlook.


B. Importing into Outlook.

The way I've set up my Outlook is to have a separate Contacts folder for each of these. E.g. one contact folder called BizUnitA, another called BizUnitB etc. This is so that when looking for contacts in iPhone, I can easily choose which group I'm looking for rather than scrolling through pages of phone listing. However if your phone does not support the multiple contact folders then you can still import each of this list into a single folder one after another.

1. 1. Create Outlook Contacts. Select Contacts, then File->New Folder. Name it "BizUnitA" (or whatever you fancy)
2. 2. File -> Import/Export -> Import from Another Program or File -> Microsoft Excel -> Select file location (Replace duplicate Item) -> Finish (all mappings have already been done in Excel)
3. 3. Repeat from step #1 if you intend to have multiple Contact folders, or #2 if you want a single folder
   
   To sync with iPhone
   
   
4. Connect iPhone / Open iTunes, go to Info. Chances are you won't see the new Contact folders yet. Sync once and then you should be able to see the new Contact folders. Select them and then sync again.
   

That's it folks!