Examples

The following examples use the flights.csv file.

Read a CSV file from disk, auto-infer options:

SELECT * FROM 'flights.csv';

Use the read_csv function 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 using a CREATE TABLE .. AS SELECT statement:

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, DELIMITER '|');

If we serialize the entire table, we can simply refer to it with its name.

COPY ontime TO 'flights.csv' WITH (HEADER, 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 the read_csv function. But not all parameters are accepted by the COPY statement.

auto_type_candidates Details

The auto_type_candidates option lets you specify the data types that should be considered by the CSV reader for column data type detection. 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

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 = 20_000);

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. For example:

iconv -f ISO-8859-2 -t UTF-8 input.csv > input-utf-8.csv

Order Preservation

The CSV reader respects the preserve_insertion_order configuration option. When true (the default), the order of the rows in the resultset returned by the CSV reader is the same as the order of the corresponding lines read from the file(s). When false, there is no guarantee that the order is preserved.