The short version: point DuckDB at the file.
duckdb -c "SELECT region, SUM(amount) FROM 'sales.csv' GROUP BY region"
No schema, no import, no server, no CREATE TABLE. The CSV stays exactly where
it is, and the header row becomes the column names. If you install one thing
today, install that one.
The rest of this page is why that works, what to do when DuckDB isn't available to you, and how to handle the two things that trip everyone up — quoting a filename, and columns whose names have spaces in them.
Why you don't need to load the file anywhere
The instinct to "get the data into a database first" is a holdover from when SQL engines only knew how to read their own storage. Modern ones don't have that limitation. DuckDB, SQLite and several small CLI tools will treat a CSV as a table for the length of one query, infer the types by sampling it, answer, and forget the whole thing.
That changes the calculation completely. A one-off question about a file you were emailed is no longer worth a schema, a load script and a cleanup step. It's worth one line.
Loading into a real database is still the right call in exactly two cases: the data is going to be queried repeatedly by more than one person, or it needs constraints and relationships that a single flat file can't express. Neither describes "what's the total by region in this export".
The options, honestly
| Tool | Install | Best for | Weak at |
|---|---|---|---|
| DuckDB | One binary, no deps | Everything up to tens of GB | Nothing much at this job |
SQLite + .import | Already on macOS and Linux | Persisting the table for later | Types — everything lands as TEXT |
q | pip install q-text-as-data | Quick one-liners in a shell pipeline | Large files; it is slower |
csvsql (csvkit) | pip install csvkit | Generating a CREATE TABLE for a real DB | Speed — it is the slowest here |
Pandas + pandasql | You already have pandas | When the answer feeds more Python | The SQL dialect is SQLite's, not Postgres's |
| Phone app with SQL | App Store / Play | The file arrived in your email, on your phone | Anything you'd want to script |
The honest ranking is short: DuckDB for almost everything, SQLite when you can't install anything, and a phone app when you aren't at a computer. The others are fine and have their moments, but nothing on that list beats DuckDB at this specific job.
DuckDB, properly
Install is a single binary — brew install duckdb, or download it and put it on
your path. Then:
duckdb
D SELECT * FROM 'orders.csv' LIMIT 10;
Things worth knowing straight away:
The filename needs single quotes. FROM orders.csv is parsed as a schema
named orders and a table named csv, and the error message doesn't make that
obvious. FROM 'orders.csv' is right.
Column names with spaces need double quotes. SELECT "Order Date" FROM ….
Single quotes there give you the literal string, silently, for every row — which
looks like the query worked.
Glob a whole directory when the export came in monthly pieces:
SELECT * FROM 'exports/*.csv'
When type inference gets it wrong — usually a column of IDs with leading zeros, or dates in a format it doesn't recognise — take control of the read instead of fighting it:
SELECT * FROM read_csv('orders.csv',
header = true,
columns = {'id': 'VARCHAR', 'ordered_at': 'DATE', 'amount': 'DOUBLE'},
dateformat = '%d/%m/%Y'
)
read_csv also takes delim, quote, escape, ignore_errors = true and
sample_size = -1 (scan the whole file before deciding types, rather than the
first 20,480 rows). That last one is the fix when a column looks numeric for
ten thousand rows and then hits N/A.
Write the answer back out without leaving SQL:
COPY (SELECT * FROM 'orders.csv' WHERE amount > 1000)
TO 'large-orders.csv' (HEADER, DELIMITER ',');
And if the file is going to be queried more than a handful of times, convert it
once — COPY (SELECT * FROM 'orders.csv') TO 'orders.parquet' — and query the
Parquet instead. It is typed, compressed, and usually five to twenty times
faster to scan.
SQLite, when you can't install anything
sqlite3 ships with macOS and most Linux distributions, so this is the option
that works on a locked-down machine.
sqlite3 shop.db
sqlite> .mode csv
sqlite> .import orders.csv orders
sqlite> SELECT region, COUNT(*) FROM orders GROUP BY region;
The catch, and it matters: .import makes every column TEXT. Sorting is
then lexicographic, so 100 sorts before 20, and SUM() gives surprises.
Cast at query time (CAST(amount AS REAL)), or create the table with real types
before importing into it.
Unlike DuckDB, this leaves a shop.db behind — which is the point if you want
the table again tomorrow, and clutter if you didn't.
q, for a shell pipeline
When the query is one step in a chain of shell commands, q reads standard
input like any other Unix tool:
q -H -d, "SELECT region, SUM(amount) FROM - GROUP BY region" < orders.csv
-H means the first row is a header, -d, sets the delimiter, and - is
stdin. It's SQLite underneath, so the dialect is SQLite's. It's also the slowest
sensible option on a big file — fine for a few hundred thousand rows, painful
past that.
When the file is on your phone
None of the above helps when the export is an attachment in your email and you are not at a desk. That is a real situation and the usual answer — "send it to yourself and look at it later" — is a bad one.
Runs actual SQL against the file on iOS and Android, with a visual filter builder for when you'd rather not type a query on a phone keyboard, and a plain-language assistant for when you don't know the column names yet.
What it doesThe dialect is SQLite's, so the queries in this article port across with the
usual caveats — no window functions from the DuckDB examples, and the same
CAST habits apply.
Queries worth having ready
What is actually in this file:
SELECT * FROM 'file.csv' LIMIT 20;
DESCRIBE SELECT * FROM 'file.csv';
SELECT COUNT(*) FROM 'file.csv';
Where the nulls are, one column at a time being tedious, so:
SELECT COUNT(*) - COUNT(email) AS missing_email,
COUNT(*) - COUNT(phone) AS missing_phone
FROM 'contacts.csv';
Duplicates on a key:
SELECT id, COUNT(*) AS n
FROM 'orders.csv'
GROUP BY id HAVING n > 1
ORDER BY n DESC;
Joining two exports — the query that most often justifies the whole exercise, because doing it with VLOOKUP across two sheets is genuinely worse:
SELECT o.id, o.amount, c.name
FROM 'orders.csv' o
JOIN 'customers.csv' c ON c.id = o.customer_id;
A monthly total from a date column:
SELECT date_trunc('month', ordered_at) AS month, SUM(amount)
FROM 'orders.csv'
GROUP BY month ORDER BY month;
The failure modes
Everything came back as one column. The delimiter isn't a comma —
semicolons are standard in much of Europe. read_csv('f.csv', delim = ';').
If the file is stranger than that, it may be
genuinely malformed rather than merely unusual.
A numeric column won't sum. It was inferred as text, because something
non-numeric appears in it. Find the culprit with
SELECT amount FROM 'f.csv' WHERE TRY_CAST(amount AS DOUBLE) IS NULL LIMIT 10;
— it's usually a currency symbol, a thousands separator, or the string N/A.
Leading zeros vanished. The column was read as a number. Declare it
VARCHAR in columns, and don't let a spreadsheet anywhere near it afterwards.
The header row is repeated throughout the file. Several exports were
concatenated. WHERE id <> 'id' is the crude fix and usually the correct one.
It ran out of memory. DuckDB streams, so this normally means an ORDER BY or
a DISTINCT over the whole file. Give it a temp directory
(SET temp_directory = '/tmp/duckdb') and it will spill to disk instead — or
see how to open a 1GB CSV file for the wider
question of what handles what at scale.
