Batu Lab Notes · Batu
A CSV file is not ready for import merely because it opens. The wrong delimiter can collapse an entire row into one column. An unexpected encoding can damage non-ASCII characters. A repeated identifier can overwrite an existing record in the destination. A short, read-only check makes these problems visible before data reaches a spreadsheet or application.
Quick answer
Before importing a CSV, set its encoding and delimiter, validate the exact header and every row width, distinguish empty IDs, duplicate IDs, and duplicate rows, and keep the check read-only.
1. Specify the encoding
Python may open a text file with the platform’s default encoding, which can differ across machines. If the expected encoding is UTF-8, encoding="utf-8-sig" is a practical choice: it reads ordinary UTF-8 and skips an optional UTF-8 byte order mark at the start. If decoding fails, verify the source application’s export settings instead of silently trying unrelated encodings.
2. Make the delimiter explicit
Despite the name, CSV exports may use commas, semicolons, or tabs. Python’s csv module treats the delimiter as part of a dialect. Sniffer.sniff() can infer a format from a sample, but inference is not a schema contract. The official documentation also describes has_header() as a rough heuristic that can produce false positives and negatives. For a critical import, configure the expected delimiter and validate the resulting column count.
3. Treat the header as a contract
When field names are not supplied separately, DictReader uses the first row as the header. That convenience does not make customerid an acceptable substitute for customer_id. Check required columns, unexpected columns, blank names, duplicate names, and order. The example below requires a fixed schema with exactly one id column. If the actual header is not identical, it stops before interpreting data under the wrong column order.
4. Validate every row width
DictReader can place extra fields under a None key and fill missing fields with its restval, which defaults to None. Successful parsing therefore does not prove that a row matches the schema. Report both extra and missing cells as errors. Do not parse rows with split(): quoted values can legitimately contain the delimiter, and csv.reader exists to apply those quoting rules.
5. Separate empty and duplicate IDs
Trim the primary identifier and reject it when empty. A second occurrence of the same ID is a key collision: two rows appear to describe the same entity. This check is different from detecting two rows whose every cell is identical.
6. Report duplicate rows separately
Two identical rows often point to duplication during export or concatenation. Two different rows with the same ID represent a conflicting record. Keep “duplicate row” and “duplicate ID” findings separate. The checker should not decide automatically which record to delete.
7. Keep validation read-only
The following standard-library example only opens the input for reading. The expected encoding, delimiter, and header are explicit. It reports row width, empty IDs, duplicate IDs, and duplicate complete rows independently.
import csv
def check_csv(path, expected_header, *, delimiter=",", encoding="utf-8-sig"):
issues = []
seen_ids = set()
seen_rows = set()
if expected_header.count("id") != 1:
raise ValueError("The expected schema must contain exactly one 'id' column.")
with open(path, "r", encoding=encoding, newline="") as stream:
reader = csv.reader(stream, delimiter=delimiter, strict=True)
header = next(reader, None)
if header is None:
return ["The file is empty."]
if header != expected_header:
issues.append(f"Header mismatch: {header!r}")
if len(header) != len(set(header)) or any(not name.strip() for name in header):
issues.append("The header contains a blank or duplicate name.")
if issues:
return issues
id_index = expected_header.index("id")
for record_number, row in enumerate(reader, start=2):
if len(row) != len(expected_header):
issues.append(f"Record {record_number}: found {len(row)} columns.")
continue
row_key = tuple(row)
if row_key in seen_rows:
issues.append(f"Record {record_number}: duplicate row.")
seen_rows.add(row_key)
item_id = row[id_index].strip()
if not item_id:
issues.append(f"Record {record_number}: empty id.")
elif item_id in seen_ids:
issues.append(f"Record {record_number}: duplicate id {item_id!r}.")
seen_ids.add(item_id)
return issues
problems = check_csv(
"import.csv",
["id", "name", "status"],
delimiter=";",
)
for problem in problems:
print(problem)strict=True asks the CSV reader to raise csv.Error on malformed input. Production callers should catch that error and add safe record context. The reader’s line_num counts physical lines consumed, while a quoted record can span multiple physical lines, so a record number and a file line number are not always interchangeable.
Do not reduce the import decision to one green check. Correct format and schema problems first, send key conflicts to the data owner for review, and only then import the clean file. Validation should not delete records or rewrite its input.
Related guide
Need to inspect changes between two exports or reports? See how to compare two text files in Python without modifying them. Byte equality and text equality answer different questions.
Official sources
Disclosure: This article was prepared with AI assistance. Its technical claims were checked against the official Python documentation, and the example was verified with synthetic local data.