How to Fix Broken CSV Delimiters (Mismatched Commas & Semicolons)
Your CSV file has some rows with 4 columns and others with 5 or 6 columns, causing data to shift into the wrong headers and creating broken, jagged tables.
A broken CSV delimiter occurs when separator characters appear inconsistently across lines, unescaped commas exist inside text fields, or files mix commas and semicolons. To repair a broken CSV: 1) Open the file in TextToTable to view row column-count warnings, 2) Use the Quick Delimiter chips to test whether Semicolon or Tab resolves the alignment, 3) Use "Normalize Columns" to pad uneven rows, and 4) Re-export as a clean, properly quoted RFC 4180 CSV.
In This Guide
1. Why Does This Problem Happen? (Root Causes)
Unquoted Commas in Free-Text Fields: An address field containing "Suite 400, Floor 2" or a company name "Acme, Inc." without surrounding quotes splits the single column into two.
Mixed Regional Separators: Copying rows from European datasets (semicolons) into US datasets (commas) produces mixed delimiter lines.
Unescaped Line Breaks: Multi-line comments or product descriptions exported without quotes break records across multiple physical rows.
Missing Escape Characters: Double quotes inside fields exported as `"` instead of `""` (RFC 4180 double-double quote convention).
2. How to Fix It (Step-by-Step Methods)
Step 1: Diagnose Column Count Variance
Paste your file into TextToTable. The built-in validator inspects every row and alerts you if any row has fewer or more columns than your header row. Look for warning badges such as "Row 3 has 5 columns (expected 4)".
Step 2: Inspect and Edit Broken Cells Directly
In TextToTable's interactive grid preview, double-click the cell where the shift occurred. You can edit cells, merge split values, or delete spurious empty columns directly in your browser without regex scripting.
Step 3: Click "Normalize Columns"
Click the "Normalize Columns" button in the repair toolbar. Short rows are padded with empty cells to match the header count, preventing jagged exports from crashing downstream database loaders.
Step 4: Download Corrected RFC 4180 CSV
Click "Download CSV". TextToTable ensures all commas inside text are safely enclosed in standard double quotes, producing a 100% compliant file.
3. Verified Test Fixture & Output
Reproducible test input and the verified multi-column result.
ID,Company,Location,Revenue 101,Apex Solutions,Chicago,500000 102,Global Systems, Inc.,Austin,820000 103,Metro Corp,Seattle,340000
| ID | Company | Location | Revenue |
|---|---|---|---|
| 101 | Apex Solutions | Chicago | 500000 |
| 102 | Global Systems, Inc. | Austin | 820000 |
| 103 | Metro Corp | Seattle | 340000 |
4. Common Pitfalls to Avoid
5. Frequently Asked Questions
How do I know which delimiter my CSV is supposed to use?
Count the characters on line 1 (the header). If line 1 has 4 semicolons and 0 commas, the file is semicolon-delimited. TextToTable calculates delimiter consistency scores across the first 30 lines to determine the true delimiter with statistical confidence.
What causes line breaks to break my CSV file?
Under RFC 4180, a cell can contain a line break ONLY if the entire cell is enclosed in quotation marks. If a database exports text with unquoted line breaks, spreadsheets treat each line as a new record.
Related Guides & Tutorials
How to Handle Commas Inside CSV Fields (RFC 4180 Rules)
Learn how RFC 4180 governs commas inside CSV fields. Master quotation rules, escaped quotes, and prevent broken columns in spreadsheets.
Troubleshooting GuideHow to Fix CSV Files Opening in One Column in Excel
Fix CSV files opening in a single column in Excel. Use Text to Columns, Power Query, regional separator fixes, or our free converter without data loss.
Authoritative Technical References & Standards
- • IETF RFC 4180 Specification: Common Format and MIME Type for Comma-Separated Values (CSV) Files.
- • Microsoft Excel Documentation: Import or export text (.txt or .csv) files in Microsoft Excel.
- • Unicode Consortium Standards: UTF-8, UTF-16, and Byte Order Mark (BOM) Specifications.