Reading, Cleaning and Writing CSV Data in Python

Real CSV files are messy. Empty rows, extra spaces, numbers with commas in them, a total column someone typed "N/A" into. The job is not just reading the file. It is deciding what to do with every bad row, on purpose, and writing a clean file you can trust.

Why this matters

Splitting lines with line.split(",") works on clean test data and breaks on the first real file. A customer named "Reyes, Ana" has a comma inside quotes, and your columns shift by one. Every value after that is in the wrong place, and nothing crashes. Use the csv module. It handles quoting for you.

The sample file

An order export, the way these files usually arrive:

order_id,customer,amount,date
1001,"Reyes, Ana", 1200.50 ,2026-10-01
1002,Ben Cruz,"2,350.00",2026-10-01

1003,Carla Lim,N/A,2026-10-02
1004,  Dan Uy ,980,2026-10-02
1005,Ella Go,450,10/03/2026

Five orders and one empty line. Problems: a quoted comma (fine with the csv module), spaces around values, a thousands separator, an empty line, a text value in the amount, and a date in a different format.

Step 1: read with DictReader

import csv

with open("orders.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        print(row)

{'order_id': '1001', 'customer': 'Reyes, Ana', 'amount': ' 1200.50 ', 'date': '2026-10-01'}
{'order_id': '1002', 'customer': 'Ben Cruz', 'amount': '2,350.00', 'date': '2026-10-01'}
{'order_id': '1003', 'customer': 'Carla Lim', 'amount': 'N/A', 'date': '2026-10-02'}
{'order_id': '1004', 'customer': '  Dan Uy ', 'amount': '980', 'date': '2026-10-02'}
{'order_id': '1005', 'customer': 'Ella Go', 'amount': '450', 'date': '10/03/2026'}

Each row is a dictionary keyed by the header, so row["amount"] still works if someone adds a column in the middle. DictReader skips completely empty lines on its own. Everything is still a string, including the numbers. And newline="" is not optional: the csv module handles line endings itself, and without it you get blank rows on Windows when writing, and broken values when a quoted field contains a line break.

Step 2: clean each row in one function

Put all the cleaning rules in one place, and make it raise an error with a clear reason when a row can't be fixed:

from datetime import datetime


def clean_row(row):
    order_id = int(row["order_id"])
    customer = row["customer"].strip()
    amount_text = row["amount"].strip().replace(",", "")
    try:
        amount = float(amount_text)
    except ValueError:
        raise ValueError(f"amount is not a number: {row['amount']!r}")
    try:
        date = datetime.strptime(row["date"].strip(), "%Y-%m-%d").date()
    except ValueError:
        raise ValueError(f"date is not YYYY-MM-DD: {row['date']!r}")
    return {"order_id": order_id, "customer": customer, "amount": amount, "date": date}

Notice what it does not do. It doesn't guess that 10/03/2026 means October 3. In some countries it means March 10. Guessing wrong gives you dates that look valid and are wrong, which is worse than an error. It rejects the row and says why.

Step 3: keep good rows, report bad ones

import csv
from clean import clean_row

good, rejected = [], []
with open("orders.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        try:
            good.append(clean_row(row))
        except ValueError as error:
            rejected.append((reader.line_num, str(error)))

print(f"{len(good)} good rows, {len(rejected)} rejected")
for line, reason in rejected:
    print(f"  line {line}: {reason}")
print("Total:", sum(r["amount"] for r in good))

3 good rows, 2 rejected
  line 5: amount is not a number: 'N/A'
  line 7: date is not YYYY-MM-DD: '10/03/2026'
Total: 4530.5

reader.line_num gives the line in the file, counting the header and the empty line, so the person who owns the export can open it and find the row. That report is the difference between "the total looks low" and "two rows were rejected, here they are".

Step 4: write the clean file

import csv
from clean import clean_row

with open("orders.csv", newline="", encoding="utf-8") as f:
    rows = []
    for row in csv.DictReader(f):
        try:
            rows.append(clean_row(row))
        except ValueError:
            pass  # reported in step 3; this example only writes the good rows

with open("orders_clean.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["order_id", "customer", "amount", "date"])
    writer.writeheader()
    writer.writerows(rows)

with open("orders_clean.csv", encoding="utf-8") as f:
    print(f.read(), end="")

order_id,customer,amount,date
1001,"Reyes, Ana",1200.5,2026-10-01
1002,Ben Cruz,2350.0,2026-10-01
1004,Dan Uy,980.0,2026-10-02

DictWriter puts quotes back around "Reyes, Ana" automatically. Dates come out as YYYY-MM-DD, which sorts correctly and every spreadsheet reads. Write to a new file. Don't overwrite the original export; if your cleaning has a bug, you still have the source.

When the file came from Excel

• Odd characters before the first header (\ufeffid): open with encoding="utf-8-sig".
• Semicolons instead of commas: common with European number settings. Use csv.DictReader(f, delimiter=";").
• Leading zeros gone, like phone numbers or product codes: Excel removed them before Python saw the file. Ask for the export as text, or fix it at the source. Python can't know what the zeros were.

Common mistakes

• Using split(","). Breaks on the first quoted comma.
• Converting with float(row["amount"]) and no cleaning. Crashes on "2,350.00", or on a space if the value is empty.
• Cleaning rules spread across the loop. Put them in one function so they can be tested, which is exactly what the testing lesson does later.

What happens if you skip this step

The script "works". It reads the file, writes a total, and nobody checks the total against the source, because why would they. Two rows were skipped, or a comma shifted a column, and the report has been wrong since the first run. The cost isn't the bug. It is every decision made from the wrong number before someone noticed.

If your CSV script rejected three rows tomorrow, who would find out, and how?