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

Ragnarok 2014

Update: I really appreciate the effort to put on the Rok. I love these rides and the people. I had "fun," of a sort. And I'm pleased to finish in these conditions. The post I wrote below sounds way more down than I intended.


Saturday was my second ride in the Ragnarok 105, a gravel grinder between Red Wing, Zumbro Falls, and Lake City, Minnesota.

The highlight of the course is long winding river valleys, perfect for this slow 46-year-old singlespeeder running 39x18. The low blow of the course is the turning over the last cue sheet and remembering you have to go 107, not 100 total miles.

The forecast called for scattered rain in the morning, clouds in the afternoon, and temps from 49 to 59. Planning for that was a challenge. I wanted to make up for my DNF at Almanzo 2011.

I dressed in bibs, long sleeve wool t-shirt, all-purpose hooded shell, and mid-weight tights. I hoped to take off the jacket and tights in the afternoon, but that did not happen as the temps stuck in the 40s all day. I needed luck and help to stay warm enough in the first hours after the rain.

The first leg was a slog. There was rain. There was lightning, which didn't worry me but should have. There was peanut butter road surface. There was mud (later sand) everywhere, inside and outside my clothing. My CamelBak zippers seized up.

I do not own gloves or shoes suitable for long rides in cold rain. Gloves on my hands and gloves in my pack were both soaked after 2 hours. My wool socks tried, but the water splashed on my shins drained into my shoes, and the wind sucked the heat out of my feet. Mark and TC got me out of the BP in Zumbro Falls with a tip to buy the last pair of ugly yellow felt gloves, and encouragement to layer sandwich baggies between socks & shoes. I started warming up soon.

The second leg was fun. I was almost always alone. I jammed some Girl Talk in my headphones. A rider passed me and said something like "ah single speed, you're a hard mother fucker!" I basked in that. Somewhere after the park with the ultra running races I stopped and read the cues in reverse for some people in a car looking for their rider. I think they found him later.

I caught Mark and a couple others in Lake City. We shared compliments and complaints. I ate too much carrot cake, then I rode the last leg with an upset stomach. I interrupted someone welding in a farm shed to ask "is that unmarked turn Orchard?" He saved me a lot of time since I couldn't find the spoke magnet for my odometer before the race.

Mark brought the beer for the finish. That's a great friend. I still need to finish laundry, clean up my shoes, and replace every ball bearing on my bike before Almanzo...

Civic Hacking at Minnebar

Minnebar 8 (2013)

12:00 - Calhoun - Civic hacking

@opentwincities

Bull Bushey

  • open something...
  • employee of e-democracy.org

What is civic hacking?

  • Not hacking into government servers! :-)
  • E-gov commons.wikimedia.org/wiki/file:egov.svg
  • Neat Obama/Fairey-style "Yes We Code"
  • Open science (createivecommons.org/weblog/enty/32434)

What is open data?

  • Yin to civic hacking yang
  • Civic hacking is hard to do without open data
  • 4 levels of open data
    • legal access (do I have the right as a citizen to get information? MN chapter 13 data practices)
    • equitable access (free or $$?, how long to get it?) (I have the right, reasonable cost, reasonable timeframe.)
    • structured access (what do I get? just a PDF? or print?)
    • real time access (== "there should be APIs")

What is happening around the USA?

  • taken off in last 5 years
  • citycamp bit.ly/d8tWvm unconference
  • plug for e-democracy
  • code for america, SF non-profit, 4 years old, focal point for civic hacking, four programs
    • fellowship, brigade, peer network, accelerator
  • sample projects
    • adopt a hydrant (Boston, shovel out after snows)
    • open 311, see click fix (report problems to governments) (open 311 is an open standard for this kind of messages)
    • ckan.org - repository for data sets (also, "the data hub", also "DKAN" using Drupal)
    • see projects on code for america site

What is happening in MSP?

  • open twin cities, with partners code for america and e-democracy.org
  • adopt a hydrant twin cities is up
  • free geek twin cities every 4th tues PM
  • open data day hackathon (late feb 2013) (first in MN?) 20 people, four applications
  • upcoming
    • visualizing neighborhoods "hackathon for good" may 25th at Minneapolis central library
    • national day of civic hacking Jun 1-2 at DevJam Studios "Hack for MN" (see Colin ?)
    • citycampminnesota - fall 2013 - unconference - planning stages

Audience Questions & Comments

12:25 PM question re: "exposing government corruption" A: we have too much low-hanging fruit. City of Minneapolis rec'd rating "D-" from Sunlight Foundation. We have a progressive state, but not up to speed on sharing data. Trying to get more data sets made available in MN & MSP. How can Minneapolis/MN catch up with coasts? Open data is an issue in Chicago.

12:30 PM question "lots of public data, becomes sensitive if put together" Answer: "data practices act" - Can I get snow emergency email address list? New legislation for privacy classifications of data... what should or shouldn't be published? We want more open data, but sometimes it doesn't make sense. Reference to case law from journalism.

12:34 PM Audience: Government Technology Magazine good source

12:34 PM note some states & cities are starting to adopt EU-style personal data rules, more restrictive than historically in US

12:35 PM Q: What are most popular tools? A: Agnostic, prefer to prototype or build quickly. Whatever people want to do, we will help.

12:38 PM Flyers going around the room for tcneighbors.org

12:39 PM All of these organizations need donations and/or corporate sponsorship.

Adobe, Grrr!

Today I made the mistake of downloading and installing Adobe Reader 11 on my Mac running Mountain Lion. I thought it might solve some issues I was having opening bank statement downloads. (It didn't.)

Adobe Reader is nice enough to ask if you want it to be your default application for .pdf files.

However, it does by default install itself as your new best friend in Safari. Where I was used to clicking a PDF link in Safari and getting a window with minimal embedded Preview.app controls, I now got all of Adobe's madness, with non-standard download and print controls.

There was no place in Safari or Adobe Reader preferences to turn this off. To make it go away I used the following

$ sudo rm /Library/Internet\ Plug-Ins/Adobe*

This removed the files AdobePDFViewer.plugin and AdobePDFViewerNPAPI.plugin, which seems to have me back to normal.

Dixie Banjo Songs

My grandfather's 1970's banjo recordings are now available for listening and download here.

My grandfather, Don Steeves, was a banjo player in Cincinnati. I never saw it, but I heard he played in bands on riverboats in the Ohio River. I did see him play and sing for living room audiences on many family holidays.

In the 1970's, Grandpa made home recordings to share with his sons and their children. I remember playing these cassette tapes at home on our silver and black plastic mono cassette player.

A few years ago my cousin Craig did the hard work of converting all those old tapes to CDs. I ripped the CDs and found or made up names for all the tracks and "albums."

For several years I've had the thought that these ought to be released for anyone to use. This year I finally figured out a hosting strategy using a free Amazon S3 account.

I hope you enjoy Grandpa's banjo playing, singing, and his sense of humor. Please let me know if you do anything special with this music.

Don Steeves

Don Steeves