← All guides

Why CSV Imports Fail

"CSV" stands for comma-separated values, but the format has no single official specification everyone follows exactly the same way, which is why a file that opens fine in one program can fail, silently corrupt, or throw an error in another. The failure is almost always one of four specific, diagnosable problems.

1. The wrong delimiter

Not every CSV actually uses commas. Excel, depending on your system's regional settings, often exports CSV files delimited by semicolons instead, because in locales where a comma is the decimal separator (as in many European number formats), using it as a column separator too would be ambiguous. If you open a semicolon-delimited file with a comma-only parser, every row looks like a single column with semicolons inside it.

How to tell: open the file in a plain text editor. If every line has semicolons or tabs between values instead of commas, that's your delimiter mismatch.

2. Unescaped commas inside a field

A text field that legitimately contains a comma, an address like "123 Main St, Apt 4," or a company name like "Smith, Jones & Co.", has to be wrapped in quotes for a CSV parser to treat that comma as part of the text rather than a new column boundary. If the file that generated the CSV didn't quote the field correctly, the row silently splits into extra columns, throwing off every column after it for that row.

How to tell: if a specific row has more columns than the header row, this is almost always why.

3. Character encoding

A CSV is just text, and text has to be decoded using a specific character encoding, commonly UTF-8, but older files (especially from older Windows software) sometimes use Windows-1252 or another legacy encoding instead. Open a file with the wrong encoding assumed and accented characters, curly quotes, or non-English names turn into garbled symbols, even though the underlying data was never actually corrupted.

How to tell: names or words appear with strange characters like "é" where an accented letter should be. That's a classic UTF-8-read-as-Latin-1 (or vice versa) encoding mismatch.

4. Genuinely messy data

Separate from format problems, a CSV can just contain messy data: rows that are completely blank, columns nobody ever filled in, duplicate rows from a script that ran twice, or inconsistent whitespace around values. This category is different from the three above. The file parses correctly, the data itself just needs cleaning up before it's useful.

What a cleaning tool can and can't fix

Delimiter mismatches, unescaped-comma row breakage, and encoding problems all require knowing something about the file's origin or intent that a generic tool can't safely guess. Misinterpreting any of these risks silently corrupting data rather than fixing it. A cleaning tool can safely handle problem #4: trimming whitespace, dropping fully empty rows/columns, and removing exact duplicate rows, because none of those operations require guessing at ambiguous structure.

Cleaning what can be safely cleaned

CSV Cleaner trims whitespace, drops empty rows/columns, and removes exact duplicates, entirely in your browser. It won't fix a delimiter mismatch or unescaped commas. Those need fixing at the source, in whatever program generated the file.

Related tools

Once your CSV parses cleanly, convert it with CSV to JSON or CSV to Excel.