cq

Wrapper over sqlite inspired by jq for querying CSVs using SQL

Examples of use

$ cat t/foobar.csv
foo,bar
1,2
$ cq t/foobar.csv -q 'select foo, bar + 10 as bar_2 from foobar;'
foo,bar_2
1,12
$ cat t/barbaz.csv
bar,baz
2,3
$ cq t/* -q 'select foo, baz from foobar, barbaz where foobar.bar = barbaz.bar;'
foo,baz
1,3

The tablenames are the basenames of the files without their optional .csv extension. One can also provide explicit names for the tables in 2 ways. One is with prefix assignment:

$ cq f:=t/foobar.csv b:=t/barbaz.csv -q 'select foo, baz from f, b where f.bar = b.bar;'
foo,baz
1,3

And the other one is with suffix assignment:

$ cq t/foobar.csv=:f t/barbaz.csv=:b -q 'select foo, baz from f, b where f.bar = b.bar;'
foo,baz
1,3

Notice one uses := and the other one is flipped =:. The former looks better when combining with <() process substitution:

cq \
  a:=<(ssh server_a generate_csv) \
  b:=<(ssh server_b produce_different_csv)

The latter works better with brace expansion:

cq a/very/long/path/{long_filename=:a,elsewhere/another_file=:b}

Output headers can be disabled with +H, and re-enabled with -H. The last option overrides the formers so you can set a default in a shell alias.

$ cq +H t/foobar.csv -q 'select foo, bar from foobar;'
1,2

Output mode can be set with -o:

$ cq -o column t/foobar.csv -q 'select foo, bar from foobar;'
foo         bar       
----------  ----------
1           2         

Supported output modes can be listed with sqlite3 <<< '.help mode'.

There's a --help option for more.