csv

Quoting Rules

• Fields containing comma, quote, or newline MUST be wrapped in double quotes
• Double quotes inside quoted fields escape as "" (two quotes), not backslash
• Unquoted fields with leading/trailing spaces—some parsers trim, some don't; quote to preserve
• Empty field ,, vs empty string ,"",—semantically different; be explicit

Delimiters

• CSV isn't always comma—detect ; (European Excel), \t (TSV), | in legacy systems
• Excel exports use system locale delimiter; semicolon common in non-US regions
• Sniff delimiter from first line but verify—header might not contain special chars

Encoding

• UTF-8 BOM (0xEF 0xBB 0xBF) breaks naive parsers but Excel needs it for UTF-8 detection
• When generating for Excel on Windows: add BOM; for programmatic use: omit BOM
• Latin-1 vs UTF-8 ambiguity—explicitly declare or detect encoding before parsing

Common Parsing Failures

• Newlines inside quoted fields are valid—don't split on \n before parsing
• Unescaped quote in middle of field corrupts rest of file—validate early
• Trailing newline at EOF—some parsers create empty last row; strip or handle
• Inconsistent column count per row—validate all rows match header count

Numbers & Dates

• 1,234.56 vs 1.234,56—locale-dependent; standardize or document format
• Dates: ISO 8601 (2024-01-15) only unambiguous format; 01/02/24 is chaos
• Leading zeros in numeric fields (007)—quote to preserve or document as string

Excel Quirks

• Formula injection: fields starting with =, +, -, @ execute as formulas—prefix with ' or tab
• Long numbers (>15 digits) lose precision—quote and format as text
• Scientific notation triggered by E in numbers—quote if literal text needed