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.

Dropbox Security, From TrueCrypt to BoxCryptor and 1Password

(If you want to skip the below and just get the recommended answer, go buy Boxcryptor and 1Password on all your platforms.  Job done.)

When Dropbox had various security issues last year (the no passwords required for some hours was the kick I needed to sort my security out), I started using Truecrypt to contain all sensitive material I was keeping in Dropbox.  Truecrypt felt good as it was opensource, free, stable, secure, and reasonably usable on OS X and MS-Win.

While I felt a 1000x better about my security situation, I also lost a lot of the convenience of Dropbox by moving to Truecrypt:

• File sync.  Truecrypt stores its filesystem in a single file.  While Dropbox is efficient at syncing big files at a block level, it doesn't cope well with changes to that file happening roughly concurrently from two or more locations.  If you mount your Truecrypt filesystem from two or more machines and make even vaguely concurrent changes (within a sync activity for example), you end up with two conflicted Truecrypt files.  One quickly learns to only open the Truecrypt volume on one machine at a time.
• Multi-platform access.  One thing Dropbox did well was to have clients available on all major platforms.  I could access my Dropbox files from OS X, MS-Win, iOS, Android and Linux.  When I switched to TrueCrypt, I was limited to PC, Linux and Mac only (and one at a time at that), no mobile/tablet access.
• Password management.  I won't say much about this other than it became harder using Truecrypt.

That was last year.  One of the great things about tech is that problems that need solving tend to get solved if you're patient enough.

Enter Boxcryptor for file security and improvements to 1Password for password management.

While there are a number of solutions available to encrypt what you store in Dropbox, I consolidated onto Boxcryptor:

• Secure.  Uses AES-256.  No cloud aspect to Boxcryptor and therefore no third party has my master key and can take a peak at my data.
• Plays nice with Dropbox.  Boxcryptor uses a folder+file structure (aka "package" on OS X) with each file encrypted separately enabling Dropbox efficiently sync.
• Multi-platform access.  Working clients on all major OSs.  At least read access on iOS and Android.
• Stable.  I've not had a single crash or corruption yet (although I'm still backing up more frequently than I might otherwise).
• No major delays in supporting the major OS upgrades.
• It allows for up to 2GB for free and more if you license it.  2GB is a lot.  Once I got comfortable with it I bought a license to get rid of the 2GB restriction.  I feel the license is a nominal cost versus the upside of more user friendly security and vendor support.

I considered Datalocker, Cloudfogger, Hyperdrive, and encrypted zip files.  All of them failed in one or more of the above.

An aside on Dropbox and sharing files:  I don't retain Dropbox's easy sharing of (encrypted) files using Boxcryptor.  Encrypted zip files still perfectly acceptable and secure way to e.g. share a single file in Dropbox with colleagues so long long as you unzip into a secure location and not into Dropbox.  Then you have to zip+encrypt and move the result back into the shared folder in Dropbox.  Zipfile usability compared to regular Dropbox sharing and syncing is poor as a result.  Note that today Boxcryptor doesn't appear to (easily) support multiple concurrently-open Boxcryptor filesystems.  When it does I could see having a Boxcryptor filesystem dedicated to sharing a set of folders/files with a specific workgroup.  Each group to have its own Boxcryptor filesystem - still somewhat painful but better than zip files.

Moving on to password management.  I have to admit my previous method wasn't overly secure and certainly TrueCrypt decreased it's usability.  As I was digging into secure storage, I also had a hunt around for how to improve password management.

Enter 1Password.  Yes, it's been around awhile, but used to be very OS X centric.  I don't know when they went multi-platform but they have.  While they've been the premium (i.e. expensive!) choice for OS X password management for awhile, the lack of support for other platforms had always been a showstopper for me.

Here is the thinking that led me to 1Password:

• Multi-platform: MS-Win, OS X, iOS, Android.  It's not on Linux, but I don't use a Linux desktop for the 1Password primary use case anyway.
• Secure.  While I can't keep 1Password's database in Boxcryptor's filesystem (I could, but I lose mobile/tablet access), the 1Password security approach is fine.  My passwords don't go to another third party password service to maintain them.  While Dropbox has my password files, they are encrypted.
• Plays nice with Dropbox.  The 1Password DB is also a folder+file (package) structure, just like Boxcryptor.  As a result, Dropbox syncing works well.
• Well supported browser plugins.  I use Chrome and Safari and both are well supported.  Support isn't quite so good on mobile/tablet platforms, but it's better than what I had before.
• Widely used.  The tech community seems to widely use it.  While not a particularly scientific measure, it seems to be on its way to being a "best practice" solution in my peer group.

I've now deployed 1Password's database into Dropbox.  It'll take me awhile to load all my credentials into 1Password but I think it's a durable investment.

One downside is that 1Password isn't overly cheap.  You have to pay for licenses for each platform (Android still free).  However, just like with Boxcryptor, I think it's worth the cost for the stability, support, and commitment to keep up with OS changes.

I did have a serious look at and play with Keepass for password management.  I like that it's free and opensource.  I liked aspects of it's design and usability.  However there were a few factors that put me off:

• Fiddly.  There are two different and somewhat competing database and application tracks, 1.x and 2.x.  Both are under active development.  There are various "unofficial" platform ports of each track to various OSs.  You have to pay attention to what version you use on e.g., OS X to make sure it's compatible with the version you use on iOS.  
• Not keeping up with OS upgrades.  The main OS X port indicated support for OS X 10.6 as most recent and today OS X is at 10.8.  I don't want to be the beta tester for new Keepass releases - what I'm securing is too critical to mess about with.
• The Keepass database is a single file, meaning that like with TrueCrypt you might have to deal with Dropbox sync collisions.

As a result, I'm an even happier Dropbox user now that I have secured files and passwords and reasonable usability to access both.  All in the licenses across all the platforms for both Boxcryptor and 1Password cost me about $125 (£80).  Yes, this is a lot, but conversely I now feel like I have the best of both worlds - the convenience of Dropbox and the comfort of strong security where it's needed.

Pesky winmail.dat with Outlook, Apple Mac OS X Mail and me.com IMAP folders

I use me.com's IMAP folders to file email as part of an inbox zero policy I inflict on myself.  I recently had to resume using Exchange 2010 based email, but unfortunately not (yet!) directly connected to Apple Mac OS X Mail.  To access the Exchange mail, I've elected to use Outlook inside a Windows 7 VM.  I then hooked in my me.com IMAP folders into Windows Outlook so the Exchange and me.com folders are side by side in Outlook.  I then added an Outlook rule to copy new email that hits my Exchange Inbox to a me.com's Inbox.

This worked ok until I opened my first message in OS X Mail that had an attachment.  Instead of a normal attachment, I instead found a "winmail.dat" file.  Funny, I'd not seen one of those in a long time.

After some digging, I came across two ways to deal with the winmail.dat attachment problem:

1. The first and not recommended choice is TNEF's Enough.  The price is right at free/donations.  However, it's usability is awkward as you are required to open the *.dat file in a separate application that unpacks the TNEF format file from Exchange/Outlook and gives you the option to save the file.  I tried it, too painful.

2. The second and recommended choice is Letter Opener.  It is between USD 30-50.  It is seamlessly integrated with OS X Mail so you don't ever see a *.dat file, you see the attachments as you would in Outlook.

So if you stumble on this problem and need to consider TNEF's Enough versus Letter Opener, I'd recommend Letter Opener.

Using wget to ask jspwiki to re-index its search DB

For whatever reason, our installation of jspwiki (v2.8.2) decides to ignore or lose pages out of its index (hey, what do you want for free?!).  With our jspwiki hitting 2000 pages, search is the main tool to find pages. Unfortunately, I've taken to keeping my own links page to important pages just so I don't lose them as the search indexing seems to break regularly.  While a re-index solves the problem, but it requires going into the site, authenticating, and clicking a button - way too much work.

Here is a quicky to use wget to log in to jspwiki and force a re-indexing of pages:

# POST to log in and get login and session cookies
wget --verbose --save-cookies=cookie --keep-session-cookies --post-data="j_username=myuid&j_password=mypw&redirect=Main&submitlogin=Login" "http://wiki.mydomain.com/JSPWiki/Login.jsp" --output-document "MainPostLogin.html"

# POST to kick off reindexing using cookies
wget --verbose --load-cookies=cookie --post-data="tab-admin=core&tab-core=Search+manager&bean=com.ecyrd.jspwiki.ui.admin.beans.SearchManagerBean&searchmanagerbean-reload=Force+index+reload" --output-document "PostFromForceIndexReload.html"  "http://wiki.mydomain.com/JSPWiki/admin/Admin.jsp"


Tweak myuid, mypw, and wiki.mydomain.com in the above to have them be what you need.  Drop the output once you're comfortable it's working (I was saving it in the above to make sure I could see artifacts of being authenticated in the output).

Put the above into a cron'ed script and run it hourly.

Note that all versions of wget are not created equal as 1.10 didn't seem to work but 1.10.2 and 1.12 worked fine for the above.

Using wget and google to download and translate websites

There is a website for the neighborhood I live in that is all in Spanish (cuartonparque.com).  So that's useful if your Spanish is good, which mine isn't.  Google's translate function is great, but I wanted an archive of the site both in Spanish and English in case the site disappeared or was substantially altered.

wget is a great command line *nix utility to recursively download a website providing the links are statically constructed.  I use wget on OS X (install xcode and macports to enable installation of wget if you don't have it).

For cuartonparque.com, the wget command is straight-forward and well documented.  The site uses simple static links and only has a few levels of linking. To download the site, I used:

wget -rpkv -e "robots=off" 'http://cuartonparque.com' 2>&1 | tee cuartonparque.com.wget.log

This command creates a cuartonparque.com directory with a browsable website.

To download a translate.google.com version of the site was trickier.  Although various googled pages helped a bit, I couldn't find find an example that actually worked.  After some hacking about, I uncovered the required tricks to make this work:

• Google appears to only process requests from browsers it's familiar with (use -U Mozilla)
• Google uses frames and changes it's domain name a bit as it translates (find out the final URL of interest by digging around in the page source)
• Safari really likes a .html extension on files it opens (use --html-extension)

My pain is your gain.  Here is the wget command that downloads the translated version of the website:

wget -rpkv -e "robots=off" -U Mozilla --html-extension 'http://translate.googleusercontent.com/translate_c?hl=en&sl=es&tl=en&u=http://cuartonparque.com/&rurl=translate.google.com&twu=1&usg=ALkJrhjabXZlzJpBCZeWpsmLaKss09lCuQ' 2>&1 | tee -a cuartonparque.com.En.wget.log

wget creates a translate.googleusercontent.com directory with a browsable website, localized from Spanish to English with a horrific URL for the index.html page:

file:///Users/xyz/Downloads/Web%20Sites/cuartonparque.com.En.Google.Trans/translate.googleusercontent.com/translate_c%3Fhl=en&sl=es&tl=en&u=http:%252F%252Fcuartonparque.com%252F&rurl=translate.google.com&twu=1&usg=ALkJrhjabXZlzJpBCZeWpsmLaKss09lCuQ.html

A quick browse around on the downloaded version suggests everything came through, nicely translated to English with wholly-formed pages.  Enjoy!

Synology CS407e NAS Cube Station and file move/copy errors

I've been using the Synology Cube Station CS407e as a home NAS for about a year now, and up until a few weeks ago it worked flawlessly. I'm only using it as a network attached media server, not all the other whizzy features it has.

The setup: I have three different machines at various times coping and and moving files around on the box. Trying to be overly clever, I set up a user "write" on the Synology with write permissions so if I had guests in the house using the media server they by default couldn't clobber any files with the default guest access.

Several weeks ago I started having permissions and file move/copy errors. The errors didn't make sense given the relevant context. I tried relaxing permissions by enabling user guest with full read-write permissions. I tried changing login credentials and permissions from the three different clients I used to copy and move files around. Various brick walls. And yes, I was on the latest firmware Synology had available for the CS407e.

I then noticed that I could ssh into the Synology. As I said, I had just been using it as simple NAS. Viola, after logging into it (btw, admin and root logins have the same password) I had access to chown/chgrp via the Unix like busybox interface and tidied up the permissions.

While that solved the problem, it's worth noting that it appears something about the permissions handling on the Synology is broken. The user and permissions setup via the web interface on the box isn't consistent with the errors happening (I had enabled guest with write permissions, still got client side errors) and what I saw at the filesystem level via the command line. So while repairable (ssh interface to busybox), the repair isn't viable for non-techs.

If you're non-technical and not comfortable working at a Unix command line but you still want to use the Synology, I suggest you do not set up other users. Just stick with user guest with the permissions set wide open.

If you've already dug the hole for yourself as described above, I think you can copy everything someplace else, delete all the offending files/directories, re-create a new directory structure with the guest user, and copy everything back.

Lastly, don't let this one problem put you off from considering a Synology for your home NAS.  I've had reliable use of the Synology CS407e for over a year now (as compared to two different Sans Digital MN2Ls previously - yikes, stay away).  It's quiet, reasonably fast, has a top quality management user interface and it Just Works (assuming you don't monkey with non default users!).

Synology company home page

CS407e product overview

CS407e technical specifications

Download CS407e firmware upgrades

Synology CS407e NAS Cube Station at amazon.com