Simple querying of CSV with sqlite3

From time to time there are questions about extracting data from CSV.

One approach is to apply a standard SQL query (simple structure, easily learned) to the CSV data, using sqlite3 which is a built-in macOS command line tool, and requires no installation.

Simple querying of CSV with sqlite3.kmmacros (2.7 KB)



Expand disclosure triangle to view sqlite3 shell Source
sqlite3 <<"EOF"
.mode csv
.import '| echo "$KMVAR_sampleCSV"' csvTable
SELECT clientid, weekdays, prices FROM csvTable WHERE up IS "True";
EOF
5 Likes

That is... brilliant! Also a very quick way to change .tsv to .csv, read fixed columns into a table, output a basic HTML table...

Silly question -- is csvTable a temporary construct, or does it get written to disk (and over-written next run)?

Thank you @ComplexPoint - that is very useful to know!

Can be – you have to give the dot command explicitly if you want that.

(otherwise temporary)

PS in csv mode, the .import dot command will, by default, construct a schema which uses the TEXT type for all fields.

If your query needs functions over other types (REAL or INTEGER, for example), you can specify the schema explicitly (before the import)

sqlite3 <<"EOF"
.mode csv
CREATE TABLE csvTable(
  "clientid" TEXT,
  "date" TEXT,
  "weekdays" TEXT,
  "gains" REAL,
  "prices" REAL,
  "up" TEXT
);
.import '| echo "$KMVAR_sampleCSV"' csvTable

SELECT AVG(prices) FROM csvTable;
EOF

See: Datatypes In SQLite

1 Like