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