ExcelOleDB@1
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.
ORDER BY or GROUP BY. The main disadvantage is you need to have a driver installed.Important
-
We are actively seeking for alternatives to this driver. We’ll come with some better alternative soon.
-
You’ll likely experience problems with this driver, if the Excel workbook you are trying to read is open (especially if it is open by somebody else, not by you). Ensure the MS Excel file you work with is closed.
Example:
- 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)
FROM clause in the example. You refer to a sheet by it’s name, with $ at the end. Use square brackets.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.