How to Fix a Broken CSV File

A broken CSV rarely announces itself. The parser fails on line 4,812 of a file whose real problem is on line 39, or worse, it does not fail at all — every row parses, every column shifts by one, and nobody notices until a report comes out wrong. Repairing one is mostly a matter of finding the actual break rather than the reported one.

This post covers the diagnosis and the fixes. For the underlying rules being violated, see The Complete Guide to CSV Files and Formatting.

Start by locating the real break

Before editing anything, find the first row where the structure diverges. That row is what you fix; everything after it is usually a consequence.

The fastest way is to paste the file into a CSV Validator, which reports the line and column of each structural problem — unbalanced quotes, field counts that disagree with the header, stray delimiters — rather than the single generic error a parser gives up with. Because it runs entirely in the browser, an export full of customer records never leaves your machine.

If you are on the command line, awk -F, '{print NF}' file.csv | sort | uniq -c gives you a quick histogram of field counts. One dominant number plus a handful of outliers tells you the file is mostly fine and localises the damage. Note that this trick lies about correctly quoted fields containing commas, so treat it as a first look, not a verdict.

The five things that actually break

1. Unquoted delimiters inside fields

The most common failure by a wide margin. An exporter writes a value containing a comma without wrapping it in quotes:

id,name,city
1,Ada Lovelace,London
2,Hopper, Grace,New York

Row 3 has four fields against a three-field header. The fix is to quote the offending field: 2,"Hopper, Grace",New York. If the file is large and the pattern is consistent, a regex that targets the specific column is safer than a global substitution — but verify the row count afterwards either way.

Where the damage has already propagated into a database or spreadsheet, the shifted columns are usually recoverable because the misplaced values are still recognisable (a city name sitting in a postcode field). Fix the CSV and reload rather than patching the destination.

2. Unterminated quotes

An opening quote without a closing one is the error that reports itself in the wrong place. The parser swallows every subsequent line looking for the close, so the failure surfaces thousands of rows later — or at end of file, as a cryptic "unexpected end of input".

The usual cause is a field ending in an inch mark or a stray typographic quote: 48" monitor written as "48" monitor". The parser sees the field close after 48 and then chokes on the text that follows.

The correct form doubles the inner quote: "48"" monitor". When hunting for the culprit, look for an odd number of quote characters on a line — grep -n '"' file.csv | awk -F'"' 'NF%2==0' surfaces lines with unbalanced quoting.

3. Embedded line breaks

A line break inside a quoted field is legal CSV, and a great deal of tooling assumes it is not. Address fields and free-text comments are where they come from.

id,address
1,"12 Baker Street
London NW1"

That is a valid two-row file with two fields per row. If your pipeline splits on newlines before parsing, it sees three rows and breaks. The fix is not to edit the file — it is to use a real CSV parser. If a downstream system genuinely cannot cope, replace the embedded breaks with a placeholder such as \n or a space, but do that as an explicit transformation rather than pretending the original was invalid.

4. Encoding damage

café displayed as café means a UTF-8 file was read as Windows-1252. caf? or caf with a missing character means the reverse, or a lossy conversion that already discarded the byte.

Fix this at the read step, not with find-and-replace. Reopen the file specifying UTF-8 — most editors offer "Reopen with encoding" — and if the characters come back, save it once as UTF-8 and move on. Only when the original bytes are genuinely gone do you have to repair the text itself, and at that point regenerating the export is almost always cheaper.

If the recipient will double-click the file into Excel on Windows, saving with a UTF-8 BOM prevents the same corruption happening again on their side.

5. Delimiter mismatch

A file that opens entirely in column A is not broken; it is using a delimiter your tool did not expect. Semicolons are standard output from spreadsheet software in locales where the comma is the decimal separator, and tab-separated files are common from database exports. Which delimiter to expect and how to convert between them is a topic in itself.

Convert rather than search-and-replace: swapping every semicolon for a comma will destroy any field that legitimately contains a semicolon. A proper parser reads with one delimiter and writes with another, respecting quoting on both sides.

Repairing at scale

For a handful of rows, edit the text directly in an editor that does not reformat on save. For anything larger, run the file through a parse-and-rewrite cycle: a CSV Formatter reads the file with a tolerant parser and re-emits it with consistent quoting, uniform line endings, and one delimiter throughout. That single pass fixes inconsistent quoting, mixed CRLF and LF, and trailing whitespace without you having to identify each instance.

What it cannot fix is ambiguity. If a row genuinely has an extra field and there is no quoting to say which value was split, no tool can know whether Hopper, Grace was one name or two. Those rows need a human decision or a trip back to the source.

For files too large to open at all, splitting them into chunks makes the damaged section tractable — repair the affected chunk, then recombine.

Verify before you trust it

Three checks catch almost every bad repair:

1. Row count. Compare against the source system's record count. A repair that changed the row count merged or split records.
2. Field count consistency. Every row should match the header. A CSV Analyzer shows the distribution and flags outliers.
3. Spot-check the ugly rows. Find records containing commas, quotes, non-ASCII characters, and empty fields, and confirm each survived intact. These are the rows that break; the boring ones were never at risk.

If you control the export that produced the file, fix it there too. A CSV that broke once will break again next month, and repairing files by hand is not a process.