Files & the Messy Real World

Post 1 assumed the file cooperates. Files never cooperate. This is the post about everything clients actually send: wrong encodings, semicolon CSVs, JSON that isn't JSON, and spreadsheets old enough to vote.

Assumes: Post 1 — you're comfortable with functions, loops, dicts, and Counter. Everything new here is about files, not syntax.

Before you code: clarify the ask

The zip contains three files and at least two different problems. Before touching any of them, pin down what "done" means — otherwise you'll spend the day cleaning a spreadsheet nobody asked about.

Input: the November sanitation zip (CSV + notes file)
Explicitly out of scope: the 2007 routes spreadsheet
Output: the same top-10-by-borough summary as October, plus a record of what you received
Deadline: end of day

That "don't mix in the routes file" is doing quiet work: it's a scope decision, made before a line of code, that you'll be able to point to later. Write it down. Scope discipline starts here, not at the milestone.

The one mental model: bytes vs str

Every file horror in this post traces back to a single confusion. A file on disk is bytes — just numbers. A Python str is characters — letters a human can read. An encoding is the map between the two, and the file itself almost never tells you which map was used.

What you need to know	The short version
UTF-8	The modern default. Plain English text is identical in UTF-8 and ASCII, which is why it "just works" until it doesn't.
cp1252 / latin-1	What old Windows machines and older Excel exports produce. Bytes above 127 mean different characters than UTF-8 expects — that's where mojibake comes from.
The BOM	An invisible marker some Windows programs put at the start of a file. It turns your first column header into \ufeffborough and your lookups into KeyErrors.
Mojibake	Text decoded with the wrong map: "café" becomes "café", apostrophes become "Â". The bytes are fine — your map was wrong.

The golden rule: decode bytes to str as early as possible, be explicit about which encoding you used, and never guess silently. "It opened without an error" is not the same as "it decoded correctly" — and a successful decode is not proof you chose the right encoding. Our UTF-8-then-cp1252 strategy is a practical heuristic for this controlled exercise: it handles the two cases you'll see most often, but it is not universal encoding detection. When the heuristic fails, reach for a real detector (charset-normalizer, in Useful Later below) instead of adding a third guess.

Fix it together: an intake script

You're going to build intake.py — a small script with one job: take whatever the client sent and produce a trustworthy record of it. For each file it reports the encoding, the CSV dialect, how many rows survived, how many didn't, and a fingerprint proving what you received. Bad rows don't vanish — they get quarantined with a reason.

import csv
import hashlib
import io
import json
import os
import tempfile
import zipfile
from collections import Counter
from pathlib import Path

INBOX = Path("inbox_november")
QUARANTINE = Path("quarantine")
QUARANTINE.mkdir(exist_ok=True)

EXPECTED_COLUMNS = {"borough", "complaint_type", "created_date"}

def file_hash(path):
    """SHA-256 of a file, read in chunks so big files don't eat memory."""
    h = hashlib.sha256()
    with open(path, "rb") as f:
        for chunk in iter(lambda: f.read(65536), b""):
            h.update(chunk)
    return h.hexdigest()

def read_text_safely(path):
    """Decode bytes to str with a two-guess heuristic: UTF-8 (BOM-aware), then cp1252.

    A successful decode is NOT proof the encoding is right — cp1252 accepts
    almost any byte sequence, so it can decode the wrong map without complaint.
    This heuristic handles the two cases you'll see most often; it is not
    universal encoding detection. Returns (text, encoding_name)."""
    raw = Path(path).read_bytes()
    for encoding in ("utf-8-sig", "cp1252"):
        try:
            return raw.decode(encoding), encoding
        except UnicodeDecodeError:
            continue
    raise ValueError(f"could not decode {path} as utf-8 or cp1252 — inspect it manually")

def detect_dialect(text):
    """Guess the CSV dialect from the head of the file.

    The sniffer gets confused by ragged rows, so we start with the header
    plus four data lines and show it less if it can't decide — the header
    alone is usually the cleanest line in the file. Five lines is a pragmatic
    heuristic for this intake, not a universal rule: a pathological file can
    still fool the sniffer, which is why the manifest reports the detected
    delimiter for a human to sanity-check. Falls back to plain commas when
    unsure."""
    lines = text.splitlines()
    for n in (5, 3, 2):
        try:
            return csv.Sniffer().sniff("\n".join(lines[:n]), delimiters=";,\t|")
        except csv.Error:
            continue
    return csv.excel

def load_json_flexible(path):
    """Load JSON, falling back to JSON-lines (one object per line)."""
    text, encoding = read_text_safely(path)
    try:
        return json.loads(text), f"json ({encoding})"
    except json.JSONDecodeError:
        rows = [json.loads(line) for line in text.splitlines() if line.strip()]
        return rows, f"json-lines ({encoding})"

A few things worth noticing. utf-8-sig is UTF-8 that silently strips a BOM if one is present — harmless when there isn't one. The cp1252 fallback handles the 2007-Excel case, but notice it reports which encoding it used instead of hiding the guess. detect_dialect starts the sniffer on the first five lines and shows it less if it gives up: ragged rows confuse it, and the header is usually the cleanest line in the file. Five lines is a pragmatic heuristic for this intake, not a universal rule — a pathological file can still fool the sniffer, which is why the manifest reports the detected delimiter for a human to sanity-check. And load_json_flexible handles the file that calls itself .json but is really one object per line — one of the most common "broken JSON" shapes in the wild.

Now the CSV intake — the part that earns its keep:

def intake_csv(path):
    """Read one CSV defensively. Returns (good_rows, bad_rows, notes)."""
    text, encoding = read_text_safely(path)
    dialect = detect_dialect(text)
    reader = csv.DictReader(io.StringIO(text, newline=""), dialect=dialect)
    notes = [f"encoding={encoding}", f"delimiter={dialect.delimiter!r}"]
    if reader.fieldnames is None:
        return [], [], notes + ["empty file — no header row"]
    missing = EXPECTED_COLUMNS - {c.strip().lower() for c in reader.fieldnames}
    if missing:
        notes.append(f"missing expected columns: {sorted(missing)}")
    good, bad = [], []
    for row in reader:
        # shape check first: DictReader parks surplus fields under a None key
        reason = f"extra fields: {row[None]}" if None in row else None
        # normalize keys once, at the boundary: strip whitespace, lowercase
        clean = {(k or "").strip().lower(): (v or "").strip()
                 for k, v in row.items() if k is not None}
        if reason is None and not clean.get("borough"):
            reason = "missing borough"
        elif reason is None and not clean.get("complaint_type"):
            reason = "missing complaint_type"
        if reason is None:
            good.append(clean)
        else:
            bad.append((reason, row))
    if bad:
        qpath = QUARANTINE / (path.stem + ".bad.csv")
        with open(qpath, "w", encoding="utf-8", newline="") as f:
            w = csv.DictWriter(f, fieldnames=["_quarantine_reason"] + reader.fieldnames)
            w.writeheader()
            for reason, row in bad:
                w.writerow({"_quarantine_reason": reason,
                            **{k: v for k, v in row.items() if k is not None}})
        notes.append(f"{len(bad)} rows quarantined → {qpath}")
    return good, bad, notes

The quarantined rows go to a .bad.csv file, each tagged with a _quarantine_reason — what was wrong with it, in plain words ("missing borough", "extra fields: [...]"). Not deleted, not silently "fixed": set aside with its reason. Payload plus reason plus the manifest's record of where it came from — that triple is the seed of the dead-letter queue you'll build properly in Milestone 2.

Finally, the driver: expand the zip safely, process every file, and write a manifest.

def expand_archives():
    for zpath in list(INBOX.glob("*.zip")):
        with zipfile.ZipFile(zpath) as z:
            for member in z.namelist():
                # zip-slip guard: refuse entries that would escape the inbox
                target = (INBOX / member).resolve()
                if INBOX.resolve() not in target.parents:
                    raise ValueError(f"unsafe path in {zpath}: {member}")
            z.extractall(INBOX)

def atomic_write(path, text):
    """Write a file so readers never see a half-written version."""
    path = Path(path)
    fd, tmp_name = tempfile.mkstemp(dir=path.parent, suffix=".tmp")
    try:
        with os.fdopen(fd, "w", encoding="utf-8") as f:
            f.write(text)
        os.replace(tmp_name, path)  # atomic on Windows and POSIX
    except BaseException:
        Path(tmp_name).unlink(missing_ok=True)
        raise
        
def main():
    expand_archives()
    lines = []
    total_good, total_bad = 0, 0
    for path in sorted(INBOX.iterdir()):
        if path.is_dir() or path.suffix.lower() == ".zip":
            continue
        digest = file_hash(path)[:12]
        if path.suffix.lower() == ".csv":
            good, bad, notes = intake_csv(path)
            total_good += len(good)
            total_bad += len(bad)
            lines.append(f"{path.name} | sha256:{digest} | "
                         f"{len(good)} good, {len(bad)} quarantined | {'; '.join(notes)}")
        elif path.suffix.lower() == ".json":
            data, fmt = load_json_flexible(path)
            n = len(data) if isinstance(data, list) else 1
            lines.append(f"{path.name} | sha256:{digest} | format={fmt} | {n} records")
        else:
            lines.append(f"{path.name} | sha256:{digest} | SKIPPED — out of scope for this intake")
    atomic_write("manifest.txt", "\n".join(lines) + "\n")
    print("\n".join(lines))
    print(f"\nManifest written to manifest.txt")
    print(f"November intake: {total_good:,} rows accepted, {total_bad:,} quarantined.")

if __name__ == "__main__":
    main()

Run it on Tom's zip:

$ python intake.py
NOV_Export.CSV | sha256:9f2c41aa77d1 | 18204 good, 312 quarantined | encoding=cp1252; delimiter=';'; missing expected columns: ['created_date']
notes.json | sha256:77b1c902aa4f | format=json-lines (utf-8-sig) | 312 records
routes_2007.xls | sha256:e5d08b11c3a2 | SKIPPED — out of scope for this intake

Manifest written to manifest.txt
November intake: 18,204 rows accepted, 312 quarantined.

Read that output like a stakeholder would. The script is telling you a story: the CSV was a cp1252 semicolon file missing the date column, the "JSON" was JSON-lines with a BOM, and the spreadsheet was deliberately left alone. Every one of those facts is something you'd otherwise discover at 11 PM. The manifest is the receipt — what you received, what you did with it, and what's still unresolved.

Break it: the file-horror field guide

You will meet every one of these. Learn to recognize the symptoms, because the error message never says "your encoding is wrong."

Horror	What it looks like	The fix
BOM in the header	KeyError: 'borough' even though the header looks right	print(repr(row.keys())) — you'll see '\ufeffborough'. Decode with utf-8-sig.
Mojibake	"café", stray "Â" characters	Wrong map, not broken data. Re-decode the raw bytes with the right encoding (usually cp1252).
Wrong delimiter	One giant column; every row has a single key	Sniff the dialect (csv.Sniffer) instead of assuming commas.
Ragged rows	Rows with too many or too few fields — often a stray quote character	Validate the shape of every row; quarantine the failures.
Truncated file	The last rows are simply missing; no error at all	Compare your row count against the source's count — this is why the manifest exists.
JSON-lines wearing a .json name	json.load dies on line 2	Fall back to parsing one object per line.
Excel dates	45231.0 where a date should be — days since 1899-12-30	Convert from the Excel epoch, and sanity-check against a date you know.
\r\n line endings	Stray \r characters or phantom blank rows	When you open files for csv directly, always pass newline="" and let the csv module handle endings.

The Excel problem (deliberately short)

.xlsx and legacy .xls aren't even the same format, dates can arrive as floats, and a "number" column can hold three text values and a smiley. You'll handle spreadsheets properly in Post 4. For today's engagement, routes_2007.xls is explicitly outside scope — so don't solve a problem the customer didn't ask you to solve.

Production-safe: treat every file like evidence

The intake script already follows four rules. Make them habits, because Milestone 2's pipeline is built out of them:

Never modify the original. The inbox is read-only. Your script reads from it and writes outputs elsewhere. If anyone questions your numbers next week, the original file is untouched and its hash matches the manifest.

Hash everything you receive. A SHA-256 takes milliseconds and buys you three things: proof of exactly what you were given, detection when someone silently re-sends "the same file," and the ability to reconcile counts downstream. When Tom says "I sent you the updated export," the hash tells you whether anything actually changed.

Quarantine, don't delete, don't silently fix. A quarantined row is a fact ("312 rows lacked a borough"). A silently fixed row is a guess wearing a fact's clothes. Guesses compound — by Milestone 2, an auto-"fixed" borough becomes a wrong number in Maria's dashboard that nobody can trace.

Know the limit. This first version reads the complete file into memory — read_bytes() loads it all, and the good/bad lists then hold every parsed row as Python dicts. That's a deliberate tradeoff: it keeps the encoding logic easy to follow, and it's fine for Tom's 18,000-row export. It is not fine for a 4GB client dump, which would balloon well past 4GB once represented as strings and dicts. The fix, when you need it, is streaming: read, decode, and process incrementally instead of loading everything first. Notice file_hash() already works that way — it reads in 64KB chunks and never holds more than one chunk. Same idea, applied to parsing, is how you'll handle the big files later.

Write atomically. atomic_write writes to a temp file and renames it into place, so a crash mid-write never leaves a half-written manifest behind. Cheap insurance; you'll use it for every artifact a pipeline produces.

Explain it to the customer

Two paragraphs. Tom gets what he needs for the meeting; Maria gets the caveats before she has to ask:

"Tom — the November intake is done: 18,204 rows accepted into the borough summary, same format as October. Two things to know before the meeting: 312 rows (1.7%) were missing a borough value, so they're quarantined in a separate file rather than counted — the manifest lists exactly which ones. Also, the November export doesn't include a created-date column, so I couldn't do any time-based filtering; everything below is the full month as sent."

"Maria — the manifest is attached: it records every file received, its fingerprint, and what was accepted or quarantined. Nothing from the originals was modified. The 2007 routes spreadsheet was left out of this intake per Tom's scope call — flagging it now so it doesn't surprise anyone later."

Notice the pattern from Post 1, repeated deliberately: disclose the caveat before discovery, state what you did, offer the next step. And the scope decision about the spreadsheet is in writing — in an email, not in your head.

Where this lands in CityOps

intake.py is the front door of Milestone 2. Right now it's a script you run by hand; by the milestone it becomes the first stage of a scheduled pipeline — and every habit in it graduates with it: the manifest becomes the audit log, the quarantine folder becomes the dead-letter queue, the hashes become the reconciliation report's "every record accounted for." The data contract you'll write in Post 3 slots in right where EXPECTED_COLUMNS sits today.

Keep the manifest from every intake you run. Future you, debugging a disputed number three months from now, will want to know exactly what arrived and when.

Post 1's principle was don't code before you understand the question. Post 2's: don't trust data just because it parsed.