The customer sends you a CSV export that is full of problems. How do you parse it safely in Python?
Answer it out loud first, then compare. That's what makes it stick.
I never trust the file. I open it with the right encoding and newline handling, read it with csv.DictReader so columns are named, and make every row go through one validation function that returns either a clean record or a reason for rejection. I keep the rejects in a separate file with the row number and the reason, never silently drop them, and I report counts at the end: read, accepted, rejected. That gives the customer something to fix at the source.
- Inspect first: encoding, delimiter, header
- DictReader with newline=''
- One validate-and-normalise function per row
- Rejects file with row number and reason
- Report counts and sample failures
Messy-input handling is the most-reported theme in FDE coding rounds, so have a routine.
1. Look before you parse. Print the first 5 raw lines and repr() them. Check encoding (UTF-8 vs Latin-1 vs a BOM), delimiter (commas, semicolons, tabs), header row, and quoting. csv.Sniffer can guess the dialect, but its header detection is a heuristic, so verify.
2. Open correctly. The Python docs say to open CSV files with newline='', otherwise embedded newlines inside quoted fields can be misread. Pass encoding= explicitly; utf-8-sig strips a BOM.
import csv
def load(path):
good, bad = [], []
with open(path, newline="", encoding="utf-8-sig") as f:
rd = csv.DictReader(f, restkey="_extra", restval=None)
for n, row in enumerate(rd, start=2):
try:
good.append(clean(row))
except ValueError as e:
bad.append({"row": n, "reason": str(e), **row})
return good, bad
def clean(row):
if row.get("_extra"):
raise ValueError("too many fields")
amt = (row["amount"] or "").replace(",", "").strip()
if not amt:
raise ValueError("missing amount")
return {"id": row["id"].strip(), "amount": float(amt)}
restkey catches rows with too many fields; restval fills rows with too few.
3. Normalise deliberately. Trim whitespace, unify null spellings ('', N/A, NULL, -), parse dates with an explicit format (is 03/04/2026 March or April?), and strip currency symbols and thousands separators. Use Decimal for money if you will sum it.
4. Reject loudly. Write rejects with row number and reason to a file. State counts: for example 98,412 read, 97,890 accepted, 522 rejected (0.5%). If rejects exceed a threshold (say 2%), fail the run rather than load partial data.
5. Say what you would ask the customer. Is there a data dictionary? Which system produces the file? Can they fix the export instead of us patching it forever?
Scale note. Stream with the csv module for large files; pandas is fine for a few hundred MB but read with dtype=str first to avoid silent type guessing.
- The date column has both 03/04/2026 and 2026-04-03. What do you do?
- The file is 20 GB. What changes?
- How do you stop the same bad export from recurring?
- How Distyl AI hires forward deployed engineers (prep, unknown) Describes a live applied Python exercise: parse messy input such as a CSV or semi-structured documents, call an LLM API and assemble a small pipeline.
- Python skills for the forward deployed engineer interview (prep, 2026-09-18) Lists parsing messy CSV/JSON with missing fields, inconsistent formatting and unexpected types.
- Palantir Forward Deployed Engineer at a Glance (prep, unknown) Says Palantir problems skew toward data wrangling, NULLs, duplicate keys and schema drift.
- csv - CSV File Reading and Writing (Python docs) (docs, unknown) Documents DictReader restkey/restval, the newline='' guidance, encoding and Sniffer.