Compare Two CSV Files in Python: Find Added and Missing IDs

Confirm that the two exports are comparable

Use snapshots with the same filters and identifier definition. A customer missing from an active-only export might have become inactive, not been deleted. A timezone boundary or a changed branch filter can also change membership without any underlying data loss.

Record which file is older and what each export includes. In this example, both files describe a product catalog, and id is unique within each snapshot. Leading zeros are meaningful: 00123 is a string key, not the number 123.

Save the two small files and the script

Create a working folder and save each CSV block without its filename label. Save the code below as compare_ids.py in that folder. It uses Python 3 and the standard library; it does not upload files or require a spreadsheet application.

The required key heading is exactly id. Change the heading deliberately in your working copies if your source uses another name. Do not rename a non-unique field to id merely to make the script run. That would change the meaning of the comparison.

import csv, json, sys

def ids(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        rows = csv.reader(f, strict=True)
        header = next(rows, None)
        if not header or any(not h.strip() for h in header):
            raise ValueError(f"{path}: missing or blank header")
        if len(header) != len(set(header)) or header.count("id") != 1:
            raise ValueError(f"{path}: unique headers including id required")
        col = header.index("id")
        result = set()
        for record, row in enumerate(rows, 2):
            if len(row) != len(header):
                raise ValueError(f"{path}: record {record} has wrong width")
            key = row[col]
            if not key.strip() or key in result:
                raise ValueError(f"{path}: blank or duplicate id at {record}")
            result.add(key)
        return result

if len(sys.argv) != 3:
    raise SystemExit("Usage: python compare_ids.py old.csv new.csv")
old, new = map(ids, sys.argv[1:])
report = {"added": sorted(new - old), "missing": sorted(old - new),
          "common": sorted(old & new)}
print(json.dumps(report, ensure_ascii=False, indent=2, allow_nan=False))

Run the comparison and read the output

Open a terminal in the working folder and run the command below. On Windows, py may be the installed Python launcher; use py instead of python if that is how your installation is configured. The command only reads the two input files and prints a report.

The lists are sorted as text to make the report repeatable. Their order is for presentation; it does not imply numeric ranking or the order in which changes occurred. Keep the report together with the two source filenames and export settings.

1. Run python compare_ids.py old.csv new.csv.
2. Confirm that the output contains one added ID, one missing ID and one common ID.
3. Inspect the original row for each added or missing ID before taking action in another system.

{
  "added": ["00789"],
  "missing": ["00123"],
  "common": ["00456"]
}

Understand the checks before fixing rejected data

A repeated id is rejected because the example promises one record per product. If your exports contain one row per order line, an order ID alone is not unique enough. Use a documented composite key, such as order ID plus line number, in a revised comparison.

Whitespace is preserved rather than silently removed. An ID of 00456 followed by a space is therefore different from 00456. Investigate that discrepancy at the source. Normalization can be appropriate, but applying it changes identity and should be the same explicit rule on both files.

Related help: Inspect hidden spaces before normalizing comparison keys

Separate membership changes from value changes

After the ID sets are reliable, you can compare selected fields for common IDs. Define which fields matter and whether blank, missing and whitespace-only values are equivalent. A price change needs different treatment from a renamed display label.

Do not append the two snapshots and remove duplicate rows as a shortcut. The revised name would make 00456 appear as two different full rows even though the product identity is shared. That approach answers a different question.

Know the script boundaries

The script assumes comma-separated UTF-8 input, accepts a UTF-8 byte-order mark and checks record width. It keeps the ID sets in memory, so test on a representative copy before using it for very large exports. It is not an encoding detector or a general data validator.

Only the synthetic files and rejection cases described in the verification note were run here. The result does not establish that an export is complete. Column Harbor does not run this comparison in its browser checker; run the provided script locally when that matches your task.