How to fix a broken or malformed CSV file

Most "corrupted" CSVs are not corrupted. A CSV is a text file; text files rarely rot. What has almost always happened is that the file was written with one set of assumptions and is being read with another — a different delimiter, a different encoding, a quoting rule the exporter got wrong.

So the job is diagnosis before repair. The symptom tells you which of six things went wrong, and each has a specific fix.

The fast answer

Look at the raw bytes first — not at the file in a spreadsheet, which is a tool that hides the evidence:

head -5 broken.csv          # the actual separators and quotes
file -I broken.csv          # the real encoding
wc -l broken.csv            # how many lines it thinks there are

Then match the symptom:

1. Everything lands in one column

The most common one by a distance, and never the file's fault.

Much of continental Europe uses semicolons, because the comma is the decimal separator there. Plenty of exports labelled .csv are actually tab-separated. head -5 shows you which in a second.

The fix is to tell the reader what the separator is, not to find-and-replace it. Search-and-replacing semicolons with commas will also replace the ones inside quoted values, and now you have a genuinely broken file.

• Excel: don't double-click. Data → From Text/CSV, then pick the delimiter in the preview.
• Google Sheets: File → Import → Custom separator.
• Command line: duckdb -c "SELECT * FROM read_csv('broken.csv', delim=';')"
• On a phone: an app that lets you re-open a file with explicit parse settings, rather than one that guesses once and gives up.

CSV Editor - Smart CSViPhone & iPad

Auto-detects delimiters, headers and encodings, and — the part that matters here — lets you re-open a file with custom parse settings when the guess is wrong.

What it does

2. Mojibake: é where é should be

Classic encoding mismatch. The file is UTF-8 and something read it as Latin-1 or Windows-1252. The bytes are intact; the interpretation isn't.

file -I broken.csv
# broken.csv: text/plain; charset=utf-8

The fix: re-open declaring UTF-8. In Excel, the import dialog has a File Origin dropdown — set it to 65001: Unicode (UTF-8). On the command line:

iconv -f WINDOWS-1252 -t UTF-8 broken.csv > fixed.csv

Note the direction. If the file is genuinely Windows-1252 (older systems, some ERP exports), that command converts it. If the file is already UTF-8 and merely being displayed wrong, converting it makes things worse — you would be encoding the mojibake. Check with file -I before you run anything.

3. order_id in the first column name

A UTF-8 byte-order mark: three invisible bytes at the start of the file, written by Excel and by a lot of Windows tooling. It breaks header matching in scripts and shows up as junk in naive parsers.

sed -i '' '1s/^\xEF\xBB\xBF//' broken.csv    # macOS
sed -i '1s/^\xEF\xBB\xBF//' broken.csv       # Linux

Only strip it if something downstream is choking. Excel actually wants the BOM — it is how Excel recognises a CSV as UTF-8 when you double-click it, and removing it is why accented characters break for the colleague you sent it to.

4. Rows split in half

You expected 10,000 rows and got 10,340, with fragments where addresses should be. Something contains a literal newline inside a field — an address, a comment box, a pasted paragraph.

This is legal CSV if the field is quoted:

id,name,notes
1,"Alice","Called back.
Wants a refund."

RFC 4180 allows that, and any proper parser handles it. The break happens when a tool splits on newlines before it thinks about quotes — which describes most shell one-liners, several spreadsheet importers, and a lot of hand-rolled code.

The fix is usually the reader, not the file. Use a parser that understands quoting: DuckDB, Python's csv module, csvkit, or any real spreadsheet import. If the field genuinely isn't quoted, the export is broken and re-exporting is faster than repairing.

To confirm which case you have:

csvclean -n broken.csv        # reports rows with the wrong field count

5. Everything after row 4,812 is wrong

Column counts drift partway through and never recover. That is one unescaped quote, and everything after it is being read inside a string that never closes.

id,product,price
1,"6"" pipe",4.50

That 6" should have been written 6"". Instead the parser sees the value end at 6, then a stray " opens a new one, and the rest of the file is inside it.

Find it:

csvclean -n broken.csv
awk -F',' 'NF != 3 {print NR": "$0}' broken.csv   # rough — ignores quoting

csvclean is the reliable one; the awk line is a fast approximation for files without quoted commas. Once you have the line number, fix that one row by hand — either double the inner quote, or replace it with in. and move on.

Doing that on a phone is genuinely awkward without an app that can jump to a row and edit a single cell:

CSV File Viewer - Smart CSViPhone & iPad · Android

Opens the file whole and lets you search it, so you can find the row where the structure goes wrong before deciding whether to repair it or re-export.

What it does

6. The file just ends

Last line is half a record, or the file is suspiciously round-numbered in size. The write was interrupted, or the download was.

tail -3 broken.csv

There is nothing to repair here — the data was never written. Delete the partial last line and accept the loss, or re-export. If it was a download, check the Content-Length against the actual size before blaming the file.

The repair rules

Four habits that turn most of the above into non-events:

1. Work on a copy. cp broken.csv broken.backup.csv. Every fix on this page is a guess until it's verified, and a guess you can't undo is a bad trade.
2. Fix the reader before the file. Most symptoms here are misconfiguration. Rewriting the data to suit a parser that was wrong leaves you with two problems.
3. Never find-and-replace delimiters. It hits the ones inside quoted values, and turns a readable file into a genuinely corrupt one.
4. Count rows before and after. Every repair. If the number changed and you didn't intend it to, the repair broke something.

When to give up and re-export

If more than a handful of rows are structurally wrong, stop repairing. A broken export usually means the exporter has a quoting bug, and every row it produces is suspect — including the ones that currently look fine. Ask for the file again, and ask for it quoted.