---
title: "Test CSV and Excel files"
description: "Test data in CSV files and Excel workbooks — the file delivered by a partner, the budget the business maintains, the expected results of a test — with full SQL, and compare them with the database they should match"
url: "https://docs.justcat.it/how-to-guides/data-platforms/test-csv-and-excel-files/"
---
# Test CSV and Excel files


## What you can test

| File | Provider | What happens |
|---|---|---|
| CSV (and any delimited text file) | [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") | every file becomes a table of an in-memory DuckDB database for the run; full SQL |
| Excel `.xlsx` | [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") | every worksheet becomes a table, named `"<data source>"."<sheet>"`; full SQL |
| YAML | [Yaml@1](https://docs.justcat.it/reference/data-sources/providers/yaml-1/ "Yaml@1") | a list in a YAML file is a table — the place for a small table of expected values |

No driver (the Visual C++ Redistributable on Windows). In CAT Studio the technologies are *CSV file*, *MS Excel file (xlsx)* and *YAML*.

## Add the data source


**Properties**



Name
: budget

Provider
: Excel@2

Connection string
: airport_budget.xlsx

Sheets
: Main, Details

Normalize column names
: true





Name
: partner feed

Provider
: Csv@2

Connection string
: feeds/orders_*.csv





**YAML**


```yaml
Data sources:
- Name: budget
  Provider: Excel@2
  Connection string: airport_budget.xlsx     # relative to the project file
  Sheets: Main, Details                      # optional - all sheets when omitted
  Normalize column names: true               # optional - spaces and accents out of column names

- Name: partner feed
  Provider: Csv@2
  Connection string: feeds/orders_*.csv      # a file, or a pattern for several
```




Paths are relative to the project file, so the files can sit next to it in Git — or on a share, with an absolute path or an environment variable. Details of both providers, delimiters, headers, naming: [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2"), [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2").

## Write the tests

Inside one file — the workbook's sheets are tables you can join:


**Properties**



Name
: Every budget line has details

Suite
: Budget consistency

Data source
: budget

Query
: ```sql
  SELECT m.Item, m.Amount, SUM(d.Amount) AS DetailAmount
  FROM budget.Main AS m LEFT JOIN budget.Details AS d ON d.Item = m.Item
  GROUP BY m.Item, m.Amount
  HAVING m.Amount <> COALESCE(SUM(d.Amount), 0)
  ```

Expectation
: set is empty





**YAML**


```yaml
Tests:
- Name: Every budget line has details
  Suite: Budget consistency
  Data source: budget
  Query: |
    SELECT m.Item, m.Amount, SUM(d.Amount) AS DetailAmount
    FROM budget.Main AS m LEFT JOIN budget.Details AS d ON d.Item = m.Item
    GROUP BY m.Item, m.Amount
    HAVING m.Amount <> COALESCE(SUM(d.Amount), 0)
  Expectation: set is empty
```




Against the system the file should match — the reason files are worth testing at all:


**Properties**



Name
: Partner orders were all loaded

Suite
: Feed vs warehouse

First data source
: partner feed

First query
: SELECT order_id, amount FROM "partner feed"."orders_20260821" ORDER BY order_id

Second data source
: DWH

Second query
: SELECT SourceOrderId, Amount FROM stage.PartnerOrders WHERE FeedDate = '20260821' ORDER BY SourceOrderId

Expectation
: sets match

Key
: 1





**YAML**


```yaml
- Name: Partner orders were all loaded
  Suite: Feed vs warehouse
  First data source: partner feed
  First query: SELECT order_id, amount FROM "partner feed"."orders_20260821" ORDER BY order_id
  Second data source: DWH
  Second query: SELECT SourceOrderId, Amount FROM stage.PartnerOrders WHERE FeedDate = '20260821' ORDER BY SourceOrderId
  Expectation: sets match
  Key: 1
```




The SQL you can write — joins, `CASE`, casts, text and date functions — is on [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 get-started sample project (*Budget Tests*) is exactly this kind of test over one workbook — see [Explore sample project](https://docs.justcat.it/get-started/cat-studio/explore/ "Explore sample project").

## Good to know

* A file is loaded **once per run per thread** and dropped afterwards; with hundreds of thousands of rows keep those tests single-threaded — see [Run tests in parallel](https://docs.justcat.it/how-to-guides/organize-and-run-tests/run-tests-in-parallel/ "Run tests in parallel").
* A workbook with merged header cells, or data that starts at row 5, cannot be read sensibly — give CAT a sheet that is a table: a header row, then rows.
* The same files can hold **test definitions**, not only data: a worksheet with one test per row is a test list — see [Organize a growing test base](https://docs.justcat.it/how-to-guides/organize-and-run-tests/organize-test-definitions/ "Organize a growing test base"). (That part uses `Excel@1`/`Csv@1`; `@2` serve data only.)

## Related

* [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, by example.
* [Csv@2](https://docs.justcat.it/reference/data-sources/providers/csv-2/ "Csv@2") · [Excel@2](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "Excel@2") · [Yaml@1](https://docs.justcat.it/reference/data-sources/providers/yaml-1/ "Yaml@1")