How to Query CSV Files with SQL Using DuckDB

DuckDB lets you run SQL directly on CSV files without importing them into a database. Install the CLI, point it at a file, and query. There is no server to start, no schema to define, and no Python required. On a standard laptop, DuckDB loads a 20 GB CSV in about 10 seconds at approximately 1.96 GB/s, according to benchmarks from its own CSV reader tests. For most CSV files analysts work with day-to-day, queries return in under a second.

Why DuckDB for CSV Analysis

Excel and Google Sheets struggle with files above 100,000 rows. Loading large CSVs into a proper database like Postgres means writing import scripts and managing a running server. DuckDB fills the gap.

It is an in-process OLAP database, meaning it runs inside the DuckDB process itself rather than as a separate service. The CLI binary weighs about 40 MB. You install it once, and it works offline. There is no account, no license, and no cloud dependency.

DuckDB's CSV reader ranked first in the Pollock benchmark, a test designed to measure SQL tools against malformed, non-standard, and pathological CSV files. In practice, DuckDB handles encoding mismatches, inconsistent quoting, and mixed null representations that would crash simpler tools. For analysts who receive data exports from third-party systems, this robustness matters more than benchmark numbers.

For analytical queries on large files, DuckDB consistently runs 100 to 1,000 times faster than SQLite or Postgres on the same data. The reason is columnar execution: DuckDB only reads the columns a query actually touches instead of fetching full rows.

Install DuckDB CLI

Choose the command for your operating system and run it in your terminal. No admin privileges are required on Mac or Linux.

macOS (Homebrew):

brew install duckdb

Linux:

curl https://install.duckdb.org | sh

Windows (winget):

winget install DuckDB.cli

Current version as of April 2026 is 1.5.1. Once installed, launch the interactive shell:

duckdb

The shell prompt shows D. Type exit or press Ctrl+D to quit. You can also open a one-off query without the interactive shell:

duckdb -c "SELECT COUNT(*) FROM 'sales_data.csv'"

Run Your First Query on a CSV File

No import step. Reference the file path directly in a FROM clause:

SELECT * FROM 'sales_data.csv' LIMIT 10;

DuckDB reads the header row automatically and infers column types. If the file is in a different directory, use the full path:

SELECT * FROM '/Users/yourname/downloads/sales_data.csv' LIMIT 10;

To inspect column names and detected types before writing queries:

DESCRIBE SELECT * FROM 'sales_data.csv';

This is useful when you receive an unfamiliar export and want to understand the schema before writing aggregations.

Aggregations and Grouping

The most common analytics pattern: sum or count by category, then sort by the result.

SELECT region,
       SUM(revenue)   AS total_revenue,
       AVG(revenue)   AS avg_deal_size,
       COUNT(*)       AS deal_count
FROM 'sales_data.csv'
GROUP BY region
ORDER BY total_revenue DESC;

Filter to a date range before aggregating:

SELECT rep_name,
       SUM(revenue) AS q1_revenue
FROM 'sales_data.csv'
WHERE close_date >= '2026-01-01'
  AND close_date <  '2026-04-01'
GROUP BY rep_name
ORDER BY q1_revenue DESC
LIMIT 10;

DuckDB accepts standard ISO date strings in WHERE clauses without casting.

Find Duplicates and Nulls

Two queries every analyst runs at the start of a data review:

-- Duplicate check by email
SELECT email, COUNT(*) AS occurrences
FROM 'leads.csv'
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Null check across all key columns
SELECT
  COUNT(*) AS total_rows,
  COUNT(email) AS has_email,
  COUNT(company) AS has_company,
  COUNT(revenue) AS has_revenue
FROM 'leads.csv';

Join Two CSV Files Without Importing Either

DuckDB treats CSV files like database tables. Join them directly:

SELECT o.order_id,
       o.amount,
       c.company_name,
       c.industry
FROM 'orders.csv'    o
JOIN 'customers.csv' c  ON o.customer_id = c.id
WHERE o.amount > 5000
ORDER BY o.amount DESC;

Neither file is imported or modified. DuckDB reads both on the fly during the query. This replaces the common manual workaround of using VLOOKUP to combine two exports in Excel.

Handle Type Conversions

DuckDB's type inference is accurate for well-formed files, but revenue columns stored as text (common in CRM exports) need explicit casting:

SELECT company,
       CAST(REPLACE(revenue_str, ',', '') AS DOUBLE) AS revenue
FROM 'pipeline.csv'
WHERE revenue_str IS NOT NULL;

For dates stored in non-ISO formats:

SELECT strptime(close_date, '%m/%d/%Y') AS close_date_parsed,
       deal_name
FROM 'deals.csv';

strptime converts a string to a date using a format pattern. This handles the MM/DD/YYYY format common in US CRM exports.

Export Results to a New CSV

COPY (
  SELECT region, SUM(revenue) AS total
  FROM 'sales_data.csv'
  GROUP BY region
  ORDER BY total DESC
) TO 'revenue_by_region.csv' (HEADER, DELIMITER ',');

The result file lands in the current directory. Open it in Excel or pass it to another tool.

Query Multiple Files at Once

If you have monthly exports saved with a consistent naming pattern, DuckDB reads them all in one query using a glob:

SELECT month,
       SUM(revenue) AS monthly_revenue
FROM 'sales_2026_*.csv'
GROUP BY month
ORDER BY month;

DuckDB unions the files automatically. This removes the manual step of combining exports before analyzing.

Persist Data for Repeated Analysis

Re-reading a large CSV on every query is fast but adds up for daily work. Load the file into a persistent DuckDB database once:

duckdb my_analysis.duckdb

Inside the shell:

CREATE TABLE sales AS SELECT * FROM 'sales_data.csv';

Subsequent queries against sales skip the CSV read entirely and hit in-memory columnar storage. The .duckdb file is portable: copy it to another machine and query it there with the same CLI.

When SQL Is Too Much Setup

DuckDB is the right tool when you know SQL and need speed on large files without database infrastructure. If you want to skip the terminal entirely and ask questions in plain English about your CSV, VSLZ AI handles that from a file upload with no configuration needed.

Key Takeaways

• Install DuckDB CLI with one command: brew install duckdb, curl https://install.duckdb.org | sh, or winget install DuckDB.cli
• Query any CSV with SELECT * FROM 'file.csv' -- no import required
• Join multiple CSVs with standard SQL JOIN syntax
• DuckDB loads 20 GB CSVs in about 10 seconds at ~1.96 GB/s
• Export results with the COPY ... TO command
• Persist frequently queried data to a .duckdb file to skip repeated CSV reads