How to Use csvsql and q to Run SQL on CSV Files the Safe Way

A CSV file is a table without a database, and the questions people ask of it are SQL questions: group, filter, join, sort. Reaching for a spreadsheet is slow for big files, and awk gets awkward past two conditions. Three tools let you type the SQL directly, with different trade-offs. This post compares them and flags the data-type trap they share. It follows awk sums and averages and leads to DuckDB. Examples use csvkit 2.0, q 3.1 and SQLite 3.46.

csvsql from csvkit

pipx install csvkit
csvsql --query "select customer, sum(amount) as total from orders group by customer order by total desc" orders.csv

customer,total
grace,275.0
ada,150.75

csvkit loads the file into an in-memory SQLite database named after the file and runs the query; the table name is the file name without the extension. Output is CSV with a header, so it chains with other tools: add | csvlook for a readable table. A join names two files: csvsql --query "select o.id, c.name from orders o join customers c on o.cust_id = c.id" orders.csv customers.csv. It is the most careful of the three about types, and the slowest for big files, since it reads the data twice to infer types.

q

q -H -d, -O "select customer, count(*) c from orders.csv group by customer order by c desc"
q -H -d, "select o.id, c.name from orders.csv o join customers.csv c on o.cust_id = c.id"

q ("text as data") treats the file name as the table in the query itself, which reads naturally. -H says the first line is a header, -d, sets the delimiter, -O prints the header in the output. It also reads standard input as -, so it sits in pipelines. Check the project's state before depending on it: releases have been infrequent, and if it is not available in your package manager, the other two options need no extra install.

Plain sqlite3

sqlite3 -csv -header :memory: \
  -cmd '.import orders.csv orders' \
  "select customer, sum(amount) from orders group by customer"

With a recent SQLite, .import reads a CSV into a table created from the header, and every column is text. Cast in the query for arithmetic: sum(cast(amount as real)). It needs nothing beyond sqlite3, which is on almost every system, and it is fast. Add -cmd '.import --skip 1' forms when the table already exists. Use :memory: for a throwaway database or a file name to keep the data.

The type inference trap

printf 'zip,qty\n02134,5\n90210,7\n' > z.csv
csvsql --query "select zip from z" z.csv
csvsql --no-inference --query "select zip from z" z.csv

zip
2134
zip
02134

Inference guesses that a column of digits is a number, and a number has no leading zero. Zip codes, product codes, phone numbers and IDs are text that looks like numbers; losing the zero is silent data corruption. Use --no-inference (everything is text), or -I in some versions, then cast where you need numbers. The sqlite3 import loads everything as text, which avoids the problem. Dates behave similarly: a value like 03/04/05 is ambiguous, so check how it was read.

Quoting and delimiters

CSV fields with commas must be quoted; these tools honor the quotes, unlike cut or plain awk. Tab-separated input needs -t in csvkit and -t in q. Windows line endings can leave a carriage return on the last column; see line ending problems. Check the row count first: csvsql --query "select count(*) from orders" orders.csv against wc -l (minus the header, plus any multi-line quoted fields).

Where this does not apply

These load the whole file into memory (or a temporary database) and are slow beyond a few hundred megabytes; DuckDB reads big CSV and Parquet files in place. If you query the same file repeatedly, load it into a SQLite file once, as in SQLite for log analysis. For updates and a changing schema, use a real database. Joins on messy keys (trailing spaces, different case) need cleaning first.

How these tools run SQL on a text file

Both csvsql from csvkit and q do the same underlying trick: they load the CSV into a temporary SQLite database, run your query against it and print the result. The convenience is that you write SQL against a file name as if it were a table, with no schema setup. The consequences follow from the mechanism. The whole file is read into the database before the query runs, so a very large file takes time and disk space even for a query that needs one column, and these tools suit files up to some hundreds of megabytes. Types come from guessing, which is the next section. Column names come from the header line, and names with spaces or special characters need quoting in the query. Because the engine is SQLite, the SQL dialect is SQLite's, with its date functions, its loose typing and its set of aggregate and window functions, and anything specific to another database will not work.

For repeated queries over the same large file, import it once into a real SQLite file and query that, since the loading is the slow part and it is repeated on every invocation otherwise.

The type guessing trap

Type inference is the most common source of wrong answers. A column that looks numeric becomes a number, so a ZIP code like 02134 loses its leading zero, an identifier like 1E5 becomes a floating-point number in scientific notation, and a long numeric identifier may lose precision once it is stored as a float. A column that mixes numbers and text may be read as text, so sorting puts 10 before 9. A column with blank cells may be read as numeric with nulls, or as text with empty strings, and the two behave differently in comparisons and averages. Dates are usually left as text unless a flag asks for parsing, and then the format must match what the tool expects. The defense is to be explicit. In csvsql, --no-inference keeps everything as text and you cast in the query, and in q the column type options control it. With plain sqlite3, create the table with the types you want and import into it, so nothing is guessed.

Check the result against a count and a sum from another tool, such as wc -l and awk, before you trust a report, since a silent type conversion changes totals without any message.

Delimiters, quoting and dirty input

Real CSV files are messier than the definition. Fields with commas, quotes and newlines inside quotes are legal, and a tool that splits naively on commas, such as cut or a simple awk, mangles them, which is a good reason to use a CSV-aware tool here. Set the delimiter explicitly for tab-separated or semicolon-separated files, which are common in exports from systems with a decimal comma. Windows line endings leave a carriage return at the end of the last column, which corrupts its name and values, and the fix is to strip it first, as the post on line endings describes. A byte order mark at the start of the file becomes part of the first column's name, so a query on that column fails with "no such column" even though it looks right, and removing the three bytes or choosing the right encoding option fixes it. When a query returns nothing or odd values, look at the first line of the file with head -c 200 | od -c before changing the query.

See the csvsql documentation and the q project.