How to Repair Broken CSV Files

A broken CSV file is a nightmare. You open it, and columns are shifted. Data from Row 5 is suddenly in Row 6. Half the file is missing.

Before you panic and delete it, know that most CSV errors are structural and fixable.

Here is how to diagnose and repair your file.

Signs of a Broken CSV

1. The "Staircase" Effect: Row 1 has 5 columns. Row 2 has 5. Row 3 has 4. Row 4 has 6. The data looks jagged.
2. Merged Rows: Two rows of data appear on a single line.
3. Split Rows: One row of data is split across two lines.
4. Garbage Characters: Weird symbols appearing in the text.
5. Import Errors: "Line 452: Unexpected end of file" or "Invalid column count".

Common Causes

• Unescaped Quotes: A text field contains a quote " that isn't escaped, confusing the parser.
• Unescaped Newlines: A text field contains a line break, which the parser thinks is a new row.
• Delimiter Confusion: The data contains commas , but isn't wrapped in quotes.
• Transmission Errors: The file download was cut off (incomplete file).

How to Fix It

Fix 1: The "Open and Save" (For Minor Issues)

Sometimes Excel or Google Sheets is smart enough to auto-correct minor errors.

1. Open the file in Google Sheets (it has a very forgiving parser).
2. If it looks okay, File > Download > CSV.
3. This creates a fresh, structurally correct file.

Fix 2: Text Editor Surgery (For Specific Errors)

If the error is on a specific line (e.g., import fails at Line 402), go look at it.

1. Open in a code editor (VS Code, Notepad++).
2. Go to Line 402.
3. Look for:
   • A missing comma.
   • An extra quote.
   • A line break where there shouldn't be one.
4. Manually correct it.
5. Save.

Fix 3: Fix Unescaped Quotes (The Hardest Problem)

This is the most common cause of "shifted columns".

Bad: 1,John "The Rock" Johnson,Actor Parser sees:

• Col 1: 1
• Col 2: John "The Rock" Johnson (Wait, quotes usually start/end a field. This quote in the middle confuses it.)

Fixed: 1,"John ""The Rock"" Johnson",Actor (The field is quoted, and internal quotes are doubled).

Tool Solution: Use a CSV Repair tool that uses "heuristics" (smart guessing) to identify where quotes should be.

Fix 4: Fix Line Breaks

If a row is split:

1,John,Description of
John
2,Jane,Description

You need to join line 1 and 2. In a text editor, go to the end of "Description of" and press Delete to pull "John" up.

Fixed:

1,John,"Description of John"
2,Jane,Description

When to Give Up

If the file is binary corrupted (you see a^%), it's likely unrecoverable. This happens with disk errors or bad file transfers.

• Solution: Re-download or restore from backup.

If the file is truncated (ends abruptly in the middle of a row):

• You can save the data that is there (delete the last incomplete row).
• You cannot recover the missing data.

Prevention

1. Use Robust Generators: Ensure the software creating the CSV handles quotes and newlines correctly (use standard libraries, don't just concatenate strings).
2. Validate Early: Check the file immediately after creation.
3. Use a documented dialect: Commas, tabs and pipes can all work when the generator quotes and escapes fields correctly. Match the destination specification.

Verify repairs against the source

A parser accepting the repaired file proves only that the syntax is readable. Compare the source and output record counts, expected column count and important identifiers. Inspect the rows immediately before and after every reported defect, because a missing quote can shift several later records before the parser fails.

Keep a repair log containing the original row or byte position, the change made and the reason. For sensitive or financial data, have someone else review changes that alter field boundaries or values. Never overwrite the only copy.

If many unrelated rows are malformed, request a new export before investing hours in manual edits. Repeated corruption often points to a broken generator, an interrupted transfer or a file that is not actually CSV. Correcting the source is safer than repeatedly repairing its output.

---

File won't open? HappyCSV's Repair Tool attempts a permissive parse and rewrites the rows it can read. Keep the original and verify row counts and values; severely malformed input may need manual repair.