DuckDB can read multiple CSV files at the same time using either the glob syntax, or by providing a list of files to read.

-- read all files with a name ending in ".csv" in the folder "dir"
SELECT * FROM 'dir/*.csv';
-- read all files with a name ending in ".csv", two directories deep
SELECT * FROM '*/*/*.csv';
-- read the CSV files 'flights1.csv' and 'flights2.csv'
SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv'])

Union By Position

By default, DuckDB unifies the columns of these different files by position. For example, consider the following two files:

flights1.csv

FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-01|AA|New York, NY|Los Angeles, CA
1988-01-02|AA|New York, NY|Los Angeles, CA

flights2.csv

FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-03|AA|New York, NY|Los Angeles, CA

Reading the two files at the same time will produce the following result set:

This works correctly, as long as all CSV files have the same schema. If the schema of the files differs, however, this no longer works. This might occur if columns have been added in later files, for example.

Union By Name

If you are processing multiple files that have different schemas, perhaps because columns have been added or renamed, it might be desirable to unify the columns of different files by name instead. This can be done by providing the union_by_name option. For example, consider the following two files, where flights2.csv has an extra column (UniqueCarrier):

flights1.csv

FlightDate|OriginCityName|DestCityName
1988-01-01|New York, NY|Los Angeles, CA
1988-01-02|New York, NY|Los Angeles, CA

flights2.csv

FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-03|AA|New York, NY|Los Angeles, CA

Reading these when unifying column names by position results in an error - as the two files have a different number of columns. When specifying the union_by_name option, the columns are correctly unified, and any missing values are set to NULL.

SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv'], union_by_name=True)

Filename

The filename argument can be used to add an extra filename column to the result that indicates which row came from which file. For example:

SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv'], union_by_name=True, filename=True)