Diagnose CSV Delimiters with Python: Sniffer, Explicit Formats, and Full Validation

python3 --version
mkdir csv-delimiter-demo
cd csv-delimiter-demo
python3 - <<'PY_DATA'
from pathlib import Path
Path("samples.csv").write_bytes(
    b'\xef\xbb\xbfid;note\n001;"alpha,beta"\n002;"two\nlines"\n'
)
PY_DATA

"""Inspect completed local CSV files; guess only the delimiter. Python 3.9+."""
import argparse
import csv
import json
from pathlib import Path
import sys

DELIMITERS = {"comma": ",", "semicolon": ";", "tab": "\t", "pipe": "|"}


def inspect_csv(path, mode, columns):
    if len(columns) < 2 or len(set(columns)) != len(columns):
        raise ValueError("expected columns must be unique and number at least two")
    if any(not name.strip() for name in columns):
        raise ValueError("expected column names must not be blank")
    with path.open(encoding="utf-8-sig", newline="") as stream:
        if mode == "auto":
            sample = stream.read(8192)
            try:
                delimiter = csv.Sniffer().sniff(sample, delimiters=",;\t|").delimiter
            except csv.Error as exc:
                raise ValueError("cannot suggest delimiter; specify --delimiter") from exc
            stream.seek(0)
        else:
            delimiter = DELIMITERS[mode]
        reader = csv.reader(stream, delimiter=delimiter, quotechar='"',
                            doublequote=True, skipinitialspace=False, strict=True)
        try:
            header = next(reader, None)
            if header != columns:
                raise ValueError(f"header mismatch: expected {columns!r}, got {header!r}")
            records = 0
            for row in reader:
                if len(row) != len(columns):
                    raise ValueError(
                        f"record {records + 1}, ending line {reader.line_num}: "
                        f"expected {len(columns)} fields, got {len(row)}"
                    )
                records += 1
        except csv.Error as exc:
            raise ValueError(f"CSV error near physical line {reader.line_num}: {exc}") from exc
    return {"delimiter": delimiter, "columns": columns, "records": records,
            "physical_lines": reader.line_num, "selection": mode}


def main():
    parser = argparse.ArgumentParser(description=__doc__, allow_abbrev=False)
    parser.add_argument("file", type=Path)
    parser.add_argument("--delimiter", choices=["auto", *DELIMITERS], default="auto")
    parser.add_argument("--columns", nargs="+", required=True)
    args = parser.parse_args()
    try:
        report = inspect_csv(args.file, args.delimiter, args.columns)
    except (OSError, UnicodeError, ValueError) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 1
    print(json.dumps(report, ensure_ascii=True))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

python3 inspect_csv.py --help
python3 inspect_csv.py samples.csv --columns id note
python3 inspect_csv.py samples.csv --delimiter semicolon --columns id note

{"delimiter": ";", "columns": ["id", "note"], "records": 2, "physical_lines": 4, "selection": "auto"}

python3 inspect_csv.py samples.csv --delimiter comma --columns id note
printf 'exit=%s\n' "$?"

ERROR: header mismatch: expected ['id', 'note'], got ['id;note']

python3 inspect_csv.py samples.csv --delimiter semicolon --columns id note \
  && printf 'Input structure accepted\n'

"""Run from the directory containing inspect_csv.py. No third-party packages."""
import csv
import hashlib
import io
from pathlib import Path
import subprocess
import sys
import tempfile

script = Path("inspect_csv.py").resolve()
cases = [
    ("semicolon", b'id;note\n001;"alpha,beta"\n', "auto", 0, ";"),
    ("comma", b'id,note\n001,"alpha,beta"\n', "auto", 0, ","),
    ("tab", b'id\tnote\n001\talpha\n', "tab", 0, "\t"),
    ("pipe", b'id|note\n001|alpha\n', "pipe", 0, "|"),
    ("multiline", b'\xef\xbb\xbfid;note\r\n001;"a\nb"\r\n', "semicolon", 0, ";"),
    ("wrong", b'id;note\n001;alpha\n', "comma", 1, None),
    ("ragged", b'id;note\n001;alpha;extra\n', "semicolon", 1, None),
    ("blank", b'id;note\n\n', "semicolon", 1, None),
    ("quotes", b'id;note\n001;"unfinished\n', "semicolon", 1, None),
    ("encoding", b'id;note\n001;\xff\n', "semicolon", 1, None),
    ("empty", b'', "auto", 1, None),
]
with tempfile.TemporaryDirectory() as directory:
    for name, data, mode, expected, delimiter in cases:
        path = Path(directory) / (name + ".csv")
        path.write_bytes(data)
        before = hashlib.sha256(data).hexdigest()
        result = subprocess.run(
            [sys.executable, str(script), str(path), "--delimiter", mode,
             "--columns", "id", "note"], capture_output=True, text=True
        )
        assert result.returncode == expected, (name, result.stderr)
        assert hashlib.sha256(path.read_bytes()).hexdigest() == before
        if expected == 0:
            import json
            report = json.loads(result.stdout)
            assert report["delimiter"] == delimiter
            assert report["records"] == 1 and not result.stderr
        else:
            assert not result.stdout and result.stderr.startswith("ERROR:")
        print(f"PASS {name}: exit={expected}")
# A naive split breaks the quoted comma; csv.reader preserves it.
assert '001,"alpha,beta"'.split(",") != ["001", "alpha,beta"]
assert next(csv.reader(io.StringIO('001,"alpha,beta"'))) == ["001", "alpha,beta"]
print("PASS quoted comma: field values preserved")