CSV with quotes, commas and newlines

Handle it in code

Each snippet below was run against this exact file with Python 3.14.4 and Node 24.15.0 when this page was built, and the output shown is what it printed.

Python

Sniff the dialect from a sample, then stream the rows and compare each with the header. The csv module handles quoted commas and newlines inside fields, which is exactly what a split(',') gets wrong. line_num is the physical line, so it stays right when a field spans lines.

import csv

with open("quoting-nightmare.csv", newline="", encoding="utf-8-sig") as f:
    try:
        dialect = csv.Sniffer().sniff(f.read(64 * 1024), delimiters=",;\t|")
    except csv.Error:
        dialect = csv.excel  # the sniffer gives up on irregular files: assume commas
    f.seek(0)
    rows = csv.reader(f, dialect)
    header = next(rows)
    count, bad = 0, []
    for count, row in enumerate(rows, 1):
        if len(row) != len(header):
            bad.append((rows.line_num, len(row)))
print(f"delimiter {dialect.delimiter!r}, {len(header)} columns, {count:,} data rows")
for line, n in bad[:5]:
    print(f"  line {line}: {n} field{'s' * (n != 1)}, expected {len(header)}")
if not bad:
    print("  every row matches the header")

Run against this file, it prints: delimiter ',', 4 columns, 7 data rows every row matches the header