DEV Community

Azqora Tech
Azqora Tech

Posted on

Query a CSV file with SQL without setting up a database

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)