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.