Skip to content

CSV pitfalls: delimiters, quoting, encodings and Excel

By · File & Data Conversion · 6 min read · Updated

CSV looks like the simplest data format there is: values separated by commas, one record per line. It is supported by every spreadsheet, database and programming language, which is why it remains the default for exports and imports. But CSV has no single enforced standard, and the gaps are filled differently by every tool. This guide covers the problems that break real CSV imports and exports, and how to produce and read files that survive the journey between systems.

What the standard says

RFC 4180, published in 2005, documents the common format:

  • Records are separated by line breaks (CRLF in the RFC, though LF is common).
  • Fields are separated by commas.
  • A field containing 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.
  • An optional header row has the same format as data rows.
id,name,notes
1,"Smith, Jane","Said ""call after 5"""
2,Ada Lovelace,"Line one
line two"

That small example already defeats most hand-written parsers: the second record has a comma inside a field, escaped quotes, and the third record spans two lines.

Pitfall 1: Splitting on commas

The most common CSV bug in code is line.split(","). It breaks on any quoted field containing a comma, and reading line by line breaks on any field containing a newline, such as an address or a comment. Always use a real CSV library: Python's csv module, Apache Commons CSV or OpenCSV in Java, encoding/csv in Go, or Papa Parse in JavaScript. They handle quoting, escaped quotes and multi-line fields correctly.

Pitfall 2: The delimiter is not always a comma

In many European locales the comma is the decimal separator, so Excel and other tools in those locales export with semicolons. Database exports often use tabs (TSV) or pipes. A semicolon-separated file opened as comma-separated shows each row as one long column. When accepting files from users, detect the delimiter by checking which candidate produces a consistent number of columns across the first rows, and let users override the guess. The CSV Viewer detects the delimiter automatically and lets you choose it manually.

Pitfall 3: Character encoding

CSV files carry no information about their encoding. A file saved as Windows-1252 or ISO-8859-1 and read as UTF-8 turns é into é or into replacement characters. The reverse also happens: older Excel versions open UTF-8 CSV files as the system's legacy encoding unless the file starts with a byte order mark.

Produce UTF-8, and if your users open files in Excel, add a UTF-8 BOM (bytes EF BB BF) at the start. Consume by accepting and stripping a BOM, defaulting to UTF-8, and falling back to Windows-1252 if decoding fails.

Pitfall 4: The invisible BOM in your header

If your code reads a file with a BOM as plain text, the first header becomes "\uFEFFid" instead of "id". Looking up the id column fails even though the header looks correct when printed. Many libraries have an option to handle this, such as Python's encoding="utf-8-sig".

Pitfall 5: Excel changes your data

Opening a CSV in Excel and saving it again is one of the most common ways data gets corrupted:

  • Leading zeros vanish: postal codes like 01234 and product codes like 007 become numbers.
  • Long numbers lose precision: IDs and card-like numbers longer than 15 digits are rounded and displayed in scientific notation, such as 1.23457E+15.
  • Text becomes dates: values such as 1-2, MARCH1 or SEPT2 are converted to dates. This was common enough in genetics that several human genes were officially renamed to avoid it.
  • Dates change format according to the user's locale when saved.

To view a CSV without modifying it, use a viewer that does not interpret values, or import it in Excel through the data import wizard with columns set to Text. Never round-trip production data through a spreadsheet without checking it afterwards.

Pitfall 6: CSV injection

A field that starts with =, +, - or @ may be executed as a formula when the file is opened in a spreadsheet. If your application exports user-supplied text, an attacker can plant a formula that runs when an administrator opens the export. Mitigate by prefixing such values with a single quote or a tab when generating CSV meant for spreadsheets, and by warning users about untrusted files.

Pitfall 7: Inconsistent rows and types

Real-world files contain rows with too few or too many fields, trailing empty lines, a summary row at the bottom, or a few header lines of metadata above the actual header. Values in a numeric column may contain thousands separators, currency symbols or the text N/A. Validate every row against the expected column count and types, collect errors with row numbers, and report them all rather than failing at the first one.

Pitfall 8: Line endings

Files may use CRLF (Windows), LF (Unix) or, rarely, CR (old Mac). A parser that only expects LF leaves a stray \r at the end of the last field of every row, which breaks comparisons such as status == "active". Good libraries handle all three; if you must process lines yourself, normalise endings first.

Producing CSV that works everywhere

  1. Use UTF-8, with a BOM if the main audience opens files in Excel.
  2. Use commas and quote every field that contains a comma, quote or line break; quoting all text fields is also fine.
  3. Include exactly one header row with simple, unique, ASCII column names.
  4. Write dates in ISO 8601 (2026-10-09) and numbers without thousands separators, with a dot as the decimal separator.
  5. Document the format: delimiter, encoding, column meanings and units.
  6. Generate files with a CSV library, never with string concatenation.

If you cannot use a library, the quoting rules are small enough to get right in a few lines. This JavaScript version also adds the Excel BOM, uses CRLF as RFC 4180 specifies, and neutralises formula injection in text values:

function csvField(value) {
  let s = value == null ? '' : String(value);
  if (typeof value === 'string' && /^[=+\-@\t\r]/.test(s)) s = "'" + s;  // formula guard, text only
  return /[",\r\n]/.test(s) || s !== s.trim() ? `"${s.replace(/"/g, '""')}"` : s;
}
const toCsv = (rows) =>
  '\uFEFF' + rows.map((r) => r.map(csvField).join(',')).join('\r\n') + '\r\n';

toCsv([['id', 'name', 'note'], [1, 'Smith, Jane', 'said "hi"'], [2, '=HYPERLINK("http://x")', 'a\nb']]);
// id,name,note
// 1,"Smith, Jane","said ""hi"""
// 2,"'=HYPERLINK(""http://x"")","a
// b"

The guard is applied to strings only, so real numbers such as -5 stay numeric. If a text column legitimately holds values like -5 or +44 20…, the leading quote will show in the spreadsheet; that is the price of safety for files opened by people who did not create them.

Checking a file before importing it

Before running an import, open the file in a viewer that shows it as a table without changing anything. Check that the delimiter was detected correctly, the column count is consistent, special characters display properly and suspicious values such as scientific notation or dates in code columns are absent. To see exactly what changed between two exports, compare them line by line in the Text Diff. A few minutes of inspection is far cheaper than cleaning up a bad import in a production database.