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.

iPhoto and durable photo management

When managing your digital photos, there are three things you really should do:

• Make backups of your backups of your backups.  These are your photos, don't mess about here - make backups regularly and store one of your backups someplace remote.
• Use JPG for your file formats.  If you end up with a camera saving in some goofy format either convert to JPG or get a new camera.  JPG is like mp3 is for music - it'll be a durable photo format that will be around for a very long time and is supported by lots of tools.
• Don't use software to organize your photos for you.  Use a simple directory format.  Software and their proprietary organization approaches will come and go but simple directories and folders will be around for a long time to come.  The following blog article is on this last point.

In 1999 when I bought my HP Photosmart C30 I started organizing my photos in a simple directory structure: Photos/YYYY/YYYYMMDD.  Something like this:

Over the years I would try whatever photo management software came with new cameras or emerging opensource packages.  l always regretted it as the photos would end up being hidden inside of some database and difficult to extract.  I would end up maintaining photos in two locations - the photo software and my simple archive.  That is, until I would get rid of the software and proprietary database files.

All that changed when I drank the Apple, iPhone and iPhoto koolaid when the iPhone came out.  The iPhoto software hits the magic "good enough" point Apple does so well and the integration with the iPhone Just Worked.  For the first few months I maintained iPhone photos in two locations - iPhoto and my simple archive.  But as I started using iPhoto more and more, using it to maintain photo albums, I just got lazy and one day stopped copying files from iPhoto into the archive.

I did continue to use my simple archive for regular camera photos but of course over time I started using the phone more and more for photos and conventional cameras only for special occasions like holidays.  I would pull in other camera pictures from the my simple archive into iPhoto to create albums.

I kept telling myself I could extract the photos any time - you can enter the iPhoto database structure as a filesystem (~/Pictures/iPhoto Library - "Show Package Contents"), similar to my simple archive approach.  iPhoto's File->Export function was there of course, but it would not properly set file modified date meaning it was more difficult to get into the right folder without looking at header data in the JPG.  Meanwhile, time goes on, and nothing lasts forever, maybe not even iPhoto.

That brings us to today and here's my strategy:  I've accepted that iPhoto will hold the "master" version of all iPhone (and more recently Samsung Galaxy s2 photos) and I'll occasionally extract these photos from iPhoto and fold them into my simple archives.

Here is how I did it:

1. Within iPhoto, Command-F search all your Photos for "iPhone".  Assuming you haven't otherwise marked your photos (title, tags, event names, ...) with "iPhone", iPhoto gives you a subset of your Photos created with the iPhone.

2. Within iPhoto, use File-Export to save these photos to the filesystem.  They'll be saved as one big set in whatever directory you specify.  I named the files with prefix "iphone" and selected the option to sequentially number them.  That way I can always go searching in my simple archive for iPhone photos by filename if I need to.

3. Use MacPort.org's "port" command to install jhead.  Run "jhead -ft *.JPG *.jpg" at the shell to correct all the modified dates so your files are date/time stamped with the date/time the picture was taken.  I also had a handful of .PNG (screen captures) and .MOV (movies) files and I just left these dates as they were.  Probably wrong, but I only had a few.

4.  I wrote a small shell script to organze the extracted pictures by date to match my simple archive format.  Here it is:

# 
# orgpix.sh - organize iPhoto exported photos by date 
# Files will be moved to into a directory structure like this: 
#
# YYYY 
#    YYYYMMDD
#    YYYYMMDD 
#    ... 
# YYYY 
# ... 
# 
# Place this file in the directory containing all the files you want 
# to organize and run it from there. 
# 
# 
ls -1 *.jpg | while read fn; do 
   eval set `stat -f "%Sa" -t "%Y %m %d" "$fn"` 
   export year=$1 
   export month=$2 
   export day=$3 
   echo "$fn : $year $month $day" 
   mkdir $year 2> /dev/null 
   mkdir ${year}/"${year}${month}${day}" 2> /dev/null 
   mv "$fn" ${year}/"${year}${month}${day}" 
done

5. Used cp to merge the newly organized files and directories into my main photo archive:

cp -Rvpn 2008/ /Path/to/Photo/Archives/2008

(I was tentative.  I ran it for each year by hand as I wanted to make sure it was working properly by checking a few file and directory creations as I went.  This could be scripted as well.)

So there you go.  All photos taken by the iPhone extracted from iPhoto and merged into my simple but very durable photo archives structure.

I repeated the above to extract my Samsung Galaxy s2 (Command-F search for "GT-I9100") photos as well.

Postnote: If you can't subset out the photos taken by the iPhone within iPhoto, you could just extract everything and use jhead's EXIF header reading to determine what camera took a photo.

From laptop to iPad

A few weeks ago I decided to leave my Mac OS X laptop in the rucksack for a few days and just use an iPad for everyday use as a test of the iPad's viability for typical daily use.  The work included some airplane travel and a visit to a remote office.  I also had an external Bluetooth keyboard with me in case I had to do a lot of heads-down writing.

The following is an examination of why the iPad doesn't yet fill my laptop's boots.

The Basics

Browsing generally works fine, even a few fiddly banking sites.  I miss being able to save sites to a storage area for later reading.  While there are services like Instapaper, I want to save pages to place where they can be search indexed (a filesystem!).

Email isn't a replacement for laptop email.  Most fundamentally, you can't create a new folder in your IMAP folder set.  That's a deal-breaker.  You also can't restore from trash or mark junk email.  And you can't meaningfully search email content from within the email app.  Lack of deep content search is another deal breaker.

Skype works fine, except no video (I'm on an original iPad).

Flipping between local country SIM cards for data services as I moved between countries worked fine.  New APN settings popped in without any manual intervention.  I sometimes have to reboot between SIM changes or at least flip the iPad in and out of flight mode for the new SIM to start working.  Annoying, but not earth-shattering.

Note Taking

I tried using mail messages, Plaintext, and Notes for taking notes.

I ended up primarily using "Notes".  Flipping between notes is useful.  The iPad is particularly good in meetings, especially one-on-ones as it doesn't get in the way between you and others like the display of a laptop does.

I tend to eventually convert Notes to emails for long-term archiving in IMAP, or text snips are extracted to put in docs.

However, Notes isn't perfect.  There are problems with how Notes synchronizes between devices and being on and off line.  I commonly end up with multiple copies of the same note even though I've only been using one device to edit the note with.

Plaintext's dropbox integration is compelling.  If Plaintext supported rtf files (default file type of OS X's notepad app) and some basic markup, I'd probably switch to it from Notes as my primary note taking tool.

Taking notes in the email app isn't viable because the message you're writing effectively locks down the email app interface.  I need to be able to quickly flip between taking notes and checking email without having to save and restore my notes from the draft folder.

It would be very useful to have some basic text markup capabilities in Notes, Mail and Plaintext such as lists, bold, and highlight.

Offline usage

A lack of access to files is annoying.  iOS forces a strong siloing of data by application with some sharing, but not uniformly enforced.  Flexible cut-and-paste and an underlying filesystem are very useful and general purpose ways to share information between apps and neither exists on iOS.

To an extent, dropbox and me.com can act as a filesystem.  However, without control over caching policies, being able to pre-load the cache, and lack of universal integration, they just don't cut it as a real filesystem substitute.

Ideal integration for me would be between apps like mail, Plaintext, and iWork with dropbox and iDisk working properly under them.  In particular the iWork apps (equivalents to Pages, Numbers, and Keynote) work very poorly relative to iDisk, little less dropbox.

For example, when I open an Excel doc via Numbers from an email attachment, it's a one-time shot.  The file is not being stored in a central, synchroized spot if I make changes.  I have to remember the doc is being held in Numbers and extract it later.

Network/Cloud Storage - dropbox and iDisk


Dropbox continues to wow me and iDisk continues to disappoint.  I'm continuing to put more and more into Dropbox.  About the only items I don't have in Dropbox now are large storage requirements items such as my local storage mail archives, iPhoto database, software downloads, Music, Sites, and vmware+images.  Adding all of these would mean a significant annual cost for dropbox.

iDisk continues to be painfully slow and I'm not fully confident of its syncing (n.b., I just found another legacy folder structure artifact with no files under it that shouldn't have been there).  I continue to use iDisk for backups only as I still don't trust it or find it sufficiently usable for primary storage.

Even if iDisk synchronization and app integration is radically improved, it still suffers from poor adoption and multi-device availability as compared to Dropbox.

Miscellaneous

The iPad needs some way to dock to a keyboard, monitor and mouse in a generalized (not app by app specific) way.  Otherwise, creating and maintaining Numbers, Keynote and Pages documents on the iPad is pretty-much out of the question as the screen is too small.  At best the iWork applications can be used for quick last-minute edits.

The lack of meaningful search is a deal killer.  I really miss search as directly integrated into Mail and the Desktop.  Search is likely a resource hog to maintain - memory, disk, power.  The iOS desktop search is just too shallow, not readily available and focused within apps.

I'd really like cache management under explicit control.  Email, attachments, Notes, Dropbox, and iDisk.  I'd like to be able to "preload cache" when I have a fat wifi connection available.  I'd like to be able to specify the maximum size of each cache.  I'd like to enable "background" caching so the app isn't restricted to only caching when it's in the foreground.

There is no undo or ^Z in mail (and I assume most other apps) in case I accidentally delete something.

Pages was so slow as to be effectively unusable on a 20 page MS Word document.

I often encounter links to pages I want to save for later review.  They can be from email, a news site (e.g., BBC), general surfing (e.g., Safari), tweets (e.g., tweetdeck), or a feeds manager (e.g., flipboard).  I want to save these pages to a place that is search indexed (e.g., via spotlight).  I end up having to mail the link to myself and browse/save the page later from my laptop.  I don't consider instapaper or Evernote to be adequate.

On the positive side, the convenience due to the iPad's size is tough to beat.  It strikes a good balance between portability and usability.  You can unobtrusively carry and use it almost everywhere.  And the long battery life means a full day of use between charges.

Conclusion

Switching to full-time use of an iPad would be possible if:

• Apple significantly improves their synchronization capability, both with IMAP (Notes) and iDisk.
• Apple provides a better note-taking tool with some basic formatting, ability to flip quickly between notes, and integration to Dropbox (or an iDisk that works as well as Dropbox).
• Add Undo in a standard way to most apps that edit, move or delete objects.
• Add a pervasive search function that is available within applications and implicitly scopes its search to that application data.
• Be able to create IMAP folders in mail
• Better, explicit cache management for cloud storage
• Better way to save web pages to a centralized search-indexed location

The new iPads have been out a few weeks as I write this.  They should help with a few of the issues I mentioned above (Skype video, speed), but most issues are software and iOS design related.

Dropbox continues to shine as the best network, synchronized, shared, multi-device/OS filesystem on the market.  Adding improved cache controls by device would be a great feature addition.  iOS (or any) app makers would be wise to provide seamless integration to Dropbox for cloud file management.

Given all these iPad/iOS issues, the iPad remains appropriately only for more casual and mobile meetings.  It is still only a supplement for my laptop, not a replacement.

(For completeness, here are other apps I'm using regularly during a typical week for work.  They all function adequately:  Lonely Planet City Guides, FlightTrack, Flipboard, Kindle, PlainText, Tweetdeck (add LinkedIn integration!), Skype, and GoodReader.)