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
9 Likes
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)

1 Like
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

3 Likes
6

Thank you - I found that very helpful for my use case. A beginners question: is it possible to have a KM variable in the WHERE clause of the SELECT statement? Something like SELECT weekdays, prices FROM csvTable WHERE clientid = "$KMVAR_clientID";
I put much effort in trying all sorts of quoting the "$KMVAR_clientID" but without success.

7

Been battling with it for an hour or so too. This worked for me:

/usr/bin/sqlite3 test.sqlite "SELECT locID from Locations WHERE IP='$KMVAR_IP';"

The key seems to be surround the command with double quotes and the values with single quotes. If I do it the other way around I get errors.

1 Like