CSV Rows Have Different Column Counts: Find and Fix the Cause

Count fields after parsing, not commas in the line

The schema here is sku, description, price: three fields. A17 contains an additional comma inside a quoted description, so counting punctuation would incorrectly label it as too wide. D40 contains an empty middle field, which still occupies the description position.

A real mismatch appears on B28, where the final comma introduces a fourth, empty field. C39 supplies only two fields. These are separate defects, and neither one tells us whether the missing or extra content was caused by a bad export, a manual edit, or a different schema.

Locate the first mismatch with a repeatable check

Save the sample as input.csv and run the Python script below from the same folder. It reports record numbers including the header. Python strict parsing can catch some malformed quoting, while the explicit length check handles unequal field counts. A successful parse is not proof that values match the intended business columns.

import csv

with open("input.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.reader(f, delimiter=",", strict=True)
    header = next(reader, None)
    if header is None:
        raise ValueError("The file is empty")
    expected = len(header)
    for record, fields in enumerate(reader, start=2):
        if len(fields) != expected:
            print(f"record {record}: {len(fields)} fields; expected {expected}")

# Output for this sample:
# record 3: 4 fields; expected 3
# record 4: 2 fields; expected 3

Choose a repair using the source meaning

Check the exporter definition, the corresponding source record, or the file supplier before editing. For this exercise, assume the supplier confirms that B28 ends with an accidental delimiter and C39 has no recorded price. Those confirmations make the changes below justified; the field counts alone would not.

Related help: Check commas and double quotes before editing a record

Write an explicit corrected example

The corrected C39 record ends in a comma because its final price field is empty. That differs from the unwanted fourth field on B28. The same punctuation can therefore be necessary in one record and erroneous in another, depending on the schema.

An empty price is still missing information. Do not substitute zero unless zero is the correct recorded price. A structurally valid table can remain incomplete for invoicing, inventory valuation, or another downstream task.

sku,description,price
A17,"Cup, blue",12.50
B28,Plate,8.00
C39,Spoon,
D40,,4.25

Recheck the whole file after fixing one record

Run the field-count check again and confirm four data records remain. Compare the corrected values against the source, then verify required columns separately. In this example, prices present in the source total 24.75, and one price remains missing. Report both facts rather than presenting 24.75 as a complete valuation.

If many rows fail in the same way, investigate the export mapping instead of editing hundreds of lines. If the first failure contains an opening quote, inspect the following physical lines as well: an unfinished quoted field can cause a parser to consume later content as part of the same record.

Do not hide extra fields during import

Check import settings that force a fixed number of columns. A preview can look rectangular after an importer discards extra fields or fills missing positions. Microsoft documents this possibility for Csv.Document when a column count is supplied. Validate the source before such a transformation masks the evidence.

Column Harbor flags selected CSV structural risks; it does not know which business field was omitted or repair the source for you. Keep a short edit log with the affected record, the correction, and how you confirmed it.