Csv@2

Csv@2 loads CSV files into an in-memory DuckDB database and runs full SQL over them. Needs the Microsoft Visual C++ Redistributable on Windows.

Connects to #

CSV files. CAT loads each file into a table of an in-memory DuckDB database and the queries run against those tables. A test run loads only the data sources its tests use, once, and the data stays in memory until the project is closed — see When the files are loaded.

Example #

• Properties
• YAML

Data sources:
- Name: FinancialData
  Provider: Csv@2
  Technology: Csv
  Connection string: DataToTest\Invoices.csv

Each file becomes a table named "<data source name>"."<file name without extension>" — here "FinancialData"."Invoices".

Connection string #

A path, or a list of paths, to CSV files — any of:

• an absolute path to a CSV file;
• a relative path to a CSV file — resolved against the directory of the .cat.yaml project file;
• an absolute or relative path with the wildcard * — allowed only in the file name, no recursion into subdirectories;
• several of the above, separated by commas.

• Properties
• YAML

Data sources:
- Name: My CSV data
  Provider: Csv@2
  Technology: Csv
  Connection string: >
     DataToTest\*.csv,
     D:\Export\Financial\Invoices-2024.csv

Every file that matches is loaded into a separate table; the files are not combined into one. If DataToTest holds Summary-2023.csv and Summary-2024.csv, a query can combine them itself:

SELECT * FROM "My CSV data"."Summary-2023"
UNION ALL
SELECT * FROM "My CSV data"."Summary-2024"

Settings #

• Properties
• YAML

Data sources:
- Name: FinancialData
  Provider: Csv@2
  Technology: Csv
  Connection string: DataToTest\Invoices.csv
  Settings: delim = '|', header = true, compression = 'gzip'

Query #

One SQL statement in DuckDB’s dialect against the loaded tables — table names are "<data source name>"."<file name without extension>", quoted when they contain spaces or a hyphen. Because the data sits in a database, the whole language is available: joins, grouping, CTEs, window functions, functions for dates, strings and numbers. What that looks like in practice — SELECT, joins and set operations, expressions, functions — is in Query CSV and Excel data with DuckDB SQL; the full reference is the DuckDB SQL documentation.

When the files are loaded #

A test run loads only the data sources its tests use: the data source each test names, plus every Excel@2 and Csv@2 data source whose name appears in its queries. Each is loaded once per run, however many threads the run uses — the threads all read the same copy. The tables exist for the data sources a run or a query uses, not for every data source of the project at once.

Within one session — CAT Studio with the project open, one PowerShell or Python session, one catcli process — a data source whose files have not changed since the previous run is not loaded again. CAT looks for the files matching the connection string anew on every run and compares their sizes and last-write times and the data source’s settings; save a file, add one that matches a wildcard (or edit Settings) and the next run reads the data source anew. Closing and reopening the project always loads everything afresh.

Two consequences to keep in mind:

• A query that lists tables it does not name — for example from information_schema — sees what the session has loaded so far: the data sources of this run and of the earlier runs since the project was opened, not every data source of the project. A source an earlier run loaded is checked for changes again only when a run needs it.
• A file replaced by a copy with exactly the same size and last-write time is not read again within the session; reopen the project to force it.

Prerequisites #

DuckDB ships with CAT; Windows and Linux alike. Two things must be in place on the machine:

Microsoft Visual C++ Redistributable (Windows) #

A very common prerequisite used by much other software, so it is probably already installed. To verify or install it, go to Latest Microsoft Visual C++ Redistributable Version and download and install the “X64” version:

If the installer shows a dialog with “Repair”, “Uninstall” and “Close” buttons, just close it — the prerequisite is already there. Otherwise install it (next, next, next…).

Internet connection #

DuckDB loads its extensions from http://extensions.duckdb.org. The URL has to be reachable — connect to the Internet and white-list it if needed. If you need to test CSV data offline, file a request using the “Get Help” button on the right of this site.

Limitations #

• Project open — and every reload — pays the file load for each Csv@2 list, see below.
• Needs access to extensions.duckdb.org at start — see Prerequisites.

In a list #

Csv@2 serves lists of tests, data sources and queries: a CSV file whose header row carries the property names, one definition per row.

Get list of data sources from:
- Provider: Csv@2
  Connection string: DataSources.csv
  Query: SELECT * FROM DataSources

Before the list is read, CAT loads the file into a throwaway in-memory database, reads the list and discards the database. The list is loaded as a data source named main, so the table is named after the file without the extension and needs no schema prefix. It takes the same settings as a data source (Connection string, Settings; an empty connection string means every *.csv in the working directory, as on a data source), and its query is DuckDB SQL, not the SELECT … FROM [file.csv] of Csv@1. A file used both as a list and as a data source is read twice.

Related #

• Technologies — CSV file and the provider behind it.
• Csv@1 — the simple CSV reader.
• Excel@2 — the same DuckDB engine over workbooks.
• Query CSV and Excel data with DuckDB SQL — the SQL you can write against the loaded tables, by example.