How to Fix CSV Files with Ragged Rows (Rows with Too Many or Too Few Columns)

A practical guide — Excel, Google Sheets, Python, and the automatic way.

A CSV file has a simple contract: every row has the same number of cells as the header. When that contract breaks — some rows are too short, others too long — it's called ragged rows, and import tools react badly. Excel's text wizard may shove data into the wrong columns, databases reject the file outright ("expected 7 columns, found 6"), and in the worst case values silently land under the wrong headers, which you only discover weeks later.

Ragged rows creep in from sloppy exports, hand-editing a CSV in a text editor, joining two files with different columns, and — the classic — extra trailing commas on some rows. Here's how to find and fix them.

Method 1: Excel

1. Open the CSV in Excel. To find the ragged rows, add a helper column (say column H if your data goes to G) and enter =COUNTA(A2:G2) in the first row of it — this counts the non-empty cells in that row.
2. Double-click the fill handle to copy it down the whole column. Now sort or filter: rows whose count doesn't match the header are your ragged ones.
3. For short rows: insert empty cells where they're missing (right-click the row number → Insert) so every row lines up with the header. If the missing cells are at the end, you can just add commas at the end of the row.
4. For long rows: look at the extras. If they're stray trailing commas, delete the trailing empty cells. If real data is spilling into extra columns, check whether a value contains a comma that isn't wrapped in quotes — that one comma split one cell into two. Wrap the value in quotes or remove the comma.
5. Delete the helper column and save.

Method 2: Google Sheets

1. Import the CSV into Google Sheets (File → Import).
2. Add a helper column with =COUNTA(A2:G2) pointing at your data range, and fill it down. Any row whose count differs from the header row is ragged.
3. Use Data → Create a filter and filter the helper column to the wrong counts so you only see the problem rows, then fix them the same way as in Excel: pad short rows, inspect and fold long rows.
4. Delete the helper column and re-download as CSV (File → Download → Comma-separated values).

Method 3: Python (for the technical)

import csv

with open('ragged.csv', newline='', encoding='utf-8') as f:
    rows = list(csv.reader(f))

width = len(rows[0])  # trust the header
fixed = [rows[0]]
for n, row in enumerate(rows[1:], start=2):
    if len(row) < width:
        row += [''] * (width - len(row))      # pad short rows
    elif len(row) > width:
        print(f"row {n}: too long, check: {row}")
        row = row[:width]                     # trim after checking
    fixed.append(row)

with open('fixed.csv', 'w', newline='', encoding='utf-8') as f:
    csv.writer(f).writerows(fixed)

The print in the loop matters: rows that are too long are the dangerous ones, because trimming them can discard real data. Look at each flagged row before trusting the output.

The one-click way

More CSV fixes: remove blank rows · fix headers with spaces · convert to UTF-8 · CSV won't import? · trim extra spaces · fix numbers stored as text

← All guides