Reference Utility Data

Schema-Driven File Validation

A small Python validation primitive for checking headers, required fields, types, allowed values, and maximum lengths in tabular files.

Problem Data exports often fail only after import because required columns, dates, numbers, allowed values, or field lengths were not checked first.

Outcome A reusable validator that reports row, column, and reason for every schema violation before the file reaches the destination system.

This is a modernized version of the 2016 Excel Validator article. The original implementation used an Excel macro workbook to load a file, read a mapping table, validate cells, and highlight failures. The durable mechanism is the schema: describe the expected columns once, then validate every imported row against that contract.

Code

from __future__ import annotations

import csv
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path


@dataclass(frozen=True)
class FieldRule:
    name: str
    kind: str = "text"
    required: bool = False
    max_length: int | None = None
    allowed: set[str] | None = None
    date_format: str = "%Y-%m-%d"


@dataclass(frozen=True)
class ValidationError:
    row: int
    column: str
    message: str


def validate_value(value: str, rule: FieldRule) -> str | None:
    value = value.strip()
    if not value:
        return "required value is missing" if rule.required else None

    if rule.allowed is not None and value not in rule.allowed:
        return f"{value!r} is not in {sorted(rule.allowed)!r}"

    if rule.kind == "int":
        try:
            int(value)
        except ValueError:
            return f"{value!r} is not an integer"
    elif rule.kind == "decimal":
        try:
            float(value)
        except ValueError:
            return f"{value!r} is not a number"
    elif rule.kind == "date":
        try:
            datetime.strptime(value, rule.date_format)
        except ValueError:
            return f"{value!r} does not match {rule.date_format}"

    if rule.max_length is not None and len(value) > rule.max_length:
        return f"length {len(value)} exceeds {rule.max_length}"

    return None


def validate_csv(path: Path, schema: list[FieldRule]) -> list[ValidationError]:
    errors: list[ValidationError] = []
    rules = {rule.name: rule for rule in schema}

    with path.open(newline="", encoding="utf-8-sig") as handle:
        reader = csv.DictReader(handle)
        actual_headers = reader.fieldnames or []
        expected_headers = [rule.name for rule in schema]

        if actual_headers != expected_headers:
            errors.append(
                ValidationError(
                    row=1,
                    column="header",
                    message=f"expected {expected_headers!r}, got {actual_headers!r}",
                )
            )

        for row_number, row in enumerate(reader, start=2):
            for column, rule in rules.items():
                message = validate_value(row.get(column, ""), rule)
                if message:
                    errors.append(ValidationError(row_number, column, message))

    return errors

Usage

from pathlib import Path

schema = [
    FieldRule("trade_id", required=True, max_length=20),
    FieldRule("trade_date", kind="date", required=True, date_format="%Y-%m-%d"),
    FieldRule("amount", kind="decimal", required=True),
    FieldRule("currency", required=True, allowed={"EUR", "GBP", "USD"}),
]

errors = validate_csv(Path("export.csv"), schema)
for error in errors:
    print(f"row {error.row}, {error.column}: {error.message}")

How It Works

The validator keeps the destination contract separate from the input file. A FieldRule defines each expected column, and validate_csv compares both the header order and each row value against those rules.

That makes it useful for import gates, nightly reconciliation files, partner feeds, and spreadsheet-to-system workflows where bad rows should be rejected before they mutate production data.

Notes

The original article highlighted invalid Excel cells in place. This version returns structured errors instead, which is easier to use in command-line tools, CI checks, web upload handlers, and batch jobs. If you need spreadsheet output, write the returned errors to a report sheet or annotate the workbook after validation.

Source

Original article: Project 6: Validator

Full Explanation

The 2016 post describes the Excel macro workflow: load a file, read a configurable mapping table, validate headers and cells, then surface errors back to the user.