How to extract a contact list from BambooHR and import it into Google Contacts

BambooHR is an HR SaaS used to manage employees.  It is typically used as a system of record for employee details.  Unfortunately, BambooHR doesn't make it easy for your digital address book to use the contact information it contains.  Two ways the product could be improved to help out its users in this respect are:

• Offer CardDAV service.  This is by far the best option.  This would enable you to connect iOS or OS X directly to BambooHR leaving Bamboo in charge of employee data and minimal messing about.
• Export Contacts a friendly format:
• Google Contact's CSV format (which implies very specific column requirements, naming and order)
• Apple's VCF (contact card) format

As these options aren't possible we'll fake our way into the second option by using BambooHR's ad-hoc reporting tool to create a CSV file we'll eventually import (with changes) to Google Contacts.

1. Create a bamboohr report that includes the employees you want along with the fields you want.  Download it in CSV format.

Here is an example of some of fields I selected using BambooHR's custom reporting tool:

2. Enter Google Contacts.  Create a single contact that has sample data in all the fields you want have available for your contacts.   It is possible to create custom fields for data that doesn't directly map from BambooHR to Contacts using Custom fields in Contacts.  For example, I created a custom Contacts field for employee start date:

I used other custom fields for line manager and office location.

Now export a CSV version of the one contact record you've created.

3. Open the Contact CSV file in OS X Numbers (Excel, at least on OS X, doesn't work with UTF-8 encoding and trashes special characters).  You'll note many unused column headers.  Their inclusion, names and order are important - don't change any of them.  Google Contacts import is really picky when it imports data and generally won't work if you don't have the exact columns it expects.

Some of the columns will have static values.  For instance I ended up with a "Relation 1 - Type" column that I had to repeat "Line Manager" for all rows.

4. Open BambooHR CSV file in OS X numbers.  Remove the one test record.  Drag the columns from the BambooHR CSV file to the Google Contacts CSV file making sure you preserve column names and order.

Note: You MUST preserve column inclusion, names and order for importing to work.  Most of the problems and unexpected results I had with this process was because I hadn't done so.

If you've exported any dates like employee start date from BambooHR you'll need to change them from the default MM/DD/YYYY to YYYY-MM-DD which is what Google Contacts import requires.

5. Export the results from Numbers as a new CSV file.  At this point you should have a new CSV file that is a merge of Google Contacts columns and all contact data from BambooHR.  Many of the columns will be empty.

6. Before you import anything, make sure you take a backup of any existing contacts you have.  You may also to decide to delete the contacts that you already have in place before you import anything.  Google Contacts provides a merge function which works pretty well but I've found its better to backup/delete my current contact set and start with an empty upload area.  In particular we might hand off a mobile phone number from a previous employee to a new one meaning that two employees will have the same number if I don't clear out the old ones.  If you make changes to the original contacts or add notes to them you'll be stuck - you'll need to rely on a merge.

At this point you have a backup (if you want one) and you've decided whether you're starting with a clean slate or will merge.

7. Import your new CSV contacts list into Google Contacts.

Run Google Contacts merge if you need to.

8. Mark the newly imported set to be included in “My Contacts” (by default it isn’t).

9. Assuming you've connected your Google Contacts to OS X and iOS Contacts apps it may take a few minutes for them to appear.

And that's it.  Depending on how often you add/change/delete employees in BambooHR you'll have to repeat this every few months to make all the new changes available.  You can help your colleagues who would benefit from your fancy new contact list by exporting and sharing VCF (for OS X, iOS) or Google CSV format files from Google Contacts so others can import them.

---

Postnote: BambooHR has updated some of their default report column header names to match Google's Contacts header naming convention.  If they would also allow for user-defined column names and an optional static value for these custom columns in their ad-hoc reporting tool that would be really useful as well as you could basically build the CSV file in exactly the format needed by Google Contacts import.