How do you query a CSV with SQL?
A CSV file is a table saved as plain text, with one line for each row and commas between the values. DuckDB can read one by its name, with no need to create a table first.
You write from 'trips.csv' where you would write a table name, and the rest of the query is the same SQL you already know.
By the end of this lesson
Note
I can query a CSV file by its name.
Key words
| Word | What it means |
|---|---|
| CSV | a text file with one row on each line and values split by commas |
| Header | the first line of a file, naming its columns |
| read_csv | the function that reads a CSV file as a table |
| Type | the kind of value a column holds, such as a number or a date |
| Playground | the page where you can upload a file and run a query on it |
The cells students fill in are left blank in this preview.
A file can stand in for a table
Our file trips.csv has a header line, trip_id,start_day,station,minutes,paid, then one line for each bike trip.
Put its name in quotes after from, and DuckDB treats the file as a table. It finds the file you uploaded, and the query runs as usual.
Below is the query. Its result is the same as for the table later in this lesson.
A query that reads a file
-- trips.csv holds a header line and eight lines of data.
-- Uploaded in the playground, the file is read by its name.
select station,
count(*) as trips,
round(avg(minutes), 1) as avg_minutes
from 'trips.csv'
group by station
order by station;
Worked example: what the query gives
| station | trips | avg_minutes |
|---|---|---|
| Canal | 3 | 16.7 |
| Park | 3 | 26.7 |
| Station | 2 | 17.0 |
The cells students fill in are left blank in this preview.
DuckDB guesses the type of each column
A CSV file is only text. DuckDB samples the values and chooses a type for each column. Whole numbers become BIGINT, values such as 2026-05-01 become DATE, and true and false become BOOLEAN.
Use describe to see the types it chose. The table in this lesson is built with those same types, so you can try it here.
Try it: the guessed types
-- The column names and types of the table.
describe trips;
Worked example: types DuckDB chose
| Column | Example value | Type |
|---|---|---|
| trip_id | 1 | BIGINT |
| start_day | 2026-05-01 | DATE |
| station | Canal | VARCHAR |
| minutes | 12 | BIGINT |
| paid | true | BOOLEAN |
The cells students fill in are left blank in this preview.
Try it: the same query on a table
-- The same query, on a table built to match the file.
select station,
count(*) as trips,
round(avg(minutes), 1) as avg_minutes
from trips
group by station
order by station;
-- Add: where paid (a boolean column is a test on its own).
Playground uploads a file, lessons use tables
In the playground, you upload a CSV, TSV or JSON lines file of up to 64 kilobytes. Then from 'name' reads it. Parquet files are not supported.
A lesson block has no uploaded file. So here the file is a table with the same columns and types. The query you write is the same, apart from the name after from.
A common mistake
Warning
Many people think a query can open any file on the internet or on their computer. It cannot. The query reads only a file you uploaded to the playground, by the name it has there.
Going further
Write a query that finds the longest trip at each station, and the day it started. Use row_number() over a partition, as in the last lesson.
If you have a small CSV of your own, upload it to the playground and run describe on it first.