Get Help

Excel@1

Excel@1 reads one worksheet of an .xlsx workbook with a minimal SQL subset and skips rows struck through. Nothing to install.

Connects to

MS Excel .xlsx workbooks; the older .xls format is not supported. For full SQL over workbook data (joins, GROUP BY, ORDER BY, functions) use Excel@2; Excel@1 is the one that can also serve lists of definitions and honours strike-through.

The workbook may stay open in Excel while CAT runs — edit it, save it and run CAT without closing the file.

Example

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

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. The worksheet is then named in the query’s FROM clause.

Name
generated xlsx
Technology
MS Excel file (xlsx)
Provider
Excel@1
Connection string
T:\\MySystemTests\\GeneratedExcelFiles\\2023.xlsx
Data sources:
- Name: generated xlsx
  Provider: Excel@1
  Technology: Excel
  Connection string: "T:\\MySystemTests\\GeneratedExcelFiles\\2023.xlsx"

Queries then read SELECT * FROM [Sheet1], SELECT * FROM [Sheet2] and so on.

Settings

Setting Default Meaning
Skip striked through true A row that has at least one cell with strike-through text is ignored — as if the row did not exist. Set to false to read such rows like any other.

Query

A SQL statement, even though no database is involved — CAT needs it to know which worksheet to read, because the connection string names only the file. Only these clauses are supported:

SQL clause Notes
SELECT Either * or a comma-separated list of column names in square brackets. The columns may be listed in a different order than in the worksheet.
FROM Just the name of an existing worksheet in the workbook given by the connection string. Square brackets around the sheet name are recommended. No joins.
WHERE Only [Column name] = 'something'; several conditions joined with AND. No functions, no OR, no brackets — simple filtering on one or more columns. 
SELECT  [Last Name], [First name]
FROM    [Sheet1]
WHERE   [First Name] = 'Helena' AND [ID] = 2

Prerequisites

None. The driver ships with CAT. Windows; on Linux it has no extra dependencies and is expected to work, but it has not been tested there.

Limitations

  • The first row of the worksheet holds the column names; reading stops at the first row that is empty in every column.

Serving test and data source definitions

Excel@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. Rows struck through are skipped — that is what the Skip striked through setting is for. Organize a growing test base shows a workbook in use.

← Dax@2
Excel@2 →