A spreadsheet gets slow and awkward once a CSV has many thousands of rows and you want totals, filters or joins. SQL is made for those questions, and you do not need to install and configure a database server to use it. Here are three simple ways, using an example file called orders.csv with the columns order_id, customer, category, amount and order_date.
Option 1: SQLite command line
SQLite can import a CSV into a temporary database in a few commands.
sqlite3 :memory:
.mode csv
.import orders.csv orders
.headers on
SELECT category, SUM(amount) AS total
FROM orders
GROUP BY category
ORDER BY total DESC;
Note that every imported column starts as text, so cast numbers when you calculate, for example SUM(CAST(amount AS REAL)).
Option 2: DuckDB
DuckDB can query a CSV file directly, without an import step.
SELECT customer, COUNT(*) AS orders
FROM 'orders.csv'
GROUP BY customer
HAVING COUNT(*) > 3
ORDER BY orders DESC;
DuckDB detects column types automatically, which saves the casting step.
Option 3: In the browser
If you only have a file and want a quick answer, a browser tool that runs SQL on a CSV is the fastest route, because there is nothing to install.
Three queries worth knowing
-- Monthly totals (SQLite)
SELECT substr(order_date, 1, 7) AS month, SUM(amount) AS total
FROM orders
GROUP BY month
ORDER BY month;
-- Duplicate order ids
SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Top 5 customers by spend
SELECT customer, SUM(amount) AS spend
FROM orders
GROUP BY customer
ORDER BY spend DESC
LIMIT 5;
Before you start
- Check the delimiter and the encoding of the file first.
- Make sure the header row has clean column names without spaces.
- Keep dates in one format, such as YYYY-MM-DD, so that sorting works.
Disclosure: I work on SnipFerry, a set of free browser-based JSON, CSV and SQL tools (https://snipferry.com). The commands above work with SQLite and DuckDB on your own machine.
Top comments (0)