Python Basics: Detect a CSV Delimiter and Validate Every Row

A successful delimiter guess does not make a CSV file valid. This example accepts comma or semicolon input, checks an exact two-column schema, and validates every quantity before returning any records.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14. You need Bash, Python, a text editor, and the standard mktemp, rm, and rmdir utilities. Run without -O so the assertions remain enabled. No external packages are required.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-csv-dialect-validation.py:

import csv
import io


def read_stock(text):
    dialect = csv.Sniffer().sniff(text[:4096], delimiters=",;")
    rows = csv.reader(io.StringIO(text, newline=""), dialect, strict=True)
    if next(rows, None) != ["name", "quantity"]:
        raise ValueError("expected name and quantity headers")
    records = []
    for row in rows:
        if len(row) != 2 or not row[0].strip():
            raise ValueError("expected a name and one quantity")
        try:
            quantity = int(row[1])
        except ValueError:
            raise ValueError("quantity must be an integer") from None
        if quantity < 0:
            raise ValueError("quantity must be nonnegative")
        records.append((row[0], quantity))
    return dialect.delimiter, records


valid = 'name;quantity\n"Notebook; blue";2\nPencil;5\n'
delimiter, records = read_stock(valid)
assert delimiter == ";"
assert records == [("Notebook; blue", 2), ("Pencil", 5)]
print("Detected delimiter:", delimiter)
for name, quantity in records:
    print(f"{name}: {quantity}")
print("Total:", sum(quantity for _, quantity in records))

invalid = "name;quantity\nPencil;-1\n"
try:
    read_stock(invalid)
except ValueError as error:
    print("Rejected:", error)
else:
    raise AssertionError("expected invalid quantity to fail")

Run it:

python3 -B demonstrate-csv-dialect-validation.py

Observed output:

Detected delimiter: ;
Notebook; blue: 2
Pencil: 5
Total: 7
Rejected: quantity must be nonnegative

Read the result

The quoted semicolon stays inside the notebook’s name. Sniffer is limited to two candidate delimiters, while the reader enforces quoting syntax. The explicit checks handle application rules that the CSV parser cannot infer. No records leave the function until all rows have passed.

The negative quantity is syntactically valid CSV. Its rejection therefore comes from the schema check, not dialect detection. Numeric conversion uses Python’s integer syntax; applications requiring digits alone should add that stricter rule.

Apply it carefully

Sniffing is a heuristic and can raise csv.Error or choose the wrong dialect on ambiguous or short samples. Prefer a configured dialect when the supplier defines one. Production callers should handle both csv.Error and ValueError and impose input and record limits. Here the whole document is deliberately tiny and already in memory. For a real file, open it with newline="" and an explicit encoding; rewind after sampling before parsing.

The official csv reference describes the relevant APIs and their limits.

Clean up

Remove the saved source, return to the starting directory, and remove the empty practice directory:

rm -- demonstrate-csv-dialect-validation.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its remaining contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.