Get Help

CsvOleDB@1

CsvOleDB@1 reads CSV files in a directory through the Microsoft ACE OLE DB driver with basic SQL. Needs the Microsoft Access Database Engine 2016 Redistributable; Windows only.

Connects to

CSV files in a directory, read through the Microsoft ACE OLE DB driver. Where Csv@1 only iterates a file, this provider lets the driver process the data with basic SQL — ORDER BY, GROUP BY, aggregations — which matters when a file is not sorted the way a set comparison needs. The price is a driver to install.

Example

Name
MyCsvData
Technology
CSV file
Provider
CsvOleDB@1
Connection string
.
Data sources:
- Name: MyCsvData
  Provider: CsvOleDB@1
  Technology: Csv
  Connection string: . # this directory (where the project file is)

If you are lucky, this works on the first attempt. If not, install the driver — see Prerequisites.

Connection string

Not a connection string: a path to a directory where the CSV files are, relative paths resolved against the directory of the project file. The same slot is also accepted as Directory or Folder — see Data source properties. Left out, the working directory is used.

What CAT sends to the driver, with the directory path and the extended properties substituted exactly as you provide them:

Provider=Microsoft.ACE.OLEDB.16.0;Data Source='{ResolvedDirectoryPath}';Extended Properties="{ExtendedProperties}";

Settings

Setting Default Meaning
Extended Properties Text;HDR=YES;FMT=Delimited; The driver’s text-file options — header row present or not, character set, delimiter and the other basic CSV matters. Passed to the driver as they are.

When the driver is installed but encoding, delimiters or other basic CSV things misbehave, tweak the extended properties:

Name
MyCsvData
Technology
CSV file
Provider
CsvOleDB@1
Connection string
.
Extended Properties
Text;HDR=YES;CharacterSet=65001;
Data sources:
- Name: MyCsvData
  Provider: CsvOleDB@1
  Technology: Csv
  Connection string: . # this directory (where the project file is)
  Extended Properties: "Text;HDR=YES;CharacterSet=65001;"

For the meaning of the individual options refer to the Microsoft Access Database Engine 2016 documentation.

Query

A SQL statement as the ACE driver understands it, naming the file in FROM. A reference of the supported SQL features is as hard to find as the one for the extended properties; basic SQL works — grouping, aggregations, ORDER BY — so do not be afraid to use it.

Prerequisites

Install the Microsoft Access Database Engine 2016 Redistributable. Choose the 64-bit edition:

MS Access Database Engine 64 bit

Click Next and then Download:

MS Access Database Engine 64 bit

The installation is straightforward — Next, Next, Next.

Limitations

  • Windows only — the ACE OLE DB driver does not exist for Linux.

Serving test and data source definitions

CsvOleDB@1 can also serve lists of definitions: a CSV file whose header row carries the property names of a test, data source or query definition, one definition per row; the query’s FROM names the file.

  • Technologies — CSV file and the provider behind it.
  • Csv@2 — the replacement: full SQL, no driver.
  • Csv@1 — the driverless reader for definition lists.
← Yaml@1
Dax@1 →