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. Avoid Comma Delimiters: If your data has lots of text/commas, consider using Tabs (TSV) or Pipes (|) as delimiters to reduce confusion.

---

File won't open? HappyCSV's Repair Tool uses advanced algorithms to fix structure, close quotes, and recover your data.