Your public CSV looks fine until Excel opens it — charset, BOM, and timezone traps

Public extracts usually fail in pagination or scope. A quieter killer: the file is “correct” in Python and wrong the moment a buyer opens it.

Before you hand off a public-source CSV, run these five checks:

1. UTF-8 with an explicit BOM when Excel is in the loop — macOS Numbers / pandas often hide the problem; Windows Excel silently mangling café / São / curly quotes is classic. Prefer utf-8-sig for buyer-facing CSVs, and say so in schema.md.
2. No smart punctuation from HTML — scrape text often includes NBSP (\u00a0), zero-width chars, and fancy dashes. Normalize whitespace and replace NBSP → space before write.
3. Timezone is a column, not a vibe — timestamps without an offset become “wrong day” after a buyer’s spreadsheet localizes them. Store ISO-8601 with Z or an explicit offset (e.g. 2026-09-30T15:33:00-07:00), or split into date_utc + time_utc.
4. Leading zeros survive as text — ZIP codes, FIPS, and external ids. Prefix with a tab or force text quoting so Excel does not turn 02139 into 2139.
5. Round-trip sample — reopen the shipped file in Excel and LibreOffice once. Spot-check 10 rows for mojibake, shifted dates, and truncated ids. If either client lies, fix the export — not the buyer.

Prefer official JSON/API when it exists; flatten once; document encoding + timezone policy in schema.md next to null rates.

Update (Oct 2026): the OpsPacket Public Data Pull service mentioned in the original version of this post is retired and no longer for sale. The checklist above still stands on its own.