Excel@1 Provider lets you read MS Excel files without any drivers installed.
ExcelOleDB@1 provider allows you to use many SQL constructs, such as
ORDER BY, but it needs a driver installed. This provider (
Excel@1) is here to help you out in cases, when you don’t have that driver installed.
Another difference from ExcelOleDB@1 is that the file you are testing can be open during CAT runs. You can edit it and save it and run CAT without closing the file.
Data Sources: - Provider: Excel@1 Connection string: ./MyData.xlsx # file in this directory (where the project file is) Name: MyExcelData
Connection string, you provide full path to a
.xlsx file. Older
.xls format is NOT supported. In queries, you then specify what sheet you are querying in the
So with this data source:
- Provider: Excel@1 Connection String: "T:\\MySystemTests\\GeneratedExcelFiles\\2023.xlsx" Name: generated xlsx
You can then use queries like
SELECT * FROM [Sheet1],
SELECT * FROM [Sheet2] etc.
If the provider finds any row that has at least one cell with strike-through text, it ignores the row (behaviour is just as if that row didn’t exist at all).
This provider is driverless, but you still provide the query in form of a SQL statement - it is even required, because CAT needs to know the sheet you want to iterate (in the
Connection string you specify only a file).
Only these clauses are supported:
|SELECT||You can specify either * or comma separated list of column names, enclosed in square brackets. Different order of columns in
|FROM||Just name of an existing sheet in the file specified in the connection string. Square brackets arround the sheet name are recommended. No joins allowed.|
SELECT [Last Name], [First name] FROM [Sheet1] WHERE [First Name] = 'Helena' AND [ID] = 2