Simple querying of CSV with sqlite3

Macro Library
#1

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)

Keyboard Maestro Export
Keyboard Maestro Export1264×1594 298 KB

Screenshot 2022-06-21 at 17.38.34
Screenshot 2022-06-21 at 17.38.34744×548 67.3 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
Regex101 can get it, but not in Keyboard Maestro
#2

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)?

#3

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

#4

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

(otherwise temporary)

#5

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