CSV Edge Cases That Break Imports (and How to Handle Them)

CSV looks like the simplest format there is: values, commas, line breaks. Most CSV bugs come from that assumption. There is a written standard, RFC 4180, but it is informational, and plenty of software ignores parts of it. Below are the cases that cause the most real damage, what they look like, and how to deal with each one.

1. Line breaks inside a field

RFC 4180 allows a field to contain line breaks as long as the field is wrapped in double quotes:

id,name,address
1,Ada,"12 High Street
London"
2,Grace,"5 Elm Road"

That is two records, not three. Anything that splits the file on newlines first (split('\n'), wc -l, head, many quick scripts) will count three lines and break the first record in half.

Fix: use a real CSV parser that tracks whether it is inside quotes. In Python, open the file with newline='' as the csv module docs require, otherwise embedded newlines are mangled:

import csv
with open("export.csv", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        ...

2. Quotes inside a field

A double quote inside a quoted field is written as two double quotes:

id,comment
1,"She said ""ship it"" and left"

The value is She said "ship it" and left. Hand-written parsers often treat the second quote as the end of the field. If you are writing CSV yourself, let the library quote for you rather than building lines with string concatenation.

3. The byte order mark

Some programs, including Excel when you choose "CSV UTF-8", put three bytes (EF BB BF) at the start of the file. This is the UTF-8 byte order mark (BOM). Most viewers hide it, but code does not: your first column header becomes id instead of id, and row["id"] fails with a key error even though the header looks right.

Fix: in Python, read with encoding="utf-8-sig", which strips the BOM if it is there and does nothing if it is not. In pandas, pd.read_csv(path, encoding="utf-8-sig").

The reverse problem also exists: Excel on Windows opens a UTF-8 file without a BOM using the system's legacy code page, so café shows up as café. If people will double-click your export to open it in Excel, writing the BOM is the pragmatic choice.

4. Not every "comma" separated file uses commas

In locales that use a comma as the decimal separator (much of Europe and South America), Excel saves "CSV" with semicolons: 1;Berlin;3,5. Tab and pipe separated files are also common from databases.

Fix: detect the delimiter instead of assuming it. Python has csv.Sniffer().sniff(sample). A simpler approach, which is what the Fix42 CSV Viewer does, is to count commas, semicolons, tabs and pipes in the header row and pick the most frequent. That works for most files but can be fooled when the header has quoted names containing commas, so it is worth checking the column count on the first few rows.

5. Excel changes your data on open

Double-clicking a CSV in Excel runs automatic type conversion, and some of it is lossy:

• Leading zeros vanish. ZIP code 02134 becomes 2134, product code 007 becomes 7.
• Long numbers lose digits. Excel stores 15 significant digits, so a 16 digit card or order number like 1234567890123456 becomes 1234567890123450, and is often displayed as 1.23457E+15.
• Things that look like dates become dates. 3-4 becomes 3 April (or 4 March, depending on locale). This was common enough in genetics data that in 2020 the HUGO Gene Nomenclature Committee renamed genes such as SEPT1 and MARCH1 (now SEPTIN1 and MARCHF1) so Excel would stop turning them into dates.

If the file is then saved, the changed values are written back. Fix: import instead of opening: Data > From Text/CSV, then set those columns to Text in Power Query before loading. In pandas, read identifier columns as strings: pd.read_csv(path, dtype={"zip": str, "order_id": str}).

6. Row limits

An Excel worksheet holds 1,048,576 rows. Open a bigger CSV and Excel warns that the file was not loaded completely, then drops the rest. Save it and those rows are gone. Google Sheets has a 10 million cell limit per spreadsheet, which hits even sooner on wide files. There is a separate post on opening CSV files that are too big for Excel.

7. Formula injection

If a cell starts with =, +, - or @, spreadsheet programs may treat it as a formula. A user who types =HYPERLINK("http://example.com","Click") into a signup form ends up as a live link in whatever CSV export your team opens later. OWASP describes this as CSV injection.

Fix: when you generate CSV from user input that will be opened in a spreadsheet, prefix cells that start with those characters with a single quote, or escape them as OWASP recommends.

8. Trailing junk and ragged rows

Exports from older systems often end with a summary line ("Total rows: 5000") or have rows with fewer fields than the header. A strict parser throws; a lenient one silently fills blanks or shifts values into the wrong columns.

Fix: check that every row has the same number of fields as the header, and log the line numbers that do not, rather than guessing.

A short checklist before you import a CSV

1. Open the first few hundred bytes in a text editor, not a spreadsheet. Look for a BOM, the delimiter, and quote style.
2. Count records with a parser, not with wc -l.
3. Decide which columns are identifiers and force them to text.
4. Check field counts per row.
5. Compare the row count after import with the row count from the source system.

How the Fix42 CSV Viewer handles these

For files under 500MB, the viewer parses the file in your browser. It strips a UTF-8 BOM, keeps quoted line breaks inside a single row, handles doubled quotes, accepts Windows, Unix and old Mac line endings, and guesses the delimiter from the header row as described above. It shows values as text, so leading zeros and long numbers are displayed exactly as they are in the file.

Known limits: it reads files as UTF-8, so files saved in a legacy encoding will show garbled accented characters; it trims spaces at the start and end of each field; and for browser-parsed files, search and sort apply to the page you are viewing. Files of 500MB and over are uploaded to the server, where "Search All" covers the whole file, and are deleted after 24 hours.