How to validate CSV, JSON and Excel files

A practical guide to validating flat files — why spreadsheets break in ways databases never do, the six checks that catch it, and how to run the same data contract against a CSV that you run against your warehouse.

2026-09-05 · 9 min read

To validate a CSV, JSON or Excel file, load it into something that speaks SQL, then run the same checks you would run against a warehouse table: nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. The difference is what comes *before* the checks. A database column has a declared type and a rejected insert; a spreadsheet column has whatever the last person typed. Most flat-file incidents are parsing and typing problems, not rule violations — so the parse is where the validation really starts.

Why flat files break differently

A warehouse table has already rejected the worst data before you see it. A file has rejected nothing.

There is no schema, only a guess. Every CSV reader infers types from the first N rows. A column that holds 1, 2, 3 for ten thousand rows and N/A at row ten thousand and one is an integer column until it suddenly isn't. Change the sample size and the same file parses differently.

Excel silently rewrites your data. Leading zeros vanish from postcodes and product codes, long identifiers become scientific notation (1.23457E+14), and anything shaped like a date becomes one. The problem is well enough known that the human gene-naming committee renamed several genes because Excel kept converting symbols like SEPT2 into dates. If a file has passed through Excel, assume some columns were reformatted on the way.

Delimiters and quoting are a guess too. A semicolon-delimited export from a European locale parsed as comma-delimited yields one giant column. An unescaped quote inside a field shifts every subsequent column by one — and only for some rows, which is worse than failing outright.

Encoding is unstated. A file written as Windows-1252 and read as UTF-8 turns é into é. Nothing errors; the data is just quietly wrong.

The header may not be row 1. Exports from reporting tools routinely open with a title row, a blank row, and a "Generated on…" line before the real header.

None of these are rule violations. They all produce a file that parses "successfully" into garbage, which is why a flat-file workflow needs a preview-and-confirm step before any rule runs.

Get the parse right first

Before writing a single check, confirm four things:

1. Delimiter and quote character. Comma, semicolon, pipe and tab are all common. Look at the parsed preview, not the raw text.
2. Header row. Does column one read order_id, or Sales export — Q3?
3. Inferred types. This is the one people skip. If an identifier column came back as a number, you have already lost the leading zeros.
4. Row count. If a 50,000-row file previews as 3 rows, quoting is broken.

Catalyst makes this an explicit step: you upload the file, it parses with DuckDB and shows the resulting columns, inferred types and a row preview, and you adjust the delimiter, quote character and header setting until the preview is right. Only then does it become a dataset. For .xls and .xlsx you also pick the worksheet, because a workbook with Data, Pivot and Notes tabs will otherwise default to whichever happens to be first.

A note on identifiers specifically: if a column is a code rather than a quantity — order numbers, SKUs, postcodes, account numbers — you want it as text, not a number. No arithmetic is ever done on it, and typing it as a number is how 00123 becomes 123 permanently.

The six checks every file needs

Once the file is a dataset, it is queryable with ordinary SQL. Catalyst registers each uploaded file as a DuckDB view, so the validation engine runs the same compiled SQL it would run against Postgres — no separate code path and no second rule language.

1. Completeness — nulls in required columns

SELECT count(*) AS violations
FROM files.orders_csv
WHERE order_id IS NULL OR trim(order_id) = '';

For files, always check the empty string as well as NULL. A CSV has no concept of null — an empty field is two adjacent commas, and readers differ on whether that becomes NULL or ''. Half your rows can be blank while a naive IS NULL check reports perfect completeness.

2. Uniqueness — duplicate keys

SELECT count(*) AS violations
FROM (
    SELECT order_id
    FROM files.orders_csv
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING count(*) > 1
);

Files have no primary key and no unique index, so nothing has ever stopped a duplicate. The most common cause is mundane: two exports covering overlapping date ranges, concatenated.

3. Conformity — values outside an allowed set

SELECT count(*) AS violations
FROM files.orders_csv
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

Keep the null guard — NULL NOT IN (...) is NULL, so those rows silently leave the count.

This check earns its keep on files more than anywhere else, because free-typed spreadsheet columns drift: paid, Paid, PAID, paid with a trailing space. Consider trimming and lower-casing in the rule if the source is human-maintained.

4. Accuracy — numbers outside a plausible range

SELECT count(*) AS violations
FROM files.orders_csv
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Watch for currency symbols and thousands separators. €1.234,56 does not parse as a number in most readers; it either fails or becomes 1.234. If a numeric column came back as text in the preview, that is why.

5. Conformity — malformed identifiers

SELECT count(*) AS violations
FROM files.orders_csv
WHERE reference IS NOT NULL
  AND NOT regexp_matches(reference, '^ORD-[0-9]{6}
#39;);

DuckDB uses RE2, so anchored patterns, character classes and quantifiers work as written — no translation, unlike SQL Server. A pattern check is the fastest way to catch a column Excel reformatted: if ORD-000123 became ORD-123, this fires.

6. Timeliness — freshness

SELECT count(*) AS violations
FROM files.orders_csv
WHERE created_at < now() - INTERVAL 24 HOUR;

Dates are the most dangerous column type in a flat file. 03/04/2026 is 3 April or 4 March depending on the locale of whoever produced it, and both parse without error. If a date column matters, check its range explicitly — a file where every date lands in the first twelve days of the month is a file that was parsed with the wrong day/month order.

Cross-checking a file against your warehouse

The most valuable flat-file check is often not about the file in isolation. A supplier list, a manual correction sheet or a finance export is usually meant to reconcile with something you already hold:

SELECT count(*) AS violations
FROM files.suppliers_xlsx f
LEFT JOIN files.known_suppliers k ON f.supplier_id = k.id
WHERE f.supplier_id IS NOT NULL
  AND k.id IS NULL;

Rows in the file that do not exist upstream are either new records or typos, and knowing which before you load them is the entire point of validating on the way in rather than after.

From ad-hoc checks to a data contract

The reason to treat a file as a dataset rather than a one-off script is that files recur. The same supplier sheet arrives every month, from the same person, with the same failure modes. Writing the expectations down once means the second upload is checked for free.

Catalyst uses the Open Data Contract Standard, and a file contract looks exactly like a warehouse one:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders_csv
  version: 1.0.0
  owner: finance-ops
schema:
  - name: orders_csv
    physicalName: orders_csv
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: error
            mustBe: "'^ORD-[0-9]{6}
#39;"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: error
        mustBe: "> 0"

Two things are worth pointing out.

The regex rule on reference sits at error severity here, where the warehouse guides set it to warning. That inversion is deliberate: in a warehouse the column type already constrains the value, so a pattern miss is usually a data-entry oddity. In a file, a pattern miss is often evidence that the *parse* went wrong — and that is worth stopping on.

The table-level rowCount rule matters more here too. An empty result after an upload usually means the delimiter or sheet selection was wrong, not that the business had no orders.

Practical limits

A few things to know before pointing a workflow at this:

• Uploads are capped at 25 MB per file, checked against the declared Content-Length and again while reading, since a client can misreport its size. Larger extracts belong in a warehouse.
• Supported formats are .csv, .json (array of objects or NDJSON), .xls and .xlsx. Spreadsheets are normalised to CSV once at upload, so everything downstream deals with one parse path.
• Files are snapshots. A dataset points at the bytes you uploaded; re-uploading is an explicit act. That is the right default — you want to know when the underlying data changed.
• Uploaded files are encrypted at rest and read only to answer a check.

Flat-file gotchas worth knowing

Frequently asked questions

How do I validate a CSV file without writing code?

Upload it, confirm the parse (delimiter, quote character, header row, inferred types), then declare the expectations — required, unique, allowed values, numeric range, pattern. Catalyst turns the file into a DuckDB-backed dataset and compiles those rules to SQL, so the same contract format covers a CSV and a warehouse table.

Can I validate Excel files, or do I need to convert to CSV first?

.xls and .xlsx upload directly; pick the worksheet at upload time. The file is normalised to CSV once during ingest so everything downstream has a single parse path. Be aware that anything Excel touched may already have been reformatted — stripped leading zeros, scientific notation, autocorrected dates — which is exactly what the pattern and range rules are there to catch.

Why does my CSV validation pass when the data is obviously wrong?

Almost always because the parse is wrong rather than the rules. The usual causes: an empty field became '' instead of NULL, so the completeness check saw a value; the wrong delimiter put everything into one column, so the checked column is empty and every null guard skipped it; or a column inferred as text makes a numeric range rule compare strings. Check the parsed preview before trusting a passing run.

How big a file can I validate?

Catalyst caps uploads at 25 MB per file, which covers most manual exports, reference sheets and supplier lists. Beyond that the file really belongs in a warehouse — load it into PostgreSQL or BigQuery and validate it there, where partition pruning and indexes make the checks cheap.

Is validating a file different from validating a database table?

The rules are identical — the same contract, the same six checks. What differs is everything before them. A database has already enforced types and rejected malformed rows; a file has enforced nothing, so parsing and typing are where most defects live. Get the parse right and the rest is the same job.