CSV dialects and validation

Correct CSV Formatting by Diagnosing the Specific Error

Diagnose CSV delimiter, quoting, row-width, header and encoding problems before correcting a source copy, then validate the resulting records.

By Uddhav Gopani, FounderPublished 27 September 2026Updated 29 September 2026

Correct CSV Formatting by Diagnosing the Specific Error

To correct CSV formatting, identify the specific failure before changing the file. A wrong delimiter, broken quote, inconsistent record width, duplicate header or encoding mismatch requires a different repair. Preserve the original and use a parser with the expected dialect to locate the problem.

Start from the observed symptom

If all fields appear in one column, check the delimiter selected by the importer. If a note containing a comma creates extra columns, inspect the quote characters around that value. If accented text appears damaged, investigate the encoding rather than replacing delimiters that may already be correct.

A file can be syntactically readable but still violate the receiving application’s schema. Missing required headers, an unexpected column order or an identifier interpreted as a number may cause rejection even though a CSV reader can separate the fields. Keep syntax checks distinct from data requirements.

Run a defined structural check

This example checks a comma-delimited UTF-8 file with one header record. It requires nonempty, distinct header names and the same number of fields in every data record. It writes a separate checked.csv only after those checks succeed and verifies the written values by parsing them again.

import csv

with open("input.csv", encoding="utf-8-sig", newline="") as source:
    reader = csv.reader(source, delimiter=",", strict=True)
    rows = list(reader)
if not rows or not rows[0]:
    raise ValueError("Expected a header row")
header = rows[0]
if any(not name.strip() for name in header) or len(set(header)) != len(header):
    raise ValueError("Expected nonempty, distinct header names")
for record_number, row in enumerate(rows[1:], start=2):
    if len(row) != len(header):
        raise ValueError(f"Record {record_number} has {len(row)} fields; expected {len(header)}")
with open("checked.csv", "w", encoding="utf-8", newline="") as target:
    csv.writer(target, lineterminator="\n").writerows(rows)
with open("checked.csv", encoding="utf-8", newline="") as check:
    assert list(csv.reader(check, strict=True)) == rows

The reported record number counts parsed records, including the header as record one. A quoted multiline field can occupy several physical lines, so that record number is not always a text editor’s line number. Inspect enough surrounding source to find the whole record before editing it.

Understand what the check does not repair

Strict CSV parsing catches certain quoting errors, while the explicit loop checks width. Neither knows the intended content of an ambiguous row. If a field contains an unquoted comma, an extra parsed value may be a data error or a genuinely additional column. Compare it with the authoritative record before choosing a correction.

The example also does not validate business-specific dates, amounts or required values. A blank field is still a field, and a consistently misspelled header can pass a uniqueness check. Add the receiving schema’s exact requirements when those are part of the task.

Make the smallest justified correction

Repair an identified quote boundary or delimiter setting on a copy, then rerun the same check. Avoid global replacements that modify commas inside quoted text. If an export application can regenerate the file with the correct settings, prefer a repeatable corrected export over a growing list of manual edits.

Record any manual correction with its source evidence. A repair should recover the intended value, not merely force a parser to accept fewer columns. Deleting an inconvenient field can make the table rectangular while losing information.

Handle encoding separately

The example accepts UTF-8 with an optional initial byte-order mark. It does not guess every legacy encoding or silently replace undecodable bytes. If reading fails, determine the source encoding and decode it explicitly. Using an ignore-errors setting can discard characters without resolving the underlying mismatch.

Inspect representative non-ASCII values after writing the corrected file. A visually ordinary English header does not demonstrate that names or descriptions elsewhere in the file survived decoding.

Check a valid and an invalid fixture

Test a small valid file with leading-zero identifiers, a quoted comma and an empty final field. Then test a malformed-width record and confirm the check rejects it without replacing an existing checked.csv. This demonstrates both successful processing and a safe failure path.

After the structural check, import the corrected copy through the receiving application. Compare record count, column names and representative values. Keep the original until that final acceptance succeeds; parser success alone does not prove that an application interpreted every date or identifier as intended.

Parser reference

Python’s CSV documentation describes the reader and writer used here. The additional header and width checks are explicit requirements of this example.