Preventing Excel CSV Postal Code Corruption (02108 → 2108)
How Excel auto-type inference destroys New England, German, Korean, and Singaporean postal codes — and how AddressQA CSV (Excel-Safe) protects every row.
- Emit UTF-8 BOM (\uFEFF): Prefix CSV exports with \uFEFF so Excel on Windows and macOS decodes UTF-8 Japanese, Chinese, German, and French characters without mojibake.
- Protect leading-zero cells with ="02108": Wrap postal codes matching /^0/ as ="02108" inside RFC 4180 quotes so Excel preserves exact string digits.
- Enforce string schema in ETL & SQL: Import CSV columns explicitly as TEXT / VARCHAR(16) rather than numeric auto-detection.
The Silent Spreadsheet Bug in QA & Fulfillment Workflows
Double-clicking a plain CSV file in Microsoft Excel triggers aggressive type inference: Massachusetts 02108 becomes integer 2108, New Hampshire 03101 becomes 3101, Rome 00187 becomes 187, and some hyphenated codes turn into calendar dates. Re-saving that file permanently corrupts the test fixture.
RFC 4180 + UTF-8 BOM + Leading-Zero Formula Escaping
AddressQA’s CSV (Excel-Safe) tab automatically prepends a UTF-8 BOM (\uFEFF), uses CRLF line endings per RFC 4180, escapes embedded quotes/commas, and wraps any leading-zero postal code using Excel’s string formula guard:
fixture_id,seed,full_name,phone_e164,city,state,postal_code,country
qa-nh-01-1-US-NH,qa-nh-01,Claire Mercer,+16035550142,Manchester,NH,"=""03101""",US Automated Ingestion Tip for Python / Node.js Scripts
When consuming CSV directly in pandas or Node.js (rather than opening in Excel), pass dtype={"postal_code": str} or fetch the clean JSON endpoint GET /api/v1/fixtures?format=json so you never have to strip Excel formula guards.