Column to list

In last night’s post, I mentioned that often need to get a column of values out of a spreadsheet and turn them into a Python list. I’ve been doing that through a combination of copy-and-paste and regex find-and-replace in TextMate. Today I made a bundle with a couple of commands that do all the work in a single step.

The commands were inspired by a comment on that post by Barron Bichon, who said that he pulls in spreadsheet data by saving the spreadsheet as a text file and then reading the values through NumPy’s loadtxt command. For many scripts, that’s the best way to import the data, but since many of my little programs are one-offs, I find that building the information directly into the script often works better than the traditional separation of data and programming.1

What I want for those cases are one-step commands that take the column of values I just copied out of a spreadsheet and paste them into my program as a Python list.

Here’s the first TextMate command, Column to List Bare:

Column to List Bare

The command itself is a pipeline with a Perl one-liner:

pbpaste | perl -ne 'BEGIN{$s="[";} chomp; $s.="$_, "; END{$s=~s/, $/]/; print $s}'

It’s intended to be used with columns of numbers.

The second TextMate command is Column to List Quoted:

Column to List Quoted

Its command is also a pipeline with a slightly more complicated Perl one-liner:

pbpaste | perl -ne 'BEGIN{$s="[";} chomp; s/"/\\"/g; $s.=qq("$_", ); END{$s=~s/, $/]/; print $s}'

Because it quotes all the items (escaping quotation marks within the items as needed), it’s intended to be used with columns of strings.

Both commands are bound to ⌃⌥⌘C, so I don’t have to remember which is which. When I type the key combo, a popup menu appears to let me choose which command to run,

Column to list popup

If I copy this column of numbers from a spreadsheet,

Numbers spreadsheet with selected column

and invoke the Column to List Bare command in TextMate, it inserts this,

[477, 305, 584, 600, 633, 639, 333, 490, 303, 402, 315, 505, 493, 559, 387, 790, 423, 457, 716, 706]

with the items separated by commas and the whole thing enclosed in square brackets.

I suppose I could extend the commands to pipe the result through, say

fmt -w 70

This would break up the list into lines of reasonable length rather having it all in one long line. I’ll see how it works in practice for a while and make the change if it seems warranted.

I started out trying sed to do the conversion because I wanted to be real Unixy, but quickly changed to Perl. Sed blows. I generally don’t mind cryptic syntax, but I just can’t get my head around sed’s. Perl may be slightly more verbose in a one-liner, but at least I have a decent sense ahead of time of what it’s going to do.

If you’re wondering why I didn’t use Python itself for the commands, the answer is simply that Perl is better at one-liners. I saw no point in being a language purist and making my life harder.


  1. If you ever see me using the phrase “business logic” in anything other than derisive terms, you have my permission to shoot me. 


3 Responses to “Column to list”

  1. Carl says:

    I always have an interactive Python window open in one tab of Terminal.app, and I wrote a module to handle copy/paste, so if I had a column of spreadsheet info in the pasteboard I wanted to manipulate, I would just write [int(line) for line in lines()] (or float(line) depending).

    I should really put that module on Github at some point, but I’m too embarrassed by how idiosyncratic a module it is. For example, I recently made it so that c.macronize() will change vowels from using cîrcumflexes to mācrons because it’s easier to type circumflexes on a standard English Mac keyboard (option+i, vowel) than macrons, but Japanese transliterations are supposed to use macrons instead of circumflexes. The whole module is a collection of little hacks like that, which I find useful but I don’t know if anyone else would appreciate.

  2. Kurt K says:

    Have you checked out http://www.shancarter.com/data_converter/index.html ?

  3. Dr. Drang says:

    Kurt, Mr. Data Converter is nice, I suppose, but it adds a step (and a requirement to be online) to the process.