Fixing Broken CSVs: A Practical Cleanup Checklist

CSVs sound simple enough — values separated by commas, what could go wrong? Plenty, it turns out. One stray comma, a mismatched quote, or a missing value and suddenly your import fails or your dataset is silently wrong. Here's how I fix broken CSVs, step by step, whether they come from spreadsheets, API exports, or log dumps.

Step 1: Confirm the delimiter

CSV stands for comma-separated, but plenty of exports actually use semicolons or tabs. Open a sample of the file and look at what delimiter appears most consistently. If it's a mix, clean that up first with the Change CSV Delimiter tool.

Step 2: Fix inconsistent quotes

Quoting problems are the number one cause of column drift in my experience. Some rows have double quotes, others don't, and parsers lose track of where fields start and end. Pick one approach: strip quotes entirely with Remove CSV Quotes, or enforce them everywhere withAdd CSV Quotes.

Step 3: Detect missing columns

Rows with uneven column counts will break whatever you feed the file into next. Use Find Incomplete CSV Records to spot the broken rows, then patch them with Fill Incomplete CSV Records.

Step 4: Normalize whitespace

Leading and trailing spaces cause the kind of bugs that waste hours — lookups fail, joins return nothing, and the data looks fine until you inspect it character by character. Clean this up withTrim CSV Columns so values actually match when they should.

Step 5: Validate the final file

Before you call it done, run the file through the CSV Validator. You're checking that every row has the same column count and that quoting is consistent throughout.

Recommended cleanup order

• Detect delimiter
• Normalize or remove quotes
• Find and fill incomplete rows
• Trim columns
• Validate

When to convert CSV to another format

If your data has nested objects or arrays, CSV is the wrong format for it. You'll spend more time escaping edge cases than doing actual work. Convert to JSON with CSV to JSON instead — it handles structure natively and you won't lose data to formatting quirks.