Quick answer
- In 1bench
- Right-click the table in the sidebar, choose Export Table, pick CSV, JSON, NDJSON or SQL, and save the file.
- In SQL
- sql
COPY sales TO 'sales.csv';Change the extension to .parquet or .json and DuckDB picks the format. Excel needs the excel extension loaded first.
Export visually in 1bench
1bench exports a DuckDB table to CSV, JSON, NDJSON or SQL from a right-click menu. You can export the whole table or just the rows you have filtered.
Prerequisite: download 1bench. Free 7-day trial, no sign-up.
- 1
Open your DuckDB file
Connect to your .duckdb file in 1bench and expand it in the sidebar.
- 2
Choose Export Table
Right-click the table and choose Export Table. If the table is already open, the Export button at the bottom of the window does the same.
- 3
Narrow it down (optional)
To export only some rows, open the table, filter it in the Query panel or sort by clicking a column header, then use the Export button at the bottom of the window (the right-click menu always exports the whole table). The dialog then offers Current view, with only those rows and the visible columns, or Whole table.
- 4
Pick a format
Choose CSV, JSON, NDJSON or SQL. For SQL, tick Include structure to get the CREATE TABLE statement, Include data to get the rows, or both.
- 5
Export and save
Click Export and choose where to save the file. A progress bar tracks the rows written, and Cancel stops a long export.
- Parquet and Excel are not in the export menu. Run the COPY statements below from the Query panel's SQL editor instead.
- BLOB columns are written as hex starting with \x in CSV and JSON, and as raw bytes in SQL so the file loads back unchanged.
- A view exports its definition in SQL mode but no INSERT statements, since inserts into a view would fail on restore.
Export with SQL
writes a table, or the result of any query, to a file. DuckDB picks the format from the file extension, so , and need no options, and a query in parentheses works anywhere a table name does.
COPY (SELECT * FROM sales WHERE channel = 'web') TO 'web_sales.csv';
Relative paths are resolved from the folder DuckDB was started in, so use a full path when you are not sure where that is. Every statement below works the same on DuckDB 1.4, 1.5 and 2.0.
Export to CSV
DuckDB writes a header row by default. These options go in parentheses after the file name:
| Option | What it does | Example |
|---|---|---|
| Write the column names first (default true) | ||
| Column separator | for TSV | |
| Quote character | ||
| Quote every value, or listed columns | ||
| Text written for NULL (default empty) | ||
| Format for DATE columns | ||
| Format for TIMESTAMP columns | ||
| gzip or zstd; a name implies gzip |
Example: export one channel's sales, newest first, as a tab-separated file without a header.
COPY (
SELECT id, customer_id, gross_cents, occurred_at
FROM sales
WHERE channel = 'web'
ORDER BY occurred_at DESC
) TO 'web_sales.tsv' (DELIMITER '\t', HEADER false);
Export to Parquet
Parquet keeps column types and compresses well, so it is the best choice when the file goes to another analytics tool or back into DuckDB. Snappy is the default compression; zstd gives smaller files.
COPY sales TO 'sales.parquet' (FORMAT parquet, COMPRESSION zstd);
writes one folder per value, in the Hive layout () that DuckDB, Spark and Athena all read. Running it again into the same folder fails unless you add (replace the files) or (add new files beside them).
Example: split sales by channel, then read the folder back as one table.
COPY sales TO 'sales_by_channel' (FORMAT parquet, PARTITION_BY (channel), OVERWRITE);
SELECT channel, count(*)
FROM read_parquet('sales_by_channel/*/*.parquet', hive_partitioning = true)
GROUP BY ALL;
Export to Excel
Excel files need the excel extension, and COPY does not load it for you. Without it you get "Copy Function with name xlsx is not in the catalog, but it exists in the excel extension".
INSTALL excel;
LOAD excel;
COPY (FROM sales_by_country) TO 'by_country.xlsx' (FORMAT xlsx, HEADER true, SHEET 'countries');
Excel stores every number as a floating-point value, so integer columns come back as DOUBLE when you read the file with . Use Parquet or CSV when the types matter.
Export to JSON
A file holds one JSON object per line (newline-delimited JSON), which streams well and loads back with . Add for a single JSON array, the shape most web APIs and expect.
COPY sales TO 'sales.json';
COPY sales TO 'sales_array.json' (FORMAT json, ARRAY true);
Export a whole database
writes every table to a folder, plus (tables, views, constraints) and . rebuilds it in another file, which also moves a database cleanly between DuckDB versions.
EXPORT DATABASE 'shop_export' (FORMAT parquet);
Example: restore the export into a new database file from the shell.
duckdb new_shop.duckdb -c "IMPORT DATABASE 'shop_export'"
Export to S3
The httpfs extension lets COPY write straight to an S3 bucket, including partitioned folders. Store the credentials once as a secret.
INSTALL httpfs;
LOAD httpfs;
CREATE SECRET (TYPE s3, KEY_ID 'AKIA...', SECRET '...', REGION 'us-east-1');
COPY sales TO 's3://my-bucket/exports/sales.parquet' (FORMAT parquet);
For an S3-compatible store such as MinIO or Cloudflare R2, add and to the secret.
Export to PostgreSQL or SQLite
opens another database inside DuckDB, so exporting is a . Use for a table that already exists.
ATTACH 'host=localhost port=5432 dbname=shop user=app' AS pg (TYPE postgres);
CREATE TABLE pg.public.sales AS FROM sales;
ATTACH 'shop.sqlite' AS lite (TYPE sqlite);
CREATE TABLE lite.sales AS FROM sales;
copies column types but not primary keys or NOT NULL constraints, so add them on the target afterwards. SQLite has no timestamp type, and TIMESTAMP columns arrive there as text.
From the CLI and Python
In the DuckDB shell, and send any query's output to a file. From your own shell, the and flags do the same in one line.
duckdb shop.duckdb -csv -c "SELECT * FROM products" > products.csv
Example: write one query to a CSV inside an interactive session.
.mode csv
.output top_customers.csv
SELECT name, country, lifetime_value_cents FROM customers ORDER BY lifetime_value_cents DESC LIMIT 5;
.output
In Python, a query result has its own write methods, and a read-only connection is enough because only the output file is written.
import duckdb
con = duckdb.connect("shop.duckdb", read_only=True)
ios = con.sql("SELECT * FROM sales WHERE channel = 'ios'")
ios.write_csv("ios.csv")
ios.write_parquet("ios.parquet")