What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To run SQL on a CSV with DuckDB, install its command-line client or Python package, then select from the file path directly. For example, SELECT * FROM 'data.csv'; reads the CSV as a queryable relation; you do not need to create a table first. Use CREATE TABLE only when you want to keep the data in a database.
Choose a setup route
Use the CLI for interactive SQL in a terminal, or Python if your work already happens in a Python script or notebook. Both routes can query a CSV by path; neither requires a preliminary import.
| Route | Setup | Best fit |
|---|---|---|
| DuckDB CLI | Download and unzip the executable, then run it from its directory. | Entering SQL directly in a terminal. |
| Python | Install the duckdb package with pip or conda. |
Running SQL within Python code or a notebook. |
Install and launch the CLI
- Open the DuckDB installation page and choose an installation method for your operating system. The documented CLI is a single executable for Windows, macOS, and Linux; the page lists an install script, direct download, and Docker as options.
- Download and unzip the executable if using the direct-download route.
- Open a terminal in the directory containing it and run
duckdb; in a POSIX shell, use./duckdbif the current directory is not on your command path.
Running the CLI without a database filename opens a temporary in-memory database. The installation page listed DuckDB 1.5.6 as stable and 1.4.5 as LTS when consulted; check the page for current release labels before installing.
Install the Python package
The Python overview documents Python 3.9 or newer. Install the package in the environment where you intend to run your code:
#1 Best Overall
pip install duckdbconda install python-duckdb -c conda-forge
The Python API overview documents both commands and the minimum Python version. Its version label is 1.5.5, while the installation page lists 1.5.6 as stable; those are page labels, not a claim that the two pages report the same release snapshot.
Query the CSV by path
In the CLI, enter this SQL statement, replacing the filename with your CSV path:
SELECT * FROM 'data.csv';DuckDB also supports the explicit CSV reader form:
SELECT * FROM read_csv('data.csv');Use a path relative to the CLI’s working directory or an absolute path. The filename shorthand and read_csv form are documented in the CSV import guide.
Rank #2
In Python, query the file and display the result like this:
import duckdb
duckdb.sql("SELECT * FROM 'data.csv'").show()You can also obtain a CSV relation with duckdb.read_csv("data.csv"). See Python data ingestion for the reader API and its options.
Check how DuckDB interprets the CSV
DuckDB’s CSV sniffer attempts to detect the delimiter, quote and escape rules, column types, and whether the file has a header. This often lets you query a CSV without specifying its layout, but inference is sample-based: the documented default type-inference sample is 20,480 rows, a DuckDB documentation setting rather than a guarantee about every row in the file.
On regular files, DuckDB can sample at different positions. For non-seekable inputs such as gzip CSV or standard input, samples come from the beginning, so later rows with different values or formats may not be reflected in the inferred types. If the file is inconsistent, inspect the detected settings or supply options explicitly.
Inspect detection or set options
Run sniff_csv('data.csv') to expose the detected configuration and a suggested reader prompt. If detection is wrong, specify the relevant options, such as delim, header, or column types. For a file where checking every row is worth the additional work, the documentation describes full-file sampling with sample_size = -1. See CSV auto detection for option details.
Decide whether to keep a database table
A direct file query reads the CSV as a relation for that query; it does not create a persistent table. If you want the data stored as a table in the database, create it explicitly:
CREATE TABLE my_table AS
SELECT * FROM 'data.csv';DuckDB also documents COPY and INSERT INTO ... SELECT for loading data into an existing table. The data overview covers these loading routes.
Read compressed or remote CSV files
Local gzip CSV
DuckDB documents reading a local gzip-compressed CSV directly by filename. The file-format guidance is in the data overview.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →CSV over HTTP or HTTPS
For an HTTP(S) CSV, install and load the httpfs extension in the DuckDB session, then query the remote URL with the CSV reader or path shorthand:
INSTALL httpfs;
LOAD httpfs;
SELECT * FROM read_csv('https://example.com/data.csv');The URL above shows the syntax shape; replace it with the actual CSV URL. Follow the HTTP CSV import guide for remote-file setup.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
