Sunday, September 18, 2011

Importing csv contacts into Gmail (with mapping)

Overview

This post attempts to provide help importing csv contacts into Gmail. The intention is that people who import data which is improperly mapped -- ends up in the 'Notes' section instead of in the appropriate field -- will be helped by this post.

Skip to Synopsis if you want the quick fix.

Background

I finally got my wife a smartphone and was trying to get it set up in time to give it to her for her birthday. Everything went smoothly. I was able to flash the LG Ally with the Velocity 1.1 custom rom (recommended if you have an LG Ally -- it is super fast. Google it). Next I used BitPim to backup her pictures, videos, and ringtones (see previous post here). I hadn't exported her contacts before..even though BitPim makes it relatively easy to export. Additionally, LG (or Verizon) seems to offer a backup tool...but I kinda like to do stuff like this manually to ensure it works properly, even if it is a little more work. I got the contacts exported in a relatively straightforward csv file. Easy. Importing into Gmail proved not so easy...

The Problem

When I first imported the csv (from Gmail's contacts section -- click more > import), it went smoothly enough. It seemed. I had expected to see some type of 'mapping' option, enabling me to specify that name becomes name, phone becomes phone...etc. There was no such option, so I thought Google's code may have just been smart enough to figure it out on its own (this turned out to be grossly incorrect). Upon review of the imported contacts, nothing went to the correct field...except maybe email. I searched a few forums and websites which gave me little hope that it was possible. After trying again with logically named headers (instead of the somewhat strange ones from the original export), and having no success, I thought I could export from Gmail contacts...then mimic the resulting headers for a (surely) successful import. Despite having to reorganize my csv substantially, this only produced another failure. The 'name' finally went to the correct place, but that is all. There were no errors, most of the imported information just ended up in a generic 'notes' textbox where any non resolved information went. This is certainly better than discarding the unsuccessfully mapped information, but not ideal, either.

The Solution

Finally, I came across this page, which had some column header suggestions and indicated experiencing a successful import. I decided to reorganize my csv information again and give it a shot. I used the third set of headers as my guide...and hypothesized that I needed to use typical Gmail contact keywords plus the 'phone' monikers to get my numbers to map correctly. This turned out to be correct -- at least for Name, Mobile Phone, Home Phone, and email. I'm not sure about successfully mapping Work Phone and Mobile 2 Phone (maybe another 'Mobile Phone' column would work?), as those column headers didn't work for me. The successful headers I used follow:

Successful Column Headers:

NameMobile PhoneHome PhoneEmail

Let me know if anyone else finds other successful headers, or a google resource which explains the situation / resolution more eloquently.

Synopsis

If you have trouble importing csv contacts into Gmail contacts (your information is being imported into the Notes box, but not being mapped correctly), try using column headers from the Successful Column Headers above. I can't be of help beyond those mappings, though.

7 comments:

Ferrograph said...

This seems to be the full list of header names. If you have a header with a name thats not in this list it will assume a custom CSV format and start guessing.

Name,
Given Name,
Additional Name,
Family Name,
Yomi Name,
Given Name Yomi,
Additional Name Yomi,
Family Name Yomi,
Name Prefix,
Name Suffix,
Initials,
Nickname,
Short Name,
Maiden Name,
Birthday,
Gender,
Location,
Billing Information,
Directory Server,
Mileage,
Occupation,
Hobby,
Sensitivity,
Priority,
Subject,
Notes,
Group Membership,
E-mail 1 - Type,
E-mail 1 - Value,
E-mail 2 - Type,
E-mail 2 - Value,
Phone 1 - Type, (EG Work, Home , Mobile etc)
Phone 1 - Value,
Phone 2 - Type,
Phone 2 - Value,
Phone 3 - Type,
Phone 3 - Value,
Phone 4 - Type,
Phone 4 - Value,
Address 1 - Type,
Address 1 - Formatted,
Address 1 - Street,
Address 1 - City,
Address 1 - PO Box,
Address 1 - Region,
Address 1 - Postal Code,
Address 1 - Country,
Address 1 - Extended Address,
Address 2 - Type,
Address 2 - Formatted,
Address 2 - Street,
Address 2 - City,
Address 2 - PO Box,
Address 2 - Region,
Address 2 - Postal Code,
Address 2 - Country,
Address 2 - Extended Address,
Organization 1 - Type,
Organization 1 - Name,
Organization 1 - Yomi Name,
Organization 1 - Title,
Organization 1 - Department,
Organization 1 - Symbol,
Organization 1 - Location,
Organization 1 - Job Description,
Website 1 - Type,
Website 1 - Value,
Custom Field 1 - Type,
Custom Field 1 - Value

Nick Chapman said...

Thanks for the information. Do you mind me asking where you came by this? Strange that the mappings (that worked for me, above) aren't listed. Maybe they are "guessed" properly?

I'll try to verify some of these at some point.

Regardless, thanks for the contribution!

Bradford Mitchell said...

You can see this by exporting your Gmail contacts and choosing the Gmail format. Then open that csv in Excel, and Cell A,A has all this in it.

Nick Chapman said...

Thanks, Ferrograph!

Having done much more importing/exporting since I wrote this, your reasoning sounds obvious. When I wrote this, though, I didn't even consider it. Thanks for posting a great, general, way to find out how headers are expected -- as they are exported.

I'm still surprised there was no 'mapping feature' or 'naming suggestion help text' available. Hopefully this has changed.

Maheshwaran Venkataraman said...

Thanks for this. I was trying the same steps using all the fields (as mentioned by Ferrograph) and it imported ok. However, when I removed the fields that I was not using, retaining the other fields with the same name, the import again got messed up. Any suggestions as converting my contacts into the format with all fields seems too time consuming. Thanks.

Nick Chapman said...

Maheshwaran Venkataraman,

Assuming you have an export of some type with some columns defined...I think you would just want to copy what Ferrograph posted, paste it into an excel column...then cut it, paste special (check 'transpose' to convert from rows to columns on paste). That gives you all of Google's column headers. Then you just map your information to that "template". You may not have a "directory server"...so just leave it blank. I'm sure Google is savvy enough to omit null data...so I expect it should allow you to use all Google's headers but only the data you find useful.

That said, I was able to import using the titles I listed in the original post, so I'm surprised you are having trouble with an abbreviated number of column headers. Was your CSV properly formatted? Sometimes, I use notepad++ to edit my CSV files because excel does strange things to flat file.

Good luck!

Nick Chapman said...

I just exported my contacts and they looked slightly different than what Ferrograph listed. Notably, I only had one block for addresses (where he lists two)...and I had as many as 6 emails for some contacts. Presumably , then, any numbered item can have more or less as necessary and allowed by Google by incrementing a '#' for all associated fields.

Name-Group Membership columns seem to be expected, though.

Oh, I had to "import from text," using commas as the delimiter, to get the information properly in excel 2007.