Test CSV and Excel files

Files are data too: the extract a partner delivers every night, the budget the business keeps in Excel, the table of expected results you signed off. CAT loads them into an in-memory database and runs SQL over them — and compares them with the systems they are supposed to match.

What you can test #

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 #

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, Excel@2.

Write the tests #

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

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:

- 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. The get-started sample project (Budget Tests) is exactly this kind of test over one workbook — see 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.
• 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. (That part uses Excel@1/Csv@1; @2 serve data only.)

Related #

• Query CSV and Excel data with DuckDB SQL — the SQL, by example.
• Csv@2 · Excel@2 · Yaml@1