CsvOleDB@1

CsvOleDB@1 provider lets you use advanced SQL, but needs a driver.

Sometimes you need to not only blindly iterate CSV file and evaluate your expectations, but you need also process the data from the file. Most notably, data in CSV files are often not sorted, which is a prerequisite for efficient Sets Match expectation evaluation.

Example: #

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

If you are lucky, all works for your setup on first attempt. If not, install the driver, the details follow in this article.

If you already have the driver installed, but you are having troubles with e.g., encoding, delimiters or other basic CSV stuff, it is time to tweak the Extended properties:

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

As you can see, you can tweak things like header (exists or not), encoding etc. For details on this, please refer to Micosoft Access Database Engine 2016 documentation.

If it helps, this is what CAT sends to the driver:

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

Mind the two placeholders, one for directory path and one for the extended properties - CAT hands them over to the driver exactly as you provide them.

When it commes to supported SQL features, the situation is the same - it is hard to find any reference. On the other hand, basic SQL works like charm, don’t be affraid to use basic SQL constructs (groupping, aggregations etc.)

Driver Installation #

You need to install Microsoft Access Database Engine 2016 Redistributable. Choose 64 bit edition:

Click on the Next button and then on Download button.

Installation is straightforward, just go Next, Next, Next.