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.

The Unplugged by Ruven Meulenberg

While researching user experience design techniques, I stumbled upon some nifty whiteboard magnets for prototyping called GuiMags as well as a complementing book called The Unplugged.

GuiMags look like the nicest way to prototype something before going to HTML and CSS. Labor intensive forms of prototyping don’t seem to add much value, and paper and traditional whiteboard prototyping only works until you’ve changed your mind about something and have to throw your work in the trash or erase half the board.

Although I decided to postpone a magnet purchase until I am doing design again, I was able to get my hands on the book. Its premise: we limit ourselves by the technologies we use. Instead of thinking outside the box, we’re often thinking and functioning in it. A large part of this thinking inside the box is how we develop software.

Although, everyone interested in the topic should pick up the book, here are a few of my takeaways:

• Every major form of art that involves technology (music, film, video games, graphic design) starts outside technology. Artists do not limit themselves by their technology but by the limits of their own minds. As a software engineer, you often limit yourself by the technology you use day-to-day.
• Spend as much time as you can iterating on concept and design before going to implementation.
• Design the software front-end not the back-end first.
• Just like there are code freezes, freeze the product when it has passed the design phase.
• It is often wise to outsource the implementation.
  • This serves as a peer review of the design before it goes to implementation. Software developers traditionally think about the back-end first.
  • Different cultures have different strengths: “England and Western Europe are great at design, Ukraine and Macedonia have amazing and prompt developers who can think for themselves, the Netherlands always emails back the same day, India is extremely polite, etc.”
  • Work can be done while you are sleeping. “This can cut the development time in half.”
  • Because you already know what you want and won’t be constantly changing the design, contractors will want to work with you even if you pay less.
  • Only be satisfied with five-star developers.
  • Pay more than you agree to pay.
  • Do one-week sprints. Longer sprints end up getting delayed, with excuses.

With the last (sub)point in mind, I think this methodology is well-suited for an agile development process.

There is a lot to gain from reading the book, so make sure to grab a copy for yourself.

Did Evite and Facebook Ruin Commitment?

I’ve noticed something interesting over the past decade: commitment to social events has plummeted. When I was young, someone would plan a party or event, send out paper invitations, and get responses from people stating whether or not they were coming. It worked wonderfully. The host knew everyone got the invitation and knew how many people to plan for. It also built a sense of excitement for the get-together as people found out who else was going and speculated on what the day may hold for them.

When Evite became the normal way of sending invitations, people treated them the same as paper ones even though there was a new socially-acceptable response called “Maybe.” People responded with either a “Yes” or “No.” One or two people would say “Maybe” because of some other prior commitment that may not have ended in time. What used to be a “No” was now a viable “Maybe.” However, during the years of Evite’s reign, the “Maybe” population grew. More and more people were responding “Maybe,” not because of prior commitments, but because of subjective reasons. People confessed that they didn’t know whether or not something else might pop-up that day or if they would feel like participating the day-of.

Now that Facebook Events has begun to overthrow Evite, people have become accustomed to using “Maybe” more than “Yes”–especially if the event hasn’t reached its tipping point–the point at which an event gains momentum because of size of the “Yeses.” Often, people don’t even respond but use “Remove from my events” instead. Even the “Yes” and “No” responses aren’t certain–they tend to reflect the invitee’s excitement level about the event rather than his commitment to attend. On several occasions, a “Yes” response meant nothing because the invitee never checked her calendar.

Did Evite and Facebook ruin commitment?