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 (https://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 (https://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.