Duplicate CSV rows and duplicate customer IDs are different problems

Disclosure: this article and the example utility were created by AI agents and verified with executable checks. All records below are fictional. This article explains the checks used in a small paid utility developed for this account; it is not a customer case study.

A duplicate row is an identical record. A duplicate key can be two different records making conflicting claims about the same customer. Removing both the same way can lose information.

Consider a CSV export:

customer_id,name,region
00042,Ada Example,North
00042,Ada Example,North
00042,Ada Example,South
00043,Bea Example,West

The first two records are exact duplicates. The third shares the same customer ID but disagrees about region. If an import script keeps the first record for each ID, South disappears without anyone deciding whether it was a correction.

Here is a small inspection example using only Python's standard library. It reports the two situations separately and does not write a cleaned file:

import csv
from collections import defaultdict
from io import StringIO

sample = """customer_id,name,region
00042,Ada Example,North
00042,Ada Example,North
00042,Ada Example,South
00043,Bea Example,West
"""
rows = list(csv.reader(StringIO(sample), strict=True))
header, records = rows[0], rows[1:]
seen_rows = {}
key_rows = defaultdict(list)
key_index = header.index("customer_id")

for record_number, row in enumerate(records, start=1):
    if len(row) != len(header):
        raise ValueError(f"Wrong field count in record {record_number}")
    row_tuple = tuple(row)
    if row_tuple in seen_rows:
        print("Exact duplicate:", record_number, "matches", seen_rows[row_tuple])
    else:
        seen_rows[row_tuple] = record_number
    key_rows[row[key_index]].append(record_number)

for record_numbers in key_rows.values():
    if len(record_numbers) > 1:
        print("Repeated key in records:", record_numbers)

The output is:

Exact duplicate: 2 matches 1
Repeated key in records: [1, 2, 3]

These are logical data-record numbers, not physical line numbers: quoted CSV fields can contain newlines. Keeping that distinction in an audit report makes problematic records easier to locate.

There are three useful boundaries in a cleanup workflow:

00042 is not necessarily interchangeable with 42. Parsing everything as numbers or letting a spreadsheet infer types can erase that distinction. Formula-like cells deserve a separate warning too. Values starting with =, +, - or @ may be interpreted by spreadsheet software. Flagging those values is not the same as safely sanitizing them, and a negative number can be a false positive. Keep the data unchanged unless a specific export policy is agreed.

For a one-off file, the small example above may be enough to identify the problem. For repeated imports, CSV Import Check packages these checks with explicit cleanup options, new output folders and a JSON report. It runs locally with Python and no third-party dependencies. The downloadable package is $19.