Skip to main content

Mutable Ideas

Working with CSV files on shell

Often we have to work with JSON data sets, now and then data comes on CSV format. I received a great tip from @diegodellera who told me about textql - Execute SQL against structured text like CSV or TSV.

Those are the steps to install on my MacOSX which homebrew:

1- Install go dependency:

$ brew install go
$ export GOPATH=$(brew --prefix go)/libexec

2- Download, compile textql and create a symlink to add to shell PATH:

$ go get -u
$ ln -s /usr/local/Cellar/go/1.3/libexec/bin/textql /usr/local/bin

3- Use the console to understand the data:

$ textql -source=~/sample.csv -console
sqlite> .schema tbl
sqlite> SELECT First_Name, Last_Name, E_mail_Address FROM tbl LIMIT 5;

4- Then you can use this as part of an script

$ textql -header=true -source=~/sample.csv -sql='SELECT First_Name, Last_Name, E_mail_Address FROM tbl'

Thanks Paul Bergeron for this tool!