CSV: a format with more edge cases than commas

What it is

RFC 4180 defines records separated by CRLF, fields separated by commas, optional double-quote enclosure, and doubled quotes inside quoted fields. It is informational; spreadsheets and exports deviate: semicolons as delimiters in locales where the comma is the decimal mark, single quotes, no quoting at all, UTF-8 with or without a byte-order mark, and embedded line breaks inside quoted fields.

Why it matters

Splitting lines on commas silently corrupts data when a field contains a comma or a newline. Encoding mistakes turn names into mojibake. Numbers formatted for humans (thousands separators, decimal commas) are not numbers.

How to apply

• Parse with the language's CSV library (csv.DictReader in Python), never with split(",").
• Declare the dialect explicitly (delimiter, quoting) and detect it only as a fallback with a sniffer.
• Open files with an explicit encoding (utf-8-sig tolerates a BOM) and newline="" as the Python documentation requires.
• Validate the header row against the expected column names; fail loudly on missing columns.
• Convert numbers and dates with locale-aware parsing after reading, not by trusting the text.

Pitfalls

Leading zeros (postal codes, identifiers) are lost when spreadsheets "help". Formulas starting with = in exported cells can execute when opened in a spreadsheet; escape them on export. Large files should be streamed row by row.