The 12 most common CSV problems (and how to fix each)

The C in CSV promises a comma, but half of Europe exports semicolons. The reason is arithmetic, not taste: in locales where the decimal separator is a comma, 1,5 is a number, so the comma cannot also separate fields. Excel writes whatever the Windows regional setting calls the list separator, which is why the same spreadsheet saved in Berlin and in Boston produces two different files.

A parser that guesses wrong gives you a single column containing the entire line. That is the easiest failure to spot and the easiest to fix: change the delimiter and re-read the file. Tab-separated files (.tsv) and pipe-separated exports behave identically — only the character differs.

Then there is Microsoft's private extension: a first line reading sep=; which tells Excel the delimiter explicitly. It is not part of RFC 4180, so every other tool reads it as a one-cell data row and shifts your header down. Strip that line before handing the file to anything except Excel — CSV Polish detects it, honours it and drops it from the table.

RFC 4180 says a field must be wrapped in double quotes if it contains the delimiter, a double quote or a line break, and that an inner quote is written twice: "She said ""yes""". Hand-built exports — a PHP script gluing strings with commas, a report writer that forgot the rule — break this constantly. The symptom is a row that suddenly has more columns than the header, with an address split across two cells.

The opposite failure is an unbalanced quote. One stray " opens a quoted field that never closes, and the parser swallows the rest of the file into a single giant cell. If a file looks like it has three rows when it should have three thousand, search the raw text for an odd number of quote characters — the last valid row before the damage tells you where to look.

Line breaks inside quoted cells are legal and common (multi-line addresses, notes fields). They are also why you cannot split a CSV file by newline with a text editor and expect rows. Use a real parser; CSV Polish uses PapaParse, which handles quoted newlines correctly and counts every warning it raises so you can check the Raw view.

A ragged row has more or fewer cells than the header. Short rows usually come from a trailing field that was empty and simply omitted; long rows come from the quoting bug above or from an export that appended a column halfway through the job. Neither is recoverable by guessing — but padding short rows and widening the table for long ones keeps the file loadable while you inspect the damage.

A byte order mark is the three bytes EF BB BF at the very start of a UTF-8 file. It carries no text; it is a flag that says “the following bytes are UTF-8”. Excel needs it to open a UTF-8 CSV correctly by double-click. Parsers that do not know about it hand you a first header named “\ufeffid” instead of “id”, which then fails every column lookup downstream.

Within one column you will find 2024-03-01, 01.03.2024, 3/1/2024 and “March 1, 2024”, because the rows were entered by different people or merged from different systems. The dangerous one is the slash form: 03/07/2024 is 3 July in London and 7 March in Chicago, and nothing in the file says which. The only honest fix is to look at the rest of the column — if any value has a day above 12, the order is settled for every row.

Normalise to ISO 8601 (yyyy-mm-dd) unless a downstream system demands otherwise. It sorts correctly as text, it is unambiguous in every locale, and every database accepts it. CSV Polish reads the whole column before converting, decides day-first or month-first from the evidence it finds, and shows you which formats it detected next to the column name.

Numbers carry the same locale split: 1 234,56 in Moscow and Paris, 1.234,56 in Berlin, 1,234.56 in New York. A thousands separator turns a number into text for every importer, and a comma decimal silently becomes a different number if the reader assumes it is a thousands mark. Strip the group separators, settle on a dot for decimals, and keep the currency in its own column.

Open a CSV in Excel and it converts what it thinks it recognises. Leading zeros disappear (00745 becomes 745), long digit strings become scientific notation (8901234567890123 becomes 8.90123E+15), and because Excel stores only 15 significant digits, the tail of a card or IBAN number is replaced by zeros for good. Anything that looks like a date becomes one — a problem so persistent that in 2020 the HUGO Gene Nomenclature Committee renamed human genes such as SEPT1 because spreadsheets kept turning them into 1-Sep.

The defence is to never let Excel parse the file: use Data → From Text/CSV and set the problem columns to Text, or do the cleaning somewhere that treats every cell as a string, as CSV Polish does. Nothing in this tool retypes a value — it only removes characters you asked it to remove or rewrites a format you chose.

The last two problems are invisible. Leading and trailing spaces, double spaces, non-breaking spaces (U+00A0, typically pasted from a web page) and zero-width characters (U+200B) make “Paris” and “Paris ” two different customers in every join and every pivot table. Duplicate rows do the same in the opposite direction. Fix whitespace first, then deduplicate — in that order, or the duplicates will not match.