Get Help

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

Name
MyExcelData
Technology
MS Excel file (xlsx)
Provider
Excel@2
Connection string
./2023.xlsx
Sheets
sheet1, sheet2
Normalize column names
true
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

Setting Default Meaning
Sheets all sheets Comma-separated names of the worksheets to load, case-insensitive. Naming a sheet that does not exist is an error. Completely empty sheets (not even headers) are skipped. Load only the part of the workbook you test — CAT then does not have to load unnecessary data.
Headers true The first row holds the column names; with false every row is data.
Normalize column names false Normalize column names — see below.
Normalize table names false Normalize table (sheet) names — see below.
All varchars false Do not guess data types; import every column as VARCHAR.

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 every Excel@2 data source is a table, so they can be combined freely — joins, UNION, UNION ALL, EXCEPT, INTERSECT. 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.

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:

Microsoft Visual C++ Redistributable web download link

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.
  • Excel@2 cannot serve lists of test or data source definitions; Excel@1 can.
  • Needs access to extensions.duckdb.org at start — see Prerequisites.

Serving test and data source definitions

Excel@2 cannot serve lists of definitions; use Excel@1 for that.

← Excel@1
MySql@1 →