--- title: "Excel@2" description: "Excel@2 — xlsx workbooks loaded into an in-memory DuckDB database, full SQL, no driver needed" url: "https://docs.justcat.it/reference/data-sources/providers/excel-2/" --- # Excel@2 ## 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 **Properties** Name : MyExcelData Provider : Excel@2 Technology : Excel Connection string : ./2023.xlsx Sheets : sheet1, sheet2 Normalize column names : true **YAML** ```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 | 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, … ```yaml 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](https://docs.justcat.it/how-to-guides/data-platforms/query-csv-and-excel-with-duckdb-sql/ "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](visual-cpp-redistributable.png) 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](https://docs.justcat.it/reference/data-sources/providers/excel-1/ "Excel@1") for that. ## Related * [Technologies](https://docs.justcat.it/reference/data-sources/technologies/ "Technologies") — MS Excel file and the provider behind it. * [Excel@1](https://docs.justcat.it/reference/data-sources/providers/excel-1/ "Excel@1") — the simple worksheet reader that also serves definition lists and honours strike-through. * [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") — the same DuckDB engine over CSV files. * [Query CSV and Excel data with DuckDB SQL](https://docs.justcat.it/how-to-guides/data-platforms/query-csv-and-excel-with-duckdb-sql/ "Query CSV and Excel data with DuckDB SQL") — the SQL you can write against the loaded tables, by example.