Clean a messy CSV with Python

CSV cleanup is a strong first business automation: the input is familiar, the result is visible, and the rules can be tested. The safe pattern is read, normalize, validate, report, and only then write the clean file.

Start with explicit rules

Write down what a valid row means before touching code. In this example, a customer needs a non-empty name, a usable email address, and an integer order total. Clear rules prevent an automation from silently inventing data.

Use DictReader for readable code

A dictionary row lets the code say row['email'] instead of row[3]. Normalize surrounding whitespace and casing at the boundary so the rest of the program works with predictable values.

Keep rejected rows

Never make bad records disappear. Save the row number and reason so a person can correct the source. That audit trail is part of the product, not an afterthought.

Verify the result

Count input, accepted, and rejected rows. Open the output once, confirm its headers, and run the script twice to make sure it produces the same result. Idempotence makes scheduled automations safer.

Working example

import csv
from pathlib import Path

source = Path("orders.csv")
clean = []
rejected = []

with source.open(newline="", encoding="utf-8-sig") as handle:
    reader = csv.DictReader(handle)
    for line_number, row in enumerate(reader, start=2):
        name = (row.get("name") or "").strip()
        email = (row.get("email") or "").strip().lower()
        try:
            total = int((row.get("total_cents") or "").strip())
        except ValueError:
            rejected.append((line_number, "invalid total"))
            continue
        if not name or "@" not in email or total < 0:
            rejected.append((line_number, "missing or invalid field"))
            continue
        clean.append({"name": name, "email": email, "total_cents": total})

with Path("orders_clean.csv").open("w", newline="", encoding="utf-8") as handle:
    writer = csv.DictWriter(handle, fieldnames=["name", "email", "total_cents"])
    writer.writeheader()
    writer.writerows(clean)

print(f"accepted={len(clean)} rejected={len(rejected)}")

Remember: A dependable data automation makes its rules, failures, and counts visible.