Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 38 ยท Projects and Practice

Project: A Validating CSV Importer

Build a CSV importer with Pydantic: clean messy cells, validate every row, collect errors with line numbers, process in chunks, and write a clear import report.

CSV files are where data validation earns its keep. Cells are all text, dates come in three formats, numbers use commas as decimal separators, empty cells mean "unknown" or "zero" depending on the column, and someone always adds a column called "misc". In this project you build an importer for a bookshop's stock list: it cleans each row, validates it, keeps the good rows, collects precise errors for the bad ones, and writes a report a human can act on.

The design goal is never stop at the first bad row and never lose the row number. A person fixing a spreadsheet wants every problem at once, with line numbers.

flowchart LR
    F["CSV text"] --> R["csv.DictReader"]
    R --> C["row model<br/>(clean + validate)"]
    C -->|valid| G["good rows"]
    C -->|invalid| B["errors with<br/>line number"]
    G --> OUT["import / JSON lines"]
    B --> REP["report"]

Every row ends up either in the good pile or in the error report.

Step 1: the row model

Cells arrive as strings, so the model has before validators that tidy them. Empty strings become None, "12,50" becomes 12.50, and several date formats are accepted. Everything else is ordinary Pydantic.

Python Step 1: clean and validate one row

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

All four problems in the bad row were reported at once. Two lessons from this model: cleaning belongs in before validators because the raw cell is a string, and the clean rules sit on the type (Price, FlexDate), so another model can reuse them.

Step 2: read the file and collect errors

csv.DictReader gives one dictionary per line, with the header as keys. Wrap each row in a try/except, and keep the line number (the header is line 1, so data starts at line 2).

Python Step 2: good rows and an error list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the last row: its copies cell is empty, and in this simplified model an empty string is not a valid integer, so it also fails. Step 1's OptionalInt is the fix, which the full importer below includes.

Step 3: validate in chunks

For big files you do not want every row in memory at once. A generator yields chunks of rows, each chunk is validated with a TypeAdapter (one call per chunk, faster than one call per row), and results are consumed as they arrive. If a chunk contains a bad row, the importer falls back to row-by-row checking for that chunk only, to find the exact line.

Python Step 3: chunked validation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With 10 rows and chunks of 4 the first chunk (lines 2 to 5) passes in one call, the second (lines 6 to 9) hits the slow path, and so does the third. In a real file with a small share of bad rows, most chunks take the fast path.

Step 4: the report

The final piece turns the result into something a person can read and a program can parse. Pydantic helps twice more: a model for the report itself, and model_dump_json to write it.

Python Step 4: the complete importer with a report

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line shows a subtle point worth knowing: empty_to_none turned the empty copies cell into None, and the field allows None, so the row is valid but copies is None, not the default 0. Defaults apply only when a key is missing, not when it is present with an empty value. If you want 0, return 0 from the validator or add an after validator that replaces None.

Where to go from here

  • Write the good rows to a file with row.model_dump_json() per line (JSON Lines), or insert them with executemany into SQLite.
  • Read the header first and fail early with a clear message if required columns are missing.
  • Add --strict mode: abort if any row fails; default mode imports the good rows.
  • Collect duplicate keys (the same ISBN twice) with a set, a check no single-row model can do.
  • Use validate_json and chunking when the source is JSON Lines.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why are the cleaning functions BeforeValidators?

    A cell such as "12,50 โ‚ฌ" cannot be parsed as a Decimal until the text is tidied, which a before validator does.
  2. 2. The header is line 1. What line number does enumerate(reader, start=2) give the first data row?

    The header occupies line 1, so the first data row is line 2, matching what the person sees in a spreadsheet.
  3. 3. Why validate rows in chunks with a TypeAdapter?

    Validating a list in one call is efficient; falling back to row-by-row only for failing chunks finds the exact bad line.
  4. 4. A cell is empty and the column's field has a default of 0. Which value do you get if a before validator turned the empty cell into None and the field allows None?

    Defaults are used only for missing keys. A present key holds its (possibly None) value.

Summary

  • Treat every CSV cell as a string; clean it in before validators defined on reusable Annotated types.
  • Never stop at the first bad row: collect problems with line numbers and column names.
  • Validate chunks with TypeAdapter for speed, and fall back to row checks to locate failures.
  • Model the report too, and write it with model_dump_json.
  • A default applies only when the key is missing, not when it holds an empty value.

Next: Practice: Pydantic Exercises