Load CSV, JSON and text

sqx reads CSV, TSV, JSON arrays, JSON Lines and plain text from stdin, local files or URLs, and turns each into a SQLite table you can query with SQL.

From stdin

Pipe data in and query the table stdin:

cat data/users.csv | sqx "SELECT name, plan FROM stdin WHERE plan != 'free'"

Give the table a clearer name with -t:

cat data/users.csv | sqx -t users "SELECT name, plan FROM users"

From files and URLs

Use -f to load a file without a pipe. It is repeatable, so you can load as many as you need:

sqx -f users=data/users.csv "SELECT * FROM users"
sqx -f data/users.csv "SELECT * FROM users"
sqx -f countries=https://example.com/countries.csv "SELECT * FROM countries"

With name= you choose the table name. Without it, the table is named after the file (users.csv becomes users). A URL is downloaded through the web cache.

Supported formats

sqx looks at the data and picks the format for you. To skip detection, force one with -i.

Format	Looks like	Force with
CSV	`id,name,country`	`-i csv`
TSV	Tab-separated columns	`-i tsv`
JSON array	`[{"id":1}, {"id":2}]`	`-i json`
JSON Lines / NDJSON	One JSON object per line	`-i jsonl`
Plain lines	One value per line, such as a list of URLs	`-i lines`

TSV

cat data/metrics.tsv | sqx "SELECT service, endpoint, latency_ms FROM stdin WHERE latency_ms > 300"

+---------+-----------+------------+
| service | endpoint  | latency_ms |
+---------+-----------+------------+
| auth    | /login    | 340.2      |
| orders  | /checkout | 890.75     |
| orders  | /checkout | 410        |
| search  | /q        | 1203.4     |
+---------+-----------+------------+

metrics.tsv contains an N/A, but latency_ms is still treated as a number, so > 300 compares numbers rather than text.

Plain lines

With -i lines, each line becomes a row with the columns linenum, line and url (an alias of line, handy for URL lists):

cat data/urls.txt | sqx -i lines "SELECT linenum, url FROM stdin"

CSV without a header row

Pass -no-header and the columns are named c1, c2, c3 and so on:

cat data/headerless.csv | sqx -no-header "SELECT c2 AS city, SUM(c3) AS orders FROM stdin GROUP BY c2"

Column name cleanup

Real-world headers are messy. sqx turns them into names you can type in SQL without quoting:

Header in the file	Column in SQL
`First Name`	`first_name`
`E-mail Address`	`e_mail_address`
`2023 Revenue ($)`	`c_2023_revenue`
A second `Amount`	`amount_2`
An empty header in column 6	`c6`

To see what sqx made of a file, ask SQLite for the table layout:

cat data/messy_headers.csv | sqx "PRAGMA table_info(stdin)"

+-----+----------------+---------+---------+------------+----+
| cid | name           | type    | notnull | dflt_value | pk |
+-----+----------------+---------+---------+------------+----+
| 0   | first_name     | TEXT    | 0       |            | 0  |
| 1   | e_mail_address | TEXT    | 0       |            | 0  |
| 2   | c_2023_revenue | REAL    | 0       |            | 0  |
| 3   | amount         | INTEGER | 0       |            | 0  |
| 4   | amount_2       | INTEGER | 0       |            | 0  |
| 5   | c6             | TEXT    | 0       |            | 0  |
+-----+----------------+---------+---------+------------+----+

Type inference

Columns that hold numbers become INTEGER or REAL, so sorting and comparisons work as expected.
Placeholders such as N/A, NULL and - become SQL NULL.
Values with a leading zero, such as ZIP codes like 01234, stay as text and keep their zero.

echo '[{"id":1,"zip":"01234"},{"id":2,"active":true}]' | sqx "SELECT * FROM stdin"

+----+-------+--------+
| id | zip   | active |
+----+-------+--------+
| 1  | 01234 |        |
| 2  |       | 1      |
+----+-------+--------+

Objects with different keys are merged into one table, and missing values are NULL.

Nested JSON

Nested objects and arrays are stored as JSON text, so SQLite's JSON functions work on them:

cat data/events.jsonl | sqx "
  SELECT id, type, amount, json_extract(meta, '$.device') AS device, tags FROM stdin"

+-----+----------+--------+--------+-----------------+
| id  | type     | amount | device | tags            |
+-----+----------+--------+--------+-----------------+
| 101 | login    | 0      | mobile |                 |
| 102 | purchase | 49.99  | mobile |                 |
| 103 | login    | 0      | web    |                 |
| 104 | purchase | 120.5  | web    |                 |
| 105 | refund   | -20    | web    |                 |
| 106 | purchase | 310    | kiosk  |                 |
| 107 | purchase | 15.25  | mobile |                 |
| 108 | login    | 0      |        | ["new","promo"] |
+-----+----------+--------+--------+-----------------+

Use json_each to turn an array into rows:

cat data/events.jsonl | sqx "SELECT e.id, tag.value AS tag FROM stdin e, json_each(e.tags) AS tag"