Sharing CSV data in spreadsheets

At work I spend a lot of time on the command line. Some of that time is spent gathering tabular information into comma separated (CSV) text files.

Since CSV text doesn't paste into spreadsheets properly formatted, when I need to share this information with managers, analysts, or users, this has been my usual workflow

  • Gather the data
  • Save as .csv file
  • Download the .csv file from the server to my local machine
  • Open the .csv file in Numbers.app
  • Copy lines
  • Paste lines in Google Drive spreadsheet

Or sometimes instead of "Open..." on

  • Upload entire .csv file to Google Drive

Today, I finally scratched a persistent itch, and figured out how to quickly convert CSV text to spreadsheet text. First, it turns out that Numbers.app and Google Drive spreadsheets both use tab-separated values in copy/paste operations. That sounds simple enough but I struggled to make it work with sed for quite a while before switching to Python where I made this work

python -c 'import sys; print "\n".join([a.strip().replace(",", "\t") for a in sys.stdin])'

Now I've added that to my path as ~/bin/csv2tsv and I can shorten my workflow considerably.

  • Create CSV data
  • Copy CSV data to clipboard
  • Convert clipboard to TSV
  • Paste in spreadsheet

Yay! Next I'll probably add an Alfred workflow bash script with the following line.

pbpaste | csv2tsv | pbcopy