One-liner for running queries against CSV files with SQLite#

I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility:

1
sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
2
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

This uses the special :memory: filename to open an in-memory database. Then it uses two -cmd options to turn on CSV mode and import the taxi.csv file into a table called taxi. Then it runs the SQL query.

Instead of setting the mode with .mode you can use .import -csv like this (thanks, Mark Lawrence):

1
sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \
2
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

You can get taxi.csv by downloading the compressed file from here and running:

1
7z e -aos taxi.csv.7z

I figured this out while commenting on this issue.

The output looks like this:

1
"",128020,32.2371511482553
2
0,42228,17.0214016766151
3
1,1533197,17.6418833067999
4
2,286461,18.0975870711456
5
3,72852,17.9153958710923
6
4,25510,18.452774990196
7
5,50291,17.2709248175672
8
6,32623,17.6002964166367
9
7,2,87.17
10
8,2,95.705
11
9,1,113.6

Add -cmd '.mode column' to output in columns instead:

1
$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \
2
    'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
3
passenger_count  COUNT(*)  AVG(total_amount)
4
---------------  --------  -----------------
5
                 128020    32.2371511482553
6
0                42228     17.0214016766151
7
1                1533197   17.6418833067999
8
2                286461    18.0975870711456
9
3                72852     17.9153958710923
10
4                25510     18.452774990196
11
5                50291     17.2709248175672
12
6                32623     17.6002964166367
13
7                2         87.17
14
8                2         95.705
15
9                1         113.6

Or use -cmd '.mode markdown' to get a Markdown table:

passenger_count	COUNT(*)	AVG(total_amount)
	128020	32.2371511482553
0	42228	17.0214016766151
1	1533197	17.6418833067999
2	286461	18.0975870711456
3	72852	17.9153958710923
4	25510	18.452774990196
5	50291	17.2709248175672
6	32623	17.6002964166367
7	2	87.17
8	2	95.705
9	1	113.6

A full list of output modes can be seen like this:

1
% sqlite3 -cmd '.help mode'
2
.mode MODE ?TABLE?       Set output mode
3
   MODE is one of:
4
     ascii     Columns/rows delimited by 0x1F and 0x1E
5
     box       Tables using unicode box-drawing characters
6
     csv       Comma-separated values
7
     column    Output in columns.  (See .width)
8
     html      HTML <table> code
9
     insert    SQL insert statements for TABLE
10
     json      Results in a JSON array
11
     line      One value per line
12
     list      Values delimited by "|"
13
     markdown  Markdown table format
14
     quote     Escape answers as for SQL
15
     table     ASCII-art table
16
     tabs      Tab-separated values
17
     tcl       TCL list elements

Other options#

There are a whole bunch of other tools that can be used for this kind of thing!

My own sqlite-utils memory command can load data from JSON, CSV or TSV into an in-memory database and run a query against it. It’s a LOT slower than using sqlite3 directly though.

dsq is a tool that does this kind of thing (and a lot more). Author Phil Eaton compiled a collection of benchmarks of other similar tools, and his benchmarking script demonstrates how to use each one of them.