CSV Data Handling: Common Problems and Solutions

CSV Looks Simple But Isn't

CSV (Comma-Separated Values) is the most common data interchange format, yet it causes endless headaches. There's no formal universal standard — different applications handle quoting, escaping, encoding, and line endings differently. Understanding these differences prevents data corruption and parsing errors.

Character Encoding Issues

The most common CSV problem is character encoding mismatch. A file saved as Windows-1252 opened as UTF-8 turns é into é. Excel defaults to the system's local encoding, not UTF-8. Always specify encoding when reading CSV files. Use UTF-8 with BOM (Byte Order Mark) when creating CSVs for Excel compatibility. When receiving CSVs, try UTF-8 first, then fall back to latin-1 (which never fails but may show wrong characters).

Delimiter Conflicts

When data contains commas, fields must be quoted. But what if data contains both commas and quotes? The standard approach is to double the quotes inside quoted fields. Some applications use semicolons as delimiters (common in European locales where commas are decimal separators). Tab-separated files (TSV) avoid most delimiter conflicts since tabs rarely appear in data.

Data Type Ambiguity

CSV is text-only — there's no way to specify whether 01234 is a number (1234) or a text string (01234, like a ZIP code). Excel aggressively converts data: leading zeros are stripped, dates are reformatted, long numbers become scientific notation, and gene names like MARCH1 become dates. To prevent this, import CSVs using the text import wizard with explicit column type specifications.

Line Ending Issues

Windows uses CRLF (\r\n), Unix/Mac uses LF (\n), old Mac used CR (\r). Mixing line endings in a single file causes parsing failures. Modern CSV parsers handle mixed line endings, but simple line-by-line readers may break. When generating CSVs, use a library rather than string concatenation to handle all edge cases correctly.

Best Practices

Always use a CSV library for reading and writing — never split on commas manually. Specify encoding explicitly (UTF-8). Include a header row. Quote all fields containing special characters. Test with edge cases: empty fields, fields with newlines, fields with the delimiter character, very long fields, and Unicode characters.