Find whitespace and case-variant duplicates in a CSV column with Python (stdlib only)

Messy whitespace and inconsistent capitalisation are two of the most common reasons a CSV import produces duplicates or silently fails to match. blue-shirt and blue-shirt (trailing space) look identical in a spreadsheet, but a system comparing strings sees two different values.

This small script checks one column of a CSV and reports two things:

1. values with leading or trailing whitespace
2. values that are the same once you ignore case and extra spaces, but are spelled differently

It uses only Python's standard library.

The script

import csv, sys
from collections import defaultdict

def check(path, column):
    seen = defaultdict(list)
    problems = []
    with open(path, newline="", encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        if column not in (reader.fieldnames or []):
            sys.exit(f"Column {column!r} not found. Columns: {reader.fieldnames}")
        for line_no, row in enumerate(reader, start=2):
            value = row[column]
            if value != value.strip():
                problems.append((line_no, "leading/trailing whitespace", repr(value)))
            key = " ".join(value.split()).casefold()
            if key:
                seen[key].append((line_no, value))
    for key, hits in seen.items():
        variants = {v for _, v in hits}
        if len(hits) > 1 and len(variants) > 1:
            lines = ", ".join(str(n) for n, _ in hits)
            problems.append((hits[0][0], f"same value, different spelling (lines {lines})", repr(sorted(variants))))
    return sorted(problems)

if __name__ == "__main__":
    if len(sys.argv) != 3:
        sys.exit("usage: python ws.py file.csv column")
    res = check(sys.argv[1], sys.argv[2])
    for line, kind, detail in res:
        print(f"line {line}: {kind}: {detail}")
    print(f"{len(res)} problem(s) found")

Example

Given this file:

handle,title
blue-shirt,Blue Shirt
blue-shirt ,Blue shirt
red-hat,Red Hat
Red-Hat,Red hat
socks,Socks

Running python ws.py t.csv handle prints:

line 2: same value, different spelling (lines 2, 3): ['blue-shirt', 'blue-shirt ']
line 3: leading/trailing whitespace: 'blue-shirt '
line 4: same value, different spelling (lines 4, 5): ['Red-Hat', 'red-hat']
3 problem(s) found

If the column name does not exist, it stops and lists the available columns instead of failing with a stack trace.

Notes

• utf-8-sig handles the BOM that Excel adds when it saves CSV as UTF-8.
• Line numbers count the header as line 1, so they match what you see in a text editor (assuming no multi-line quoted fields).
• casefold() is used instead of lower() because it handles more Unicode cases.
• The script only reports problems; it never changes your file.