Excel@1

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 GROUP BY, HAVING, 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.

Example 

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

Connection String

In the 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 FROM clause.

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.

Strike-through

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).

Using SQL

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:

SQL clause Notes
SELECT You can specify either * or comma separated list of column names, enclosed in square brackets. Different order of columns in SELECT clause is supported (you don’t have to follow the order of columns in the file).
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.
WHERE Only [Column name] = 'something' syntax is allowed. You can use AND operator for more conditions. No functions, no OR, no brackets, just this simple filtering based on one or more columns. 
SELECT  [Last Name], [First name]
FROM    [Sheet1]
WHERE   [First Name] = 'Helena' AND [ID] = 2
← Running tests
DAX introduction →