DuckDB Import: Load CSV, Parquet, Excel and DataFrames into Tables

DuckDB has three ways to load a file into a table. CREATE TABLE ... AS makes a new table from a reader function such as read_csv, COPY ... FROM appends a file to a table that already exists, and INSERT INTO ... SELECT appends the result of any query, so you can filter, rename or convert columns on the way in.

CREATE TABLE new_customers AS FROM read_csv('new_customers.csv');

The examples use the 1bench demo shop (customers, products, sales). They load data into tables; to run SQL on a file without loading it, see query CSV and Parquet files in place. Every statement below works the same on DuckDB 1.4, 1.5 and 2.0 unless a section says otherwise.

Import a CSV into a new table

read_csv sniffs the delimiter, quote character, header row and column types, so most files need no options. FROM is DuckDB shorthand for SELECT * FROM, and a bare file name works as a table too.

CREATE TABLE new_customers AS SELECT * FROM 'new_customers.csv';
DESCRIBE new_customers;

Running it a second time fails with "Catalog Error: Table with name "new_customers" already exists!". Use CREATE OR REPLACE TABLE to rebuild it, or CREATE TABLE IF NOT EXISTS to keep the first load. A table made this way has no primary key or NOT NULL constraints; to keep those, create the table first and use the next section.

Example: set every column type yourself instead of trusting the guess.

CREATE TABLE new_customers AS
FROM read_csv('new_customers.csv', columns = {
  'id': 'BIGINT', 'name': 'VARCHAR', 'email': 'VARCHAR', 'country': 'VARCHAR',
  'plan': 'VARCHAR', 'signup_date': 'DATE', 'lifetime_value_cents': 'DECIMAL(12,2)'
});

Import a CSV into an existing table

COPY ... FROM appends a file to a table and checks the table's constraints as it goes. The file's columns must line up with the table's columns by position.

COPY customers FROM 'new_customers.csv';

When the file has fewer columns than the table, the sniffer gives up with "Error when sniffing file" and the hint "It does not match the number of columns found by the sniffer: 5". List the columns the file does have, and the rest get their default. That only works when the missing columns have one: leaving out the id primary key fails with "Constraint Error: NOT NULL constraint failed: products.id". For anything more, switch to INSERT INTO ... SELECT, which lets you shape the rows first.

COPY products (id, sku, name, category, price_cents) FROM 'products_no_active.csv';

INSERT INTO customers
SELECT * FROM read_csv('new_customers.csv')
WHERE plan IS NOT NULL;

INSERT INTO customers FROM 'new_customers.csv' is the short form of the second statement. Each one runs as a single statement, so one row that breaks a constraint, such as a duplicate id, rolls back the whole file.

Match columns by name with INSERT BY NAME

A plain INSERT INTO ... SELECT matches columns by position and fails with "Binder Error: table "products" has 6 columns but 5 values were supplied" when the counts differ. BY NAME matches on column names instead, in any order, and fills the columns you leave out with their default or NULL.

Example: load products from a semicolon file that has no id or active column, numbering the new rows from 101.

INSERT INTO products BY NAME
SELECT 100 + row_number() OVER () AS id, *
FROM read_csv('new_products.csv', delim = ';', nullstr = 'N/A');

The missing active column takes its default of true, and N/A in the file becomes NULL. A name in the file that the table does not have fails with "Binder Error: Table "products" does not have a column with name "title"", so rename it in the SELECT.

CSV options

Options go inside read_csv(...) as name = value, or inside the parentheses after COPY ... FROM with a space instead of the =.

Example: load a point-of-sale export that starts with two comment lines, has no header and writes dates day first.

INSERT INTO sales
SELECT * FROM read_csv('pos_sales.csv',
  skip = 2, header = false, timestampformat = '%d/%m/%Y %H:%M');

A value that does not fit a type you set stops the load with "Conversion Error: CSV Error on Line: 4" and prints the line. Add ignore_errors = true to drop such rows instead. It only skips values that fail inside the reader, so set the type with types first: a column the sniffer read as VARCHAR passes abc through, and the insert then fails with "Could not convert string 'abc' to INT32".

Example: load a supplier file and drop the row whose price is not a number.

INSERT INTO products BY NAME
SELECT 200 + row_number() OVER () AS id, *
FROM read_csv('supplier_products.csv', delim = ';', nullstr = 'N/A',
  types = {'price_cents': 'INTEGER'}, ignore_errors = true);

Import multiple CSV files

A glob loads every matching file in one statement. Without union_by_name, DuckDB takes the columns from the first file, so files that gained a column later fail with "Binder Error: table "sales" has 8 columns but 7 values were supplied".

INSERT INTO sales BY NAME
SELECT * FROM read_csv('monthly/*.csv', union_by_name = true);

Add filename = true to keep a column with the source file of each row, which helps when one month's file turns out to be wrong.

CREATE TABLE monthly_raw AS
SELECT * FROM read_csv('monthly/*.csv', union_by_name = true, filename = true);

Import Parquet and JSON

Parquet stores column types, so it loads with no options and no guessing. JSON works the same way, and read_json accepts both a JSON array and newline-delimited JSON (NDJSON or JSONL).

CREATE TABLE archived_customers AS FROM read_parquet('customers.parquet');
CREATE TABLE country_totals AS FROM read_json('by_country.json');

COPY customers FROM 'customers.ndjson' (FORMAT json);
INSERT INTO customers BY NAME SELECT * FROM read_json('customers.ndjson');

JSON records with a missing key load NULL for that column. BY NAME is the safer choice for JSON, since object keys have no fixed order.

Import an Excel file

read_xlsx comes from the excel extension. DuckDB installs and loads it on first use when it can reach the internet; otherwise run INSTALL excel; LOAD excel; first.

CREATE TABLE price_list AS FROM read_xlsx('products.xlsx', sheet = 'items');
CREATE TABLE price_list AS FROM 'products.xlsx';

Excel stores every number as a float, so integer columns arrive as DOUBLE. Cast them in the SELECT, or load into an existing table, which casts for you. range = 'A1:C4' reads part of a sheet, and all_varchar = true reads every cell as text.

INSERT INTO products BY NAME
SELECT id + 500 AS id, sku, name, category, price_cents
FROM read_xlsx('products.xlsx');

Import a pandas or Polars DataFrame

In Python, DuckDB reads a DataFrame by its variable name, so a pandas or Polars frame works like a table in SQL. No copy to CSV is needed.

import duckdb
import pandas as pd

con = duckdb.connect("shop.duckdb")
leads = pd.DataFrame({
    "id": [9201], "name": ["Cara Diaz"],
    "email": ["[email protected]"], "plan": ["pro"],
})

con.sql("CREATE TABLE lead_list AS SELECT * FROM leads")
con.sql("INSERT INTO customers BY NAME SELECT * FROM leads")

Polars frames work the same way. When the frame lives somewhere the name lookup cannot see, such as a dict or another module, register it under a name first.

import polars as pl

frame = pl.DataFrame({"id": [9301], "name": ["Dev Patel"], "email": ["[email protected]"]})
con.register("new_rows", frame)
con.sql("INSERT INTO customers BY NAME FROM new_rows")

A pandas column of mixed values (object dtype) becomes VARCHAR. For rows built in Python rather than a frame, con.executemany("INSERT INTO products VALUES (?, ?, ?, ?, ?, ?)", rows) takes a list of tuples. In the 2.0 Python package, relations no longer have fetchone(), so check results with fetchall().

Import from SQLite, PostgreSQL or MySQL

ATTACH opens another database inside DuckDB, and importing a table is then a CREATE TABLE ... AS. The sqlite, postgres and mysql extensions load on first use.

ATTACH 'app.sqlite' AS app (TYPE sqlite, READ_ONLY);
CREATE TABLE app_users AS FROM app.users;

ATTACH 'host=localhost port=5432 dbname=saas_app user=demo' AS pg (TYPE postgres, READ_ONLY);
CREATE TABLE pg_orders AS FROM pg.public.orders;

ATTACH 'host=localhost port=3306 user=demo database=saas_app' AS my (TYPE mysql, READ_ONLY);
CREATE TABLE my_users AS FROM my.users;

Types carry over from PostgreSQL and MySQL (numeric(10,2) arrives as DECIMAL(10,2), timestamptz as TIMESTAMP WITH TIME ZONE). SQLite dates are text, so cast them in the SELECT. To load only some rows, filter in the SELECT.

Example: copy only delivered orders, then append shipped ones later.

CREATE TABLE pg_recent AS
SELECT id, status, total_amount, created_at FROM pg.orders WHERE status = 'delivered';

INSERT INTO pg_recent
SELECT id, status, total_amount, created_at FROM pg.orders WHERE status = 'shipped';

COPY FROM DATABASE app TO main copies everything at once, but it rebuilds each table with its original defaults and indexes, so it is fragile across engines. From SQLite it fails with "Catalog Error: Scalar Function with name datetime does not exist!" on a DEFAULT (datetime('now')) column, and from a PostgreSQL database with a pgvector index it fails on "unknown index type". Copy table by table instead.

Import from another DuckDB file

Attach the second file and copy with CREATE TABLE ... AS, or copy the whole file with COPY FROM DATABASE.

ATTACH 'shop.duckdb' AS shop (READ_ONLY);
ATTACH 'archive.duckdb' AS archive;
CREATE TABLE archive.web_sales AS FROM shop.sales WHERE channel = 'web';

COPY FROM DATABASE shop TO archive;

On DuckDB 1.4 and 1.5, COPY FROM DATABASE fails on tables with foreign keys with "Constraint Error: Violates foreign key constraint because key "id: 2" does not exist in the referenced table". DuckDB 2.0 copies them in the right order. On the older versions, copy the schema first and fill the parent tables before the child.

COPY FROM DATABASE shop TO archive (SCHEMA);
INSERT INTO archive.customers FROM shop.customers;
INSERT INTO archive.products FROM shop.products;
INSERT INTO archive.sales FROM shop.sales;

From the CLI

The DuckDB shell's .import loads a file into a table and creates the table when it does not exist. Extra flags are passed to the reader.

.import new_customers.csv new_customers
.import new_products.csv new_products --delim ';'

This changed in 1.5. In 1.4, .import is the old SQLite-style command that uses the shell's own separators, so with default settings it creates a single VARCHAR column named after the whole header line. On 1.4, use CREATE TABLE ... AS FROM read_csv(...) instead.

To pipe data in from another program, read from /dev/stdin.

cat new_customers.csv | duckdb shop.duckdb -c "COPY customers FROM '/dev/stdin' (FORMAT csv, HEADER true)"