DEV Community

Wenlo
Wenlo

Posted on AI-assisted

Clean a CSV without losing quoted text or rejected rows

A supplier export contains a comma inside one item name, quotation marks inside another, and two quantities that cannot enter a positive-supplies report. We need more than a split on commas: preserve the text, isolate rejected records, write a new CSV, and prove that reading it back preserves the accepted items.

Run the example

All illustrative records are fictional. Save the following as csv_roundtrip_audit.py. The complete script was run in PythonIDE on iPhone, and the screenshots show its actual source and output.

import csv
from pathlib import Path
from tempfile import TemporaryDirectory

# Fictional workshop supplies; never touches user files.
source = '''item,quantity,note
"Tea, green",2,"Keep dry"
"Notebook ""A5""",3,"Blue cover"
Pencil,broken,"Check original entry"
Folder,-1,"Return needs separate handling"
Tape,4,"Shelf B"
'''
accepted, rejected = [], []
with TemporaryDirectory(prefix='csv-lesson-') as temp:
    root = Path(temp)
    original = root / 'source.csv'
    cleaned = root / 'cleaned.csv'
    original.write_text(source, encoding='utf-8')
    with original.open(newline='', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for record, row in enumerate(reader, 1):
            try:
                quantity = int(row['quantity'])
                if quantity <= 0:
                    raise ValueError('not a positive quantity')
            except ValueError:
                rejected.append((record, row.copy()))
                continue
            accepted.append({**row, 'quantity': quantity})
    with cleaned.open('w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(
            f, fieldnames=['item', 'quantity', 'note'])
        writer.writeheader()
        writer.writerows(accepted)
    with cleaned.open(newline='', encoding='utf-8') as f:
        restored = list(csv.DictReader(f))
    assert [row['item'] for row in restored] == [
        row['item'] for row in accepted]
    total = sum(int(row['quantity']) for row in restored)
    assert total == 9
    assert original.read_text(encoding='utf-8') == source
    print('CLEANED CSV (temporary example)')
    print(cleaned.read_text(encoding='utf-8'))
    print('Accepted records:', len(restored))
    print('Accepted units:', total)
    print('Rejected records:')
    for number, row in rejected:
        print(number, row['item'], repr(row['quantity']))
print('Temporary files removed; original user files untouched.')
Enter fullscreen mode Exit fullscreen mode

CSV example source, part 1

CSV example source, part 2

Actual CSV round-trip output in PythonIDE

Understand the checks and their limits

TemporaryDirectory gives this exercise a disposable workspace. The original is written once, the clean export goes to another file, and an assertion verifies the original text is unchanged. csv handles quoting; int validates integer text; a separate rule rejects non-positive quantities. The round-trip test checks item names and a total of nine units. Record numbers are CSV record positions, not physical line numbers: a quoted field can contain a newline.

The actual run accepted three rows totaling nine units and rejected two rows: Pencil with quantity broken, and Folder with quantity -1. The exported CSV retained the comma in Tea, green and the quotation marks in Notebook "A5". A negative quantity might be a legitimate return; this exercise sends it to review rather than silently treating it as a sale. Rejected records are retained in memory and printed, not permanently archived. Real input also needs header and row-shape validation; spreadsheet formula injection is a separate concern from CSV quoting.

Try one change

Put a newline inside a quoted note. Verify the exported names and total still pass. Then add a missing quantity and decide what information the review record must preserve.

I build PythonIDE, a Python coding app for iPhone and iPad. The example is intended to teach a workflow you can inspect and adapt.

AI assisted with drafting and translation. The example was checked in PythonIDE.

PythonIDE on the App Store

Top comments (0)