Why CSV Rows Have Different Numbers of Columns—and How to Repair Them

CSV rows usually have different column counts because a delimiter or quote was interpreted incorrectly, fields were omitted, or records with different schemas were combined. Do not fix the file by deleting overflow values or silently skipping bad rows. First parse it with the intended CSV rules, locate every uneven record, and determine where each value belongs.

The common CSV convention says the header and records should have the same number of fields. It also says fields containing commas, double quotes or line breaks should be enclosed in double quotes; a literal double quote inside a quoted field is written twice (RFC 4180).

What an uneven record looks like

This sample declares three columns:

id,name,location
1,Ada,"London, UK"
2,Lin,Paris

The quotes keep London, UK in one field. Remove them and the first data record has four parsed fields:

1,Ada,London, UK

The opposite problem is a missing field:

3,Sam

That record has two fields, not three. If location is genuinely blank, the explicit three-field record is:

3,Sam,

Do not add that final comma until the schema or source confirms that the missing value is location. A short row could instead indicate a truncated export.

The main causes

1. An unquoted delimiter inside a value. A place such as London, UK becomes two fields when commas are the delimiter.
2. A missing or misplaced closing quote. The parser may consume later physical lines as part of one quoted field, making several apparent “rows” look wrong.
3. An unescaped quote inside a value. In conventional CSV, She said "hello" must be represented as "She said ""hello""".
4. The wrong delimiter. A semicolon-delimited export parsed as comma-separated data may appear to have one column; mixed punctuation can produce less predictable counts. Confirm the dialect rather than relying on a filename or a one-line guess. See CSV delimiter detection.
5. A trailing delimiter or omitted placeholders. 1,Ada, has three fields, with the last empty. 1,Ada has only two. An accidental extra comma can likewise create an unwanted empty field.
6. Mixed schemas. Appending exports from different periods or systems can combine four-column and five-column records. Repeated headers in the middle of a file are another clue.
7. A valid multiline field mistaken for two records. A quoted field may contain a line break, so a physical line is not always a complete CSV record. Python’s CSV documentation makes the same distinction: its reader’s line_num counts source lines, which can differ from records returned (Python csv documentation). See line breaks inside CSV cells before removing any newline.

Audit parsed column counts with Python

The following audit assumes the first parsed record is the header and the intended dialect uses commas and doubled double quotes. Save it as check_csv.py, then run python check_csv.py data.csv.

import csv
import sys

path = sys.argv[1]

try:
    with open(path, encoding="utf-8-sig", newline="") as f:
        reader = csv.reader(
            f,
            delimiter=",",
            quotechar='"',
            doublequote=True,
            strict=True,
        )

        header = next(reader)
        expected = len(header)
        print(f"Expected columns: {expected}")

        problems = 0
        for record_number, row in enumerate(reader, start=2):
            if len(row) != expected:
                problems += 1
                print(
                    f"Record {record_number}, ending near physical line "
                    f"{reader.line_num}: {len(row)} columns"
                )

        print(f"Uneven records: {problems}")

except StopIteration:
    print("The file is empty.")
except csv.Error as error:
    print(f"CSV syntax error near physical line {reader.line_num}: {error}")

Opening with newline="" follows the Python module’s documented file-handling practice. The parser returns each record as a list, so len(row) measures parsed fields rather than counting visible commas (Python csv documentation). If the source is semicolon- or tab-delimited, change delimiter only after confirming that format.

The reported physical line is a location aid, not necessarily the start of the bad record. An unclosed quote may cause the parser to continue across multiple lines. A fatal syntax error also stops this audit at that point; repair it and run the script again to inspect later records.

Repair without losing values

Work on a copy and keep the original unchanged.

1. Confirm the schema. Establish the intended column names and count from source documentation or the generating system—not merely from the longest row.
2. Confirm the dialect. Record the delimiter, quote character, escape convention, encoding and whether a header is present.
3. Inspect each flagged record in context. Look at the preceding and following records because an unclosed quote can shift the apparent boundary.
4. Map every parsed value to a named column. For an extra field, determine whether it is part of an adjacent text value, a real new column, or an accidental empty field. Never discard it just to match the header.
5. Repair syntax at the source when possible. Quote fields containing delimiters or line breaks and double embedded quotes. If an exporter consistently produces malformed data, correct its export settings rather than repeatedly patching files.
6. Treat short records as missing-data questions. Add an explicit empty field only when you know which column is blank. Preserve the distinction between an empty field, zero and an unknown value; empty CSV field vs zero vs missing value explains that decision.
7. Separate incompatible schemas. Normalize them through an explicit column mapping before concatenation. Preserve newly introduced columns rather than forcing later records into an older layout.
8. Parse the repaired file again. Require zero syntax errors and zero unexpected field counts. Then compare record counts, headers and sampled values with the source using the CSV export validation checklist.

Avoid “skip bad lines” as a final repair. DuckDB, for example, documents that ignore_errors omits rows that cause parser errors. Its rejects-table feature also skips faulty rows, but retains the original line, error type and error message for review (DuckDB documentation). For a public dataset, reconcile every rejection and document any intentional exclusion before uploading the clean copy. Also confirm that the file contains no sensitive information, because the resulting data page is public.