Opening CSV Export Files From Strip Sync
Updated: 6/6/2012 This blog post is out of date, and the software referenced in it, Strip Sync, has been discontinued in favor of Strip for Windows and Strip for OS X. For more information on our data export format, please see this newer blog post.
Strip Sync is a free tool we distribute for customers of our password manager, Strip. It’s a kind of hold-over until we’ve got a proper desktop port of Strip that provides some basic but necessary capabilities, in particular the ability to bulk import your data (say, from another program or source), and to export your data (so you can print a hard copy to store in a safe, or so you can move off Strip someday).
The import and export files are CSV files. That is, plain-text files structured in a particular way, that can be opened in a spreadsheet programs like Excel. A good many people out there are familiar with spreadsheet programs, but Excel has some issues when it comes to opening a CSV file and displaying the data correctly:
- UTF-8 is not supported when opening a CSV, ASCII is assumed, so international characters turn into garbage.
- Leading zeros on numbers like PINs and postal codes are lopped off.
Our international customers, being quite fond of their umlauts, have written us a few times about the first problem, one that MSFT does not appear poised to fix this century. The second issue can be a real nuisance if you have a column like “Zip Code”. Excel always assumes such data is a Number (even if it’s wrapped in double-quotes), and if you format the column as Text after you’ve already opened it, the zeros have already been lopped off. Obviously, this isn’t a show-stopper, but it’s not appropriate for printing a hard copy of your secret keys for safe storage.
Looking for another work-around, I tried using Excel’s “Get External Data” wizard to import the text from a CSV, but it doesn’t respect line-breaks in quoted text fields, so even though you can mark your Zip Code column as text on the way in, the rest of your spreadsheet gets wildly munged by any line-breaks you might have in a note field.
This morning I fired up Open Office.org for the first time in a long while to see how it’s spreadsheet program Calc handles this, and it’s just the thing for our customers. Did I mention that it’s free? Here’s how it went down: I exported from Strip Sync, I right-clicked on the file, selected OpenOffice.org, tweaked some settings on the dialog panel that came up, and I was in business.
Step-by-step
First, right-click (or control-click) on the export file, and instead of opening it with Excel, go with OpenOffice.org.
When the Text Import dialog pops up, take a look at the Separator Options: the only one you should check off is Comma. Make sure Text Delimiter is set to straight double-quote (“).
Next take a close look at the Fields listing, and scroll over to any column that contains numeric data that you don’t want munged, e.g. PIN, or Zip Code, etc. Select the column header, and choose Text from the Column Type pop-up to ensure that leading zeros won’t be lopped off.
Then open it up, you’re all set! You get to keep those leading zeros.
From here you can manipulate and format the spreadsheet for printing however you like, or you can export it to a native Excel workbook format if you prefer to work in Excel. Excel does support UTF-8 characters, just not when opening a CSV file.
blog comments powered by Disqus