Query CSV text files like SQL tables

A CSV text connection lets you list, view and analyse text files as though they were SQL tables. Create one via File → Create CSV Text connection and the files appear in the Server Navigator, ready to be queried like simple tables.

How it works, and what it cannot do

The connection uses the CsvJdbc driver. The SQL is executed locally by that driver, and its syntax is limited — you cannot use IRIS or Caché SQL here, because there is no IRIS involved. The details worth knowing before you start:

• CsvJdbc accepts all types of CSV file defined by RFC 4180.
• Only SELECT queries against a single table are supported. There is no INSERT, UPDATE, DELETE or CREATE.
• Sub-queries are permitted; joins between tables in a SELECT are not.
• A query must follow this shape:

SELECT [DISTINCT] [table-alias.]column [[AS] alias], ...
 FROM table [[AS] table-alias]
 WHERE [NOT] condition [AND | OR condition] ...
 GROUP BY column ... [HAVING condition ...]
 ORDER BY column [ASC | DESC] ...
 LIMIT n [OFFSET n]

If you need joins, richer SQL or type inference across a large file, use the bundled DuckDB connection instead — see built-in support for DuckDB. The CSV text connection remains the simplest option when you want a folder of files to behave like a set of tables in the navigator.

Reading a ZIP archive

You can point a CSV connection at a ZIP file containing several CSV files. In the example below, the connection is created for IDS_CSV.zip, which contains multiple files, one of them called IDS_DATA.csv. Open a query editor and run SELECT * FROM IDS_Data to view and analyse that file — the archive member name becomes the table name, without the extension.

There is no need to unpack anything first, which makes this a quick way to look inside an archived export or a log bundle.

Reading a folder of files

You can also select a folder for a CSV connection and query every *.csv file in it via SQL. In the example below, the connection is created for a folder called testdata which contains a file population.csv. Running select * from population LIMIT 100 returns the first 100 rows of that file.

Each file in the folder is a table, so adding a file to the folder adds a table to the connection. Nothing is imported and nothing is copied: the files are read where they are.

Combining files with database data

A CSV text connection on its own cannot join. If you need to join a text file against a table in IRIS — the usual case, matching a delivered file against master data — that is what the Query Cloud is for: one statement across several connections, evaluated client-side. See also beyond IRIS for the other engines available in the same window.

The current build is on the download page.