Excel@2

Excel@2 loads the worksheets of an .xlsx workbook into an in-memory DuckDB database and runs full SQL over them. Needs the Microsoft Visual C++ Redistributable on Windows.

Connects to #

MS Excel .xlsx workbooks; the older .xls format is not supported. Because MS Excel is not a database, CAT first loads the worksheets into an in-memory DuckDB database and the queries run against that copy; it is removed when the project is closed or the CAT process ends.

Excel@2 is the provider meant to replace both Excel@1 and ExcelOleDB@1 for data testing: one small prerequisite that most machines already have, full SQL in the queries (joins, grouping, CTEs, many functions), and a maintained, well-documented engine underneath — DuckDB.

Example #

• Properties
• YAML

Data sources:
- Name:               MyExcelData     # every data source must have a friendly name
  Provider:           Excel@2         # don't confuse with Excel@1 and ExcelOleDB@1
  Technology:         Excel
  Connection string:  ./2023.xlsx     # Relative or absolute file path.
                                      # In this case CAT expects the file "next to" your project file
  Sheets:             sheet1, sheet2  # optional, if you don't want to load all sheets
  Normalize column names: true        # optional, remove spaces and non-English characters from column names

This creates two tables in the in-memory database: "MyExcelData"."sheet1" and "MyExcelData"."sheet2". The naming convention is "DataSourceName"."SheetName".

Connection string #

Not a connection string: the path to one .xlsx file, absolute or relative to the directory of the .cat.yaml project file.

Settings #

Normalizing names. Some worksheet names and headers contain characters that make querying them inconvenient, especially across many SQL queries. CAT can normalize the names: it removes all diacritics, replaces spaces with underscores and collapses consecutive underscores into one. The Sheets setting still takes the original sheet names.

All varchars is for troubleshooting. By default CAT guesses the data types of the loaded columns; when it meets a value that does not fit the guessed type it changes the column to VARCHAR — the load is designed never to fail. So you can, for instance, write tests for expected data types; DuckDB’s TRY_CAST finds the values that break your rules.

Query #

One SQL statement in DuckDB’s dialect against the loaded tables. Every worksheet of the Excel@2 data sources a query uses is a table, so they can be combined freely — joins, UNION, UNION ALL, EXCEPT, INTERSECT. This holds wherever the query runs: in a test run, on CAT Studio’s Queries page and the test detail’s query pane, in catcli exec, in the PowerShell and Python invoke commands and in CAT Pilot — every Excel@2 and Csv@2 data source the query mentions by name is loaded before the query runs, together with the one it is run against. The data is loaded with types (numbers, dates, booleans, …), so the functions for dates and times, strings and numbers, pattern matching and regular expressions all apply. The syntax covers almost everything: aggregate functions, common table expressions (CTEs), window functions, grouping sets, pivot and unpivot, correlated subqueries, …

Tests:
- Name:         Check at least something was generated
  Suite:        Smoke tests
  Description:  Sometimes happens the generated sheet is empty, this should alert us
  Data source:  MyExcelData # name of the data source
  Query:        SELECT * FROM "MyExcelData"."Sheet1" LIMIT 1
  Expectation:  set is not empty

What that looks like in practice — SELECT, joins and set operations, expressions, functions — is in Query CSV and Excel data with DuckDB SQL; the full reference is the DuckDB SQL documentation.

When the workbook is loaded #

A test run loads only the data sources its tests use: the data source each test names, plus every Excel@2 and Csv@2 data source whose name appears in its queries. Each is loaded once per run, however many threads the run uses — the threads all read the same copy. “Every worksheet is a table” holds for the data sources a run or a query uses, not for every workbook of the project at once.

Within one session — CAT Studio with the project open, one PowerShell or Python session, one catcli process — a workbook that has not changed since the previous run is not loaded again. CAT compares the file’s size and last-write time and the data source’s settings; save the workbook (or edit a setting such as Sheets) and the next run reads it anew. An edit that is not saved is invisible, as it always was. Closing and reopening the project always loads everything afresh.

Two consequences to keep in mind:

• A query that lists tables it does not name — for example from information_schema — sees what the session has loaded so far: the data sources of this run and of the earlier runs since the project was opened, not every data source of the project. A source an earlier run loaded is checked for changes again only when a run needs it.
• A workbook replaced by a copy with exactly the same size and last-write time is not read again within the session; reopen the project to force it.

Prerequisites #

DuckDB ships with CAT; Windows and Linux alike. Two things must be in place on the machine:

Microsoft Visual C++ Redistributable (Windows) #

A very common prerequisite used by much other software, so it is probably already installed. To verify or install it, go to Latest Microsoft Visual C++ Redistributable Version and download and install the “X64” version:

If the installer shows a dialog with “Repair”, “Uninstall” and “Close” buttons, just close it — the prerequisite is already there. Otherwise install it (next, next, next…).

Internet connection #

DuckDB loads its extensions from http://extensions.duckdb.org. The URL has to be reachable — connect to the Internet and white-list it if needed. If you need to test workbook data offline, file a request using the “Get Help” button on the right of this site.

Limitations #

• No strike-through support — only Excel@1 has it. Rows with strike-through formatting are processed like any other row.
• No strike-through support also applies to lists: struck-through rows of a list come back as definitions (Excel@1 skips them).
• Project open — and every reload — pays the workbook load for each Excel@2 list, see below.
• Needs access to extensions.duckdb.org at start — see Prerequisites.

In a list #

Excel@2 serves lists of tests, data sources and queries: a worksheet with a header row carrying the property names, one definition per row.

Get list of tests from:
- Provider: Excel@2
  Connection string: TestsFromBusiness.xlsx
  Query: SELECT * FROM Tests

Before the list is read, CAT loads the workbook into a throwaway in-memory database, reads the list and discards the database. The settings are the same as on a data source (Sheets, Headers, Normalize column names, Normalize table names, All varchars), with these differences:

• The list is loaded as a data source named main, so the query is plain SELECT * FROM Tests — Tests being the worksheet — with no schema prefix. It is DuckDB SQL, not SELECT * FROM [Tests$] as with Excel@1.
• Data types are guessed exactly as on a data source (All varchars defaults to false in both), so a query over the list can filter on numbers and booleans directly, WHERE Enabled = true for instance. A column that mixes numbers and text stays text. A text column that merely looks numeric — codes with leading zeros, say — would be read as numbers; set All varchars: true on such a list.
• The list loads its own copy of the workbook. A workbook used both as a list and as a data source is read twice.

Workbooks with many sheets. A list loads every sheet of its workbook unless Sheets: narrows it. Either set Sheets: on the list, or — better — use one list whose query reads across the sheet tables instead of one list per sheet:

Get list of tests from:
- Provider: Excel@2
  Connection string: TestsFromBusiness.xlsx
  Query: |
    SELECT * FROM Smoke
    UNION ALL
    SELECT * FROM Regression

That loads the workbook once instead of once per list. Lists are read one after another when the project opens; the Threads setting plays no part in it.

Migrating from Excel@1 lists. Rewrite the query to DuckDB SQL, and note that struck-through rows are not skipped — they come back as definitions. Delete such rows or filter them out in the query.

Related #

• Technologies — MS Excel file and the provider behind it.
• Excel@1 — the simple worksheet reader that honours strike-through.
• Csv@2 — the same DuckDB engine over CSV files.
• Query CSV and Excel data with DuckDB SQL — the SQL you can write against the loaded tables, by example.