Data Pipelines & Export 5 min 2026-11-22

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.

Implementation Checklist
  1. 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.
  2. Protect leading-zero cells with ="02108": Wrap postal codes matching /^0/ as ="02108" inside RFC 4180 quotes so Excel preserves exact string digits.
  3. 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.

Further Reading

More QA Engineering Guides

CI & E2E Automation

Deterministic Address Fixtures in Playwright & Cypress CI Pipelines

Why unseeded random address libraries break CI snapshots and how to wire deterministic AddressQA fixtures into Playwright test.use() hooks.

Payments & Tax QA

Testing Stripe AVS & US Sales Tax Calculation with Tax-Free ZIP Codes

How to structure Stripe shipping and billing_details objects to assert 0% state tax, local borough overrides, and leading-zero AVS behavior.

Telecom Compliance

Regulator-Safe Test Phone Numbers: NANPA 555, Ofcom, ACMA & E.164 Reference

Why random phone numbers risk contacting real subscribers — and the exact regulator-reserved blocks AddressQA uses in Safe mode.