Examples

The following examples use the flights.csv file.

-- read a CSV file from disk, auto-infer options
SELECT * FROM 'flights.csv';
-- read_csv with custom options
SELECT * FROM read_csv('flights.csv',
    delim = '|',
    header = true,
    columns = {
        'FlightDate': 'DATE',
        'UniqueCarrier': 'VARCHAR',
        'OriginCityName': 'VARCHAR',
        'DestCityName': 'VARCHAR'
    });

# read a CSV from stdin, auto-infer options
cat flights.csv | duckdb -c "SELECT * FROM read_csv('/dev/stdin')"

-- read a CSV file into a table
CREATE TABLE ontime (
    FlightDate DATE,
    UniqueCarrier VARCHAR,
    OriginCityName VARCHAR,
    DestCityName VARCHAR
);
COPY ontime FROM 'flights.csv';

-- alternatively, create a table without specifying the schema manually
CREATE TABLE ontime AS SELECT * FROM 'flights.csv';
-- we can use the FROM-first syntax to omit 'SELECT *'
CREATE TABLE ontime AS FROM 'flights.csv';

-- write the result of a query to a CSV file
COPY (SELECT * FROM ontime) TO 'flights.csv' WITH (HEADER true, DELIMITER '|');
-- if we serialize the entire table, we can simply refer to it with its name
COPY ontime TO 'flights.csv' WITH (HEADER true, DELIMITER '|');

CSV Loading

CSV loading, i.e., importing CSV files to the database, is a very common, and yet surprisingly tricky, task. While CSVs seem simple on the surface, there are a lot of inconsistencies found within CSV files that can make loading them a challenge. CSV files come in many different varieties, are often corrupt, and do not have a schema. The CSV reader needs to cope with all of these different situations.

The DuckDB CSV reader can automatically infer which configuration flags to use by analyzing the CSV file using the CSV sniffer. This will work correctly in most situations, and should be the first option attempted. In rare situations where the CSV reader cannot figure out the correct configuration it is possible to manually configure the CSV reader to correctly parse the CSV file. See the auto detection page for more information.

Parameters

Below are parameters that can be passed to the CSV reader. These parameters are accepted by both the COPY statement and the read_csv function.

auto_type_candidates Details

Usage example:

SELECT * FROM read_csv('csv_file.csv', auto_type_candidates=['BIGINT', 'DATE']);

The default value for the auto_type_candidates option is ['SQLNULL', 'BOOLEAN', 'BIGINT', 'DOUBLE', 'TIME', 'DATE', 'TIMESTAMP', 'VARCHAR'].

CSV Functions

Deprecated DuckDB v0.10.0 introduced breaking changes to the read_csv function. Namely, The read_csv function now attempts auto-detecting the CSV parameters, making its behavior identical to the old read_csv_auto function. If you would like to use read_csv with its old behavior, turn off the auto-detection manually by using read_csv(..., auto_detect = false).

The read_csv automatically attempts to figure out the correct configuration of the CSV reader using the CSV sniffer. It also automatically deduces types of columns. If the CSV file has a header, it will use the names found in that header to name the columns. Otherwise, the columns will be named column0, column1, column2, .... An example with the flights.csv file:

SELECT * FROM read_csv('flights.csv');

The path can either be a relative path (relative to the current working directory) or an absolute path.

We can use read_csv to create a persistent table as well:

CREATE TABLE ontime AS SELECT * FROM read_csv('flights.csv');
DESCRIBE ontime;

SELECT * FROM read_csv('flights.csv', sample_size = 20000);

If we set delim/sep, quote, escape, or header explicitly, we can bypass the automatic detection of this particular parameter:

SELECT * FROM read_csv('flights.csv', header = true);

Multiple files can be read at once by providing a glob or a list of files. Refer to the multiple files section for more information.

Writing Using the COPY Statement

The COPY statement can be used to load data from a CSV file into a table. This statement has the same syntax as the one used in PostgreSQL. To load the data using the COPY statement, we must first create a table with the correct schema (which matches the order of the columns in the CSV file and uses types that fit the values in the CSV file). COPY detects the CSV’s configuration options automatically.

CREATE TABLE ontime (
    flightdate DATE,
    uniquecarrier VARCHAR,
    origincityname VARCHAR,
    destcityname VARCHAR
);
COPY ontime FROM 'flights.csv';
SELECT * FROM ontime;

If we want to manually specify the CSV format, we can do so using the configuration options of COPY.

CREATE TABLE ontime (flightdate DATE, uniquecarrier VARCHAR, origincityname VARCHAR, destcityname VARCHAR);
COPY ontime FROM 'flights.csv' (DELIMITER '|', HEADER);
SELECT * FROM ontime;

Reading Faulty CSV Files

DuckDB supports reading erroneous CSV files. For details, see the Reading Faulty CSV Files page.

Limitations

The CSV reader only supports input files using UTF-8 character encoding. For CSV files using different encodings, use e.g. the iconv command-line tool to convert them to UTF-8.