---
title: "ExcelOleDB@1"
description: "ExcelOleDB@1 — legacy: xlsx workbooks through the Microsoft ACE OLE DB driver, basic SQL, Windows only"
url: "https://docs.justcat.it/reference/data-sources/providers/exceloledb-1/"
---
# ExcelOleDB@1


> **😾:** **Deprecated.** `ExcelOleDB@1` still works, but it is no longer maintained and gets no new features. Use [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") instead — the same SQL over workbooks, no driver to install. To load *test definitions* from a workbook, use [Excel@1](https://docs.justcat.it/reference/data-sources/providers/excel-1/ "Excel@1") until `Excel@2` can serve lists.



## Connects to

MS Excel `.xlsx` workbooks, read through the Microsoft ACE OLE DB driver. Where [Excel@1](https://docs.justcat.it/reference/data-sources/providers/excel-1/ "Excel@1") only iterates a worksheet, this provider lets the driver process the data with basic SQL — `ORDER BY`, `GROUP BY` — which matters when a worksheet is not sorted the way a set comparison needs. The price is a driver to install.

## Example


**Properties**



Name
: MyExcelData

Provider
: ExcelOleDB@1

Technology
: Excel

Connection string
: ./MyData.xlsx





**YAML**


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




The driver has to be installed before this works — see Prerequisites.

## 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](https://docs.justcat.it/reference/data-sources/properties/#connection-string "Data source properties").

What CAT sends to the driver, with the file path and the [extended properties](#settings) substituted exactly as you provide them:

```
Provider=Microsoft.ACE.OLEDB.16.0;Data Source='{ResolvedFilePath}';Extended Properties="{ExtendedProperties}";
```

## Settings

| Setting | Default | Meaning |
|---|---|---|
| `Extended Properties` | `EXCEL 12.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;` | The driver's workbook options — header row present or not, import mode, rows scanned for type guessing. Passed to the driver as they are. |

When the driver is installed but the data comes out wrong, tweak the extended properties:


**Properties**



Name
: MyExcelData

Provider
: ExcelOleDB@1

Technology
: Excel

Connection string
: ./MyData.xlsx

Extended Properties
: EXCEL 16.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;





**YAML**


```yaml
Data sources:
- Name: MyExcelData
  Provider: ExcelOleDB@1
  Technology: Excel
  Connection string: ./MyData.xlsx # file in this directory (where the project file is)
  Extended Properties: "EXCEL 16.0 XML;HDR=YES;IMEX=1;MAXSCANROWS=0;"
```




For the meaning of the individual options refer to the Microsoft Access Database Engine 2016 documentation.

> **🐱:** Microsoft did an excellent job of hiding the documentation (if it even exists) of these settings. You might need to spend considerable time with your favourite search engine.



## Query

A SQL statement as the ACE driver understands it. A worksheet is referred to by its name with `$` at the end, in square brackets: `[Sheet1$]`. A reference of the supported SQL features is as hard to find as the one for the extended properties; basic SQL works, so do not be afraid to use it.

```yaml
Tests:
- Name: number of rows is the same
  First query: SELECT COUNT(*) FROM [Sheet1$]
  First data source: MyExcelData
  #  ... (abbreviated)
```

## Prerequisites

Install the <a href="https://www.microsoft.com/en-us/download/details.aspx?id=54920" target="_blank">Microsoft Access Database Engine 2016 Redistributable</a>. Choose the 64-bit edition:

![MS Access Database Engine 64 bit](ms-access-choose-64-bit.png)

Click *Next* and then *Download*:

![MS Access Database Engine 64 bit](ms-access-download.png)

The installation is straightforward — Next, Next, Next.

## Limitations

* Windows only — the ACE OLE DB driver does not exist for Linux.
* The workbook must be closed. The driver is likely to fail if the file is open — especially when somebody else has it open, not you.

> **🙀:** **Ensure the MS Excel file you work with is closed.**



## Serving test and data source definitions

`ExcelOleDB@1` can also serve [lists](https://docs.justcat.it/reference/project-file/lists/ "Lists") of definitions: a worksheet whose header row carries the property names of a [test](https://docs.justcat.it/reference/tests/properties/ "Test properties"), [data source](https://docs.justcat.it/reference/data-sources/properties/ "Data source properties") or [query](https://docs.justcat.it/reference/queries/properties/ "Query properties") definition, one definition per row; the query's `FROM` names the worksheet (`[Sheet1$]`). [Organize a growing test base](https://docs.justcat.it/how-to-guides/organize-and-run-tests/organize-test-definitions/ "Organize a growing test base") shows a workbook in use.

## Related

* [Technologies](https://docs.justcat.it/reference/data-sources/technologies/ "Technologies") — MS Excel file and the provider behind it.
* [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") — the replacement: full SQL, no driver.
* [Excel@1](https://docs.justcat.it/reference/data-sources/providers/excel-1/ "Excel@1") — the driverless reader for definition lists.