Get Help

ExcelOleDB@1

ExcelOleDB@1 reads an .xlsx workbook through the Microsoft ACE OLE DB driver with basic SQL. Needs the Microsoft Access Database Engine 2016 Redistributable; Windows only.

Connects to

MS Excel .xlsx workbooks, read through the Microsoft ACE OLE DB driver. Where Excel@1 only iterates a worksheet, this provider lets the driver process the data with basic SQL — ORDER BY, GROUP BY — which matters when a worksheet is not sorted the way a set comparison needs. The price is a driver to install.

Example

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

The driver has to be installed before this works — see Prerequisites.

Connection string

Not a connection string: the path to one .xlsx file, absolute or relative to the directory of the project file. The same slot is also accepted as File path — see Data source properties.

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

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

Settings

Setting Default Meaning
Extended Properties EXCEL 12.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0; The driver’s workbook options — header row present or not, import mode, rows scanned for type guessing. Passed to the driver as they are.

When the driver is installed but the data comes out wrong, tweak the extended properties:

Name
MyExcelData
Technology
MS Excel file (xlsx)
Provider
ExcelOleDB@1
Connection string
./MyData.xlsx
Extended Properties
EXCEL 16.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;
Data sources:
- Name: MyExcelData
  Provider: ExcelOleDB@1
  Technology: Excel
  Connection string: ./MyData.xlsx # file in this directory (where the project file is)
  Extended Properties: "EXCEL 16.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;"

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. A worksheet is referred to by its name with $ at the end, in square brackets: [Sheet1$]. A reference of the supported SQL features is as hard to find as the one for the extended properties; basic SQL works, so do not be afraid to use it.

Tests:
- Name: number of rows is the same
  First query: SELECT COUNT(*) FROM [Sheet1$]
  First data source: MyExcelData
  #  ... (abbreviated)

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.
  • The workbook must be closed. The driver is likely to fail if the file is open — especially when somebody else has it open, not you.

Serving test and data source definitions

ExcelOleDB@1 can also serve lists of definitions: a worksheet 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 worksheet ([Sheet1$]). Organize a growing test base shows a workbook in use.

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