CSV Output and the Normalisation Step

By the end of this lesson you can read and write CSV correctly, including fields that contain commas, and you can turn a batch of inconsistent published rows into validated records plus a list of errors that names which record and which field needs attention.

CSV is what a reviewer wants back. A shortlist that opens in a spreadsheet is immediately useful to someone who will never run your program.

It is also the format that most often gets written by hand, with commas joined by string concatenation, and that works right up until a title contains a comma or a quotation mark. An affected unquoted row can be parsed with an extra field: values after the title land in later columns of that row, often without an obvious error. Later physical rows remain separate records.

Normalisation is the other half. Module 1 cleaned one string; Module 2 validated one record. This lesson does both across a whole batch and, critically, distinguishes the values it can safely repair from the ones it must report.

Use the csv module. csv.DictReader(handle) reads the header row and yields one dictionary per row, keyed by column name. csv.DictWriter(handle, fieldnames=[...]) writes them back, with writeheader() for the header row and writerow(record) per record.

The module handles quoting in both directions: a field containing a comma is written wrapped in quotation marks, and a quoted field containing a comma is read back as one value. Splitting on commas by hand handles neither, and no amount of care makes it correct. There is one file-handling detail worth copying: when writing to a real file, open it with newline="", which prevents an extra blank line appearing between rows on some platforms.

For a well-formed row, DictReader gives you field values as text. A short row can give you None for a missing field, and extra columns can be collected under a separate key. Check the row shape before converting; then convert deliberately in the normalisation step rather than scattering conversions through your rules.

Normalisation is a small ordered pipeline per field. For this project:

• Strip surrounding whitespace only from fields whose documented policy treats it as incidental; in this worked batch, that includes notice_id and the comparison value set_aside.
• Upper-case, or otherwise case-fold, a comparison field only when its documented policy ignores case; here "total small business" and "Total Small Business" are one set-aside category.
• For a non-negative whole-number amount, verify any grouping commas are correctly placed, remove them, then convert. Report blank, missing or malformed amounts.
• Recognise missing-value spellings, then decide by field whether missing is allowed or must be reported. In particular, a missing or unparseable amount is not zero.

Then the decision that matters: repair or report. For comparison fields whose contract ignores surrounding spaces and case, strip and case-fold without guessing a new value. Keep the raw input when its original spelling matters. An amount reading "to be determined" is not repairable, and substituting zero is inventing data. Repair the mechanical, report the semantic.

An error should be a message a person can act on, which means it names the record and the field: "SPE-2026-0412: amount is not a number: 'to be determined'". "Invalid input" tells a reviewer nothing and costs them the work your program was supposed to do.

import csv
import io

raw = 'notice_id,title,amount\nA-1,"Maintenance, Building 7",310000\nA-2,Fuel Delivery,96500\n'

records = []
with io.StringIO(raw) as handle:
    for row in csv.DictReader(handle):
        records.append({
            "notice_id": row["notice_id"].strip(),
            "title": row["title"],
            "amount": int(row["amount"]),
        })

out = io.StringIO()
writer = csv.DictWriter(out, fieldnames=["notice_id", "title", "amount"])
writer.writeheader()
for record in records:
    writer.writerow(record)

print(records[0]["title"])
print(records[0]["amount"] + records[1]["amount"])
print(out.getvalue().splitlines()[1])

The first record's title contains a comma and is quoted in the source. DictReader returns it as a single value, Maintenance, Building 7, with no special handling needed on your part.

DictWriter then puts the quotation marks back when it writes that field out, because it knows the field contains the delimiter. Round-tripping a value through CSV correctly is exactly what the module is for.

int(row["amount"]) is the conversion step. DictReader produced text; the addition on the next-to-last line would have joined two strings without it.

Predict the three printed lines, including any quotation marks.

Maintenance, Building 7
406500
A-1,"Maintenance, Building 7",310000

Replace the reader loop with a hand-rolled split: read each line after the header, call line.strip().split(","), and take positions 0, 1, and 2. Predict what the first record's title and amount become.

An assistant was asked to normalise a batch so that comparisons work reliably. It produced this and said all rows are now consistent.

rows = [
    {"notice_id": "A-1", "amount": "310000", "set_aside": "Total Small Business"},
    {"notice_id": "A-2", "amount": "96,500", "set_aside": "total small business"},
    {"notice_id": "A-3", "amount": "not stated", "set_aside": "8(a)"},
]

clean = []
for row in rows:
    try:
        amount = int(row["amount"].replace(",", ""))
    except ValueError:
        amount = 0
    clean.append({
        "notice_id": row["notice_id"],
        "amount": amount,
        "set_aside": row["set_aside"].upper(),
    })

qualifying = [r for r in clean if r["amount"] >= 100000 and r["set_aside"] == "TOTAL SMALL BUSINESS"]
print(len(clean), len(qualifying))

import re

clean = []
errors = []
for row in rows:
    amount_text = row.get("amount")
    if not isinstance(amount_text, str) or not re.fullmatch(
        r"[0-9]+|[1-9][0-9]{0,2}(?:,[0-9]{3})+", amount_text.strip()
    ):
        errors.append(f"{row['notice_id']}: amount is not a number: {amount_text!r}")
        continue
    amount = int(amount_text.strip().replace(",", ""))
    clean.append({
        "notice_id": row["notice_id"].strip(),
        "amount": amount,
        "set_aside": row["set_aside"].strip().upper(),
    })

Four published rows carrying every inconsistency this module has named. Produce validated records and actionable errors, and report both.

Loading this exercise…

Optional: use the worked ReadTheCode records, which include title, to confirm that Maintenance, Building 7 survives a DictWriter and DictReader round trip. Separately, export the three clean exercise records using their actual fields: notice_id, amount and set_aside. Decide how to report the unusable fourth row without giving it a fabricated amount. A reviewer may need a separate error list or a clearly labelled status record; the capstone asks you to make that reporting choice.

Sign in to track your progress on this exercise.

1. Why is line.split(",") insufficient for reading CSV?
2. Which normalisations are safe to apply silently, and which must be reported?
3. What is wrong with defaulting an unparseable amount to zero?
4. What two things must an actionable validation error name?

Sign in to track your progress on this exercise.

The csv module handles quoting in both directions, DictReader yields text that you convert deliberately, normalisation is an ordered per-field pipeline, mechanical repairs are safe and semantic guesses are not, and every error names a record and a field. The assistant now ingests real files and reports honestly on what it could not use. Module 7 looks at where those files come from: HTTP, API contracts, and a client boundary built against fixtures.