How to Handle Bad CSV Data in Python (Without Crashing)

Why CSV imports crash halfway through

Python's csv.DictReader reads row by row, but your code crashes when it tries to process a bad row - a missing field comes back as None, a price like $12.99 or EUR 12,99 raises ValueError on float(), and a blank row has no data at all. Without any guard, the whole import dies at that row and nothing after it reaches the database.

Wrap per-row parsing in try/except

The key pattern: validate and parse first (no database state changes), then write. If parsing fails, log and continue.

import re, csv, psycopg2

def clean_price(raw):
    # strip currency symbols and whitespace, then parse
    s = re.sub(r'[^\d.\-]', '', str(raw or ''))
    return float(s)

def import_products(csv_path, conn):
    cur = conn.cursor()
    imported = skipped = 0

    with open(csv_path, encoding='utf-8', errors='replace') as f:
        reader = csv.DictReader(f)
        for row in reader:
            try:
                sku      = (row.get('sku')      or '').strip()
                name     = (row.get('name')     or '').strip()
                category = (row.get('category') or '').strip()
                if not sku or not name or not category:
                    skipped += 1
                    continue
                price = clean_price(row.get('price'))
                stock = int(row.get('stock') or 0)
                if price <= 0:
                    skipped += 1
                    continue
            except (ValueError, TypeError) as e:
                print(f"Skipped row {row.get('sku', '?')}: {e}")
                skipped += 1
                continue

            cur.execute(
                "INSERT INTO products (sku, name, category, price, stock) "
                "VALUES (%s, %s, %s, %s, %s) "
                "ON CONFLICT (sku) DO UPDATE SET "
                "name=EXCLUDED.name, price=EXCLUDED.price, stock=EXCLUDED.stock",
                (sku, name, category, price, stock)
            )
            imported += 1

    conn.commit()
    cur.close()
    print(f"Imported: {imported}  Skipped: {skipped}")

What each guard does

• errors='replace' on open() - bad encoding bytes become ? instead of raising UnicodeDecodeError. Your loop never sees the encoding problem.
• row.get('field') or '' + .strip() - a missing column key or an empty field both collapse to an empty string; the not sku check catches it.
• clean_price regex - strips $, EUR, spaces, commas before float(). Extend the regex for other formats as your vendors evolve.
• try/except (ValueError, TypeError) - catches unparseable numbers and None arithmetic in one block, prints which SKU caused the skip, and continues.
• Validate before any DB write - if parsing raises, you haven't touched the database, so there's nothing to roll back. Keeps the error path simple.

Adding a dead-letter log

For production pipelines, write rejected rows to a separate file instead of just printing them:

with open('import_errors.csv', 'w', newline='') as errfile:
    writer = csv.DictWriter(errfile, fieldnames=['row', 'reason'])
    writer.writeheader()
    # inside the except block:
    writer.writerow({'row': str(row), 'reason': str(e)})

Teams review this file to find patterns - a vendor that always sends prices with commas as decimal separators, or a category field that went blank after an upstream schema change.