Canada Address CSV Export

Getting Canadian addresses into a spreadsheet without the postal codes being silently destroyed on the way.

The problem with Canadian postal codes in CSV

A postal code is a string that looks like it might be something else. Spreadsheet applications guess at column types when they open a CSV, and a column of values like M5V 3L9 is exactly the kind of thing a guesser gets wrong.

The failure is quiet. You export, someone opens the file, the postal codes change, and nothing anywhere reports an error. That is worse than a crash.

The fix: quote the column as text

Wrapping the value in double quotes tells a conforming reader the field is text, not a value to interpret. The export here does this for the postal code column specifically, whether or not quoting is structurally required.

Name,Address Line 1,City,Province,Postal Code,Country
Priya Sandhu,100-1234 Yonge St W,Toronto,ON,"M5V 3L9",Canada
"Sandhu, Priya",5402 Aspen Dr,Calgary,AB,"T2P 1J9",Canada

RFC 4180, and the two rules that matter

The CSV format has a specification, and most hand-rolled exporters implement about half of it. Two rules cause almost all real breakage.

A field containing the delimiter, a double quote, a carriage return or a line feed must be quoted. Miss this and one name containing a comma shifts every later column in that row.
A double quote inside a quoted field is escaped by doubling it, not with a backslash. Backslash escaping is a JSON habit and produces a file that conforming readers parse incorrectly.

Formula injection, which is a security issue rather than a formatting one

A field beginning with =, +, - or @ is interpreted by spreadsheet applications as the start of a formula. If any of your data is user-supplied, an exported CSV becomes a delivery mechanism for it.

The export here prefixes any such field with an apostrophe. This matters for synthetic data too, because the file is opened by the same people who generated it, and a habit of not escaping carries over to exports that do contain user input.

Opening the file without breaking it

Import rather than double-click. Most spreadsheet applications offer a text-import path that lets you set a column type explicitly.
Set the postal code column to Text before the import completes, not after — a value already coerced cannot be recovered by changing the column type afterwards.
If you are scripting it, read the column as a string. Pandas needs dtype=str, or it will infer.

import pandas as pd

# Without dtype=str, postal codes and anything else numeric-looking get inferred.
df = pd.read_csv('canada-addresses.csv', dtype=str)

When not to use CSV

CSV has no types, no nesting and no encoding declaration. If you are moving records between systems rather than into a spreadsheet, JSON or NDJSON is a better fit — and the API returns both.

Use CSV when a person is going to open it. Use JSON when a program is.

Questions

Why is the postal code column quoted when it contains no commas?

Because quoting is what marks it as text. Without it, a spreadsheet may reinterpret the value on open, and the file you exported is not the data the reader sees.

My postal codes changed when I opened the file. Can I recover them?

Not from that spreadsheet — the original values are gone once coerced. Re-export and import with the column set to Text before the import completes.

Does the export handle names containing commas and quotes?

Yes. Fields containing the delimiter, a quote or a line break are quoted, and inner quotes are doubled per RFC 4180. There is a test for exactly that case.

Should I use CSV or JSON?

CSV if a person will open it in a spreadsheet. JSON or NDJSON if a program will read it — those carry types and nesting, and have no ambiguity about how a value should be interpreted.

Everything this site generates is synthetic, produced for development, QA, testing, demos and staging. The records do not represent real people, and the addresses are not verified deliverable addresses.