Using CSV Mode in DuckDB CLI

Posted on by Ian

DuckDB’s command line interface (CLI) provides a convenient .mode csv command that allows you to output all query results directly in CSV format. This approach differs from the COPY statement as it affects the output format of all subsequent queries until changed.

The article takes a quick look at CSV mode in the DuckDB CLI.

Enabling CSV Mode

To enable CSV mode in the DuckDB CLI:

.mode csv

Once enabled, all query results will be automatically formatted as CSV output.

To change mode to another format (such as table, json, duckbox, etc), run the same command, but replacing csv with the new output.

Example

After enabling CSV mode, any query you run will output results in CSV format:

-- Enable CSV mode
.mode csv

-- Run a simple query
SELECT * FROM employees;

Output example:

id,name,department,salary
1,Blake Winkley,Engineering,75000.00
2,Hector Marshall,Marketing,65000.00
3,Bebe Trinkett,Sales,70000.00

Controlling Headers

You can control the display of headers using the .headers command:

-- Show headers (default)
.headers on

-- Hide headers
.headers off

Example of disabling headers:

-- Hide headers
.headers off

-- Run a simple query
SELECT * FROM employees;

Output:

1,Blake Winkley,Engineering,75000.00
2,Hector Marshall,Marketing,65000.00
3,Bebe Trinkett,Sales,70000.00

This time the header didn’t appear like it did in the previous example (i.e., we didn’t get id,name,department,salary on the first line).

To re-enable headers, simply use the following:

.headers on

Enabling CSV When Launching DuckDB

You can save time by passing the -csv argument when launching DuckDB. This removes the need to use the .mode command to switch to CSV.

Example:

duckdb test.duckdb -csv

That launches DuckDB in the test.duckdb database and enables CSV mode.

Now queries will be returned in CSV mode (until we change it to another mode).

We can also disable headers in the same fashion by using the -noheader argument:

duckdb test.duckdb -csv -noheader

Headers are enabled by default, but we can also use -header to explicitly enable them:

duckdb test.duckdb -csv -header

Output Redirection

It’s possible to do the following:

duckdb test.duckdb -csv -c "SELECT * FROM employees;" > employees.csv

This launches DuckDB runs the query, saves the results in CSV format to the employees.csv file, then closes the DuckDB connection.

The -c argument specifies a command to run.

Troubleshooting

Here are some common issues to watch out for:

Missing Headers

  • Check .headers setting
  • Ensure it’s set before running queries

Formatting Issues

  • Verify no other modes are accidentally set
  • Check for hidden characters in data

Output Redirection Problems

  • Verify file permissions
  • Check disk space
  • Ensure proper quoting in shell scripts

Summary

Using CSV mode in DuckDB CLI provides a straightforward way to output query results in CSV format. While it offers fewer customization options compared to the COPY statement, it’s perfect for quick exports and interactive sessions where you need CSV output.