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

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


- Provider: ExcelOleDB@1
  Connection string: ./MyData.xlsx # file in this directory (where the project file is)
  Name: MyExcelData

You need to install the driver in order to use this provider, the details follow in this article.

If you already have the driver installed, but you are having troubles, it is time to tweak the Extended properties:

- Provider: ExcelOleDB@1
  Connection string: . # this directory (where the project file is)
  Name: MyExcelData
  Extended Properties: "EXCEL 16.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;"

As you can see, you can tweak things like header (exists or not). 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='{ResolvedFilePath}';Extended Properties="{ExtendedProperties}";

Mind the two placeholders, one for file 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.

- Test name: number of rows is the same
  First Query: SELECT COUNT(*) FROM [Sheet1$]
  First Data Source: MyExcelData
  #  ... (abbreviated)

Driver Installation

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

MS Access Database Engine 64 bit

Click on the Next button and then on Download button.

MS Access Database Engine 64 bit

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