Importing a Contacts CSV into Gmail and BitPim Correctly

Snowmageddon was the perfect excuse for me to initiate my migration over to Google Gmail and play with Google Voice.

I had to start by exporting and merging my contacts out of an LG EnV via BitPim, Microsoft Windows XP Address Book, and Microsoft Hotmail Contacts. The export to comma-separated values (CSV) in each application was straightforward. The merging I did manually in Microsoft Excel because Address Book and Hotmail stick to the human-readable Mobile Phone, Home Phone, Office Phone, but BitPim spits-out primary, secondary, and tertiary phone numbers as numbers_number, numbers_type three times over. As for email addresses and email types, Address Book and Hotmail do the primary, secondary, tertiary dance, while BitPim limits it to primary and secondary email addresses and types.

After having a cleaned-up contacts list, I was ready to charge forward. However, to my dismay, the Gmail Contacts CSV importer prefers a different format. It wants primary, secondary, and tertiary phone number and phone number type columns and primary and secondary email and email type columns, but the type column data is different depending on what sort of data a record has. So, while Home may work for one record, another record requires * Home. Mind-blowing.

With inspiration from others who have walked a similar path, I decided to write a simple Ruby script to convert contacts from a human-readable CSV format into something Google Contacts likes.

The format of the input CSV is as follows (you might as well throw these column headers into your input CSV as well):

First Name,Last Name,Home Email,Work Email,Mobile Phone,Home Phone,Work Phone

The output CSV matches what Google likes.

And here is the script:

# Simple CSV to Gmail Contacts CSV
# by Ian Lotinsky (http://ianlotinsky.com/)
# Released under the MIT License (http://en.wikipedia.org/wiki/MIT_License)

# Run like
#   ruby gmailcsv.rb my_contacts.csv > import_into_gmail.csv

require 'rubygems'
require 'fastercsv'
if ARGV.size != 1
  puts "USAGE: #{__FILE__} <original csv>"
  puts "  First Name,Last Name,Home Email,Work Email,Mobile Phone,Home Phone,Work Phone"
  exit
end

puts "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,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value"

FasterCSV.foreach(ARGV[0], :headers => :first_row) do |row|
  full_name = "#{row.field("First Name")} #{row.field("Last Name")}".strip
  puts "#{full_name},#{row.field("First Name")},,#{row.field("Last Name")},,,,,,,,,,,,,,,,,,,,,,,* My Contacts,* Home,#{row.field("Home Email")},* Work,#{row.field("Work Email")},* Mobile,#{row.field("Mobile Phone")},Home,#{row.field("Home Phone")},Work,#{row.field("Work Phone")},"
end

And because I wanted to be able to sync my contacts between Google and my phone in the future, I tried to write a script to transform a Google Contacts output CSV into a CSV that BitPim likes. But, alas, Google’s export CSV is inconsistent at best, so I wrote a script to go again from human-readable but this time to BitPim CSV:

# Simple CSV to BitPim Contacts CSV
# by Ian Lotinsky (http://ianlotinsky.com/)
# Released under the MIT License (http://en.wikipedia.org/wiki/MIT_License)

# Run like
#   ruby bitpimcsv.rb my_contacts.csv > import_into_bitpim.csv

require 'rubygems'
require 'fastercsv'
if ARGV.size != 1
  puts "USAGE: #{__FILE__} "
  puts "  First Name,Last Name,Home Email,Work Email,Mobile Phone,Home Phone,Work Phone"
  exit
end

puts "names_title,names_first,names_middle,names_last,names_full,names_nickname,numbers_number,numbers_type,numbers_speeddial,numbers_number,numbers_type,numbers_speeddial,numbers_number,numbers_type,numbers_speeddial,emails_email,emails_type,emails_email,emails_type,categories_category"

FasterCSV.foreach(ARGV[0], :headers => :first_row) do |row|
  full_name = "#{row.field("First Name")} #{row.field("Last Name")}".strip
  puts ",#{row.field("First Name")},,#{row.field("Last Name")},#{full_name},,#{row.field("Mobile Phone")},#{"cell" unless row.field("Mobile Phone").nil?},,#{row.field("Home Phone")},#{"home" unless row.field("Home Phone").nil?},,#{row.field("Work Phone")},#{"office" unless row.field("Work Phone").nil?},,#{row.field("Home Email")},,#{row.field("Work Email")},,"
end

And of course, after I got this all working, I remembered that there is a Google Contacts API that I should have tried playing around with.

I hope this saves people from the frustration I experienced and the time I wasted because of bad CSV importers and exporters. If you have an suggestions for improvement, please leave a comment.

About these ads

8 thoughts on “Importing a Contacts CSV into Gmail and BitPim Correctly

  1. graeme

    OMG, I am having the similar problem as you. I need to get my contacts out of a mysql database and into gmail contacts in an automated form because I don’t want to do manual updates.

    There doesn’t seem to be a lot of useful stuff out here for that. I too found the Google Contacts API but it looks overwhelming.

    I could export the data using phpmyadmin into a csv then cut and paste but how tedious this would be.

    I also need to make sure I don’t end up with duplicate contacts in gmail contacts.

    Reply
    1. Ian Lotinsky Post author

      Hi Bob,

      BitPim is the tool just about all mobile phone power users utilize to sync data with their computers–especially contacts. It’s worth a look. BitPim works with most phones, and it’s open source, so there might even be a way for you to utilize it in Scrubly so that it supports mobile phones too. (Although that might take quite a bit of effort!)

      Best of luck with Scrubly!

      Reply
  2. Jeff

    i know next to nothing about ruby, but this is what i’m running into:

    $ ruby gmailcsv.rb bitpim.csv > bitpimgm.csv
    gmailcsv.rb:18: syntax error, unexpected ‘=’, expecting ‘)’
    FasterCSV.foreach(ARGV[0], :headers =< :first_row) do |row|

    i've probably copied the script in incorrectly?
    im moving my old contact from an 8 year old phone to merge with another set of contacts to *hopefully* put into a new phone

    Reply
    1. Ian Lotinsky Post author

      Sorry about that, Jeff! Small typo; fixed now. I’ve gotten comments and emails from people thanking me for the scripts, so I guess they just fixed it themselves without telling me!

      Reply
      1. Jeff

        I ended up exporting from bitpim as full vcard 3 and merging it a few places- modifying the resulting file by hand. i had a huge list and i never contacted but ~10%. clean phonebook :)

        thanks for the reply though

  3. Jorian

    hi, I am trying to use your program (I’m not too good with computers)…I have my old phone contacts into bitpim, but what’s the easiest way to transfer that over to an excel doc? i can’t seem to copy paste it. once I have it in excel, how do I use your program do switch it to something google can read? thank you so much!

    Reply
    1. Ian Lotinsky Post author

      Hi Jorian –

      You can save your BitPim contacts as a CSV file through File > Export > CSV Contacts in BitPim. You can then open that CSV in Excel to edit it. If you want to use my first script to import that edited CSV into GMail, you will need to make sure that the columns are correct and have the right names. In the end the header row must be:

      First Name,Last Name,Home Email,Work Email,Mobile Phone,Home Phone,Work Phone

      (Excel will use columns instead of commas.) Make sure to save the edited CSV as a CSV (not an Excel file).

      As for running the script, you will need to install the Ruby language first. Then you will need to save my script as a ruby file (gmailcsv.rb). Then you will go to the command line terminal and run something like:

      ruby gmailcsv.rb my_edited_contacts.csv > file_to_import_into_gmail.csv

      And then you will need to import them into GMail.

      I hope this helps. Best of luck!

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s