csvq: Run SQL Queries Against CSV Files
csvq treats CSV and TSV files as tables and lets you run standard SQL against them, so you can filter, aggregate, and join data without importing it into a database.
When a test fixture or export is a CSV, csvq is faster than writing awk. The filename (minus extension) is the table name, and it speaks real SQL including GROUP BY and JOIN.
What it does
csvq parses CSV/TSV files as relations and executes SQL over them. It supports SELECT with WHERE/GROUP BY/ORDER BY, JOINs across files, and even UPDATE/DELETE that rewrite the file. Output can be CSV, JSON, or a formatted table.
Common usage
csvq 'SELECT name, age FROM users WHERE age > 30'
csvq -f json 'SELECT COUNT(*) AS n FROM orders'
# join two CSVs by key
csvq 'SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id'Options
| Flag | What it does |
|---|---|
| -f <format> | Output format: CSV, JSON, TSV, or a text table |
| --without-header | Treat the first row as data, not a header |
| -d <char> | Field delimiter of the input (e.g. tab) |
| -N, --no-header | Do not print a header row in the output |
| -s <file> | Run SQL from a file instead of the argument |
In CI
csvq is ideal for asserting on exported data: csvq 'SELECT COUNT(*) FROM report WHERE status = "fail"' gives a number you can gate on. Quote the SQL for your shell, and remember the table name is the file stem, so orders.csv is queried as orders.
Common errors in CI
"syntax error: unexpected token ..." points at a malformed SQL statement or an unquoted identifier. "field ... does not exist" (or "field is ambiguous" in a JOIN) means a wrong or duplicated column name; qualify it with the table alias. "file ... does not exist" means the table name did not resolve to a file in the working directory.