Mutable Ideas

Notes and ideas about Java, Scala, Big Data, NoSQL, Quality and Software Deploy

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:

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

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

1
2
$ go get -u github.com/dinedal/textql
$ ln -s /usr/local/Cellar/go/1.3/libexec/bin/textql /usr/local/bin

3- Use the console to understand the data:

1
$ textql -source=~/sample.csv -console
1
2
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

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

Thanks Paul Bergeron for this tool!