deploy notes
Automation

Compare two CSV files with Python and find changed records

Compare small exports by a unique ID, report added, removed, and changed records, and avoid mistaking a new row order for a data change.

Beginner5 min read
In this guide

Two exports can look different simply because their rows are in a different order. To find actual changes, match records by an ID first, then compare their values.

This guide creates a readable report of added, removed, and changed records. It leaves both CSV files untouched. A useful example is checking yesterday’s order export against today’s before updating another system.

Decide what counts as one record

Choose a column that uniquely identifies one record, such as order_id in a file with one row per order. If an order can have several product rows, use a unique line-item ID instead. Names are not reliable IDs.

Both files must be small enough to fit in memory, use UTF-8 and commas, and have the same column names. Column order may differ. Values and IDs are compared as exact text: 001 differs from 1, and paid differs from Paid. This avoids silently changing your matching rule.

You need Python 3.12 or newer, a text editor, and a terminal. Check py --version in Windows PowerShell or python3 --version in macOS/Linux Terminal. Install it from Python’s downloads page if needed, then reopen the terminal.

Put the two files in one folder

Create a folder named csv-compare. Copy your earlier and later exports into it as before.csv and after.csv. Or save these fictional examples as UTF-8 plain text.

before.csv:

order_id,item,status
1001,Notebook,pending
1002,Mug,paid

after.csv:

order_id,item,status
1003,Pencil,paid
1001,Notebook,paid

Here, order 1001 changed status, 1002 disappeared, and 1003 appeared. The report should identify one of each; it should not treat the reordered rows as replacements.

Save the comparison script

Save this as compare_csv.py in the same folder. Use plain text and check that your editor has not added .txt to the filename.

import argparse
import csv
import json
from pathlib import Path

parser = argparse.ArgumentParser(description="Compare CSV records by unique ID.")
parser.add_argument("before")
parser.add_argument("after")
parser.add_argument("--key", required=True)
parser.add_argument("--report", required=True)
args = parser.parse_args()

def load(path):
    records = {}
    with open(path, encoding="utf-8-sig", newline="") as source:
        reader = csv.reader(source, strict=True)
        header = next(reader, None)
        if not header or any(not name.strip() for name in header):
            raise ValueError(f"{path}: missing column names.")
        if len(header) != len(set(header)) or args.key not in header:
            raise ValueError(f"{path}: repeated names or missing key column.")
        key_index = header.index(args.key)
        for row in reader:
            if not row:
                continue
            if len(row) != len(header):
                raise ValueError(f"{path}: wrong cell count near line {reader.line_num}.")
            key = row[key_index]
            if not key.strip() or key in records:
                raise ValueError(f"{path}: blank or repeated ID near line {reader.line_num}.")
            records[key] = dict(zip(header, row))
    return header, records

try:
    if Path(args.report).resolve() in {Path(args.before).resolve(), Path(args.after).resolve()}:
        raise ValueError("Report must not be either input file.")
    old_header, old = load(args.before)
    new_header, new = load(args.after)
    if set(old_header) != set(new_header):
        raise ValueError("The files have different column names.")
    added = sorted(new.keys() - old.keys())
    removed = sorted(old.keys() - new.keys())
    changed = sorted(key for key in old.keys() & new.keys() if old[key] != new[key])
    lines = [f"Added: {len(added)}; removed: {len(removed)}; changed: {len(changed)}."]
    for key in added:
        lines.append(f"ADDED {key}: {json.dumps(new[key], ensure_ascii=False)}")
    for key in removed:
        lines.append(f"REMOVED {key}: {json.dumps(old[key], ensure_ascii=False)}")
    for key in changed:
        lines.append(f"CHANGED {key}:")
        for column in old_header:
            if old[key][column] != new[key][column]:
                lines.append(f"  {column}: {old[key][column]!r} -> {new[key][column]!r}")
    with open(args.report, "x", encoding="utf-8") as target:
        target.write("\n".join(lines) + "\n")
    print(lines[0])
    print("Open the report to inspect the actual values.")
except (OSError, ValueError, csv.Error) as error:
    parser.exit(1, f"Stopped: {error}\n")

The script rejects repeated IDs rather than picking whichever row happens to appear last. A comparison would otherwise hide an ambiguous match.

Run it once

Open a terminal in csv-compare. In Windows File Explorer, type powershell in the folder’s address bar. In macOS/Linux Terminal, type cd , drag in the folder, and press Enter.

Windows PowerShell:

py compare_csv.py before.csv after.csv --key order_id --report changes.txt

macOS/Linux:

python3 compare_csv.py before.csv after.csv --key order_id --report changes.txt

Replace order_id with your actual unique column name. Keep quotation marks around names with spaces, for example --key "Order ID". before means the earlier snapshot; reversing the two files reverses what is considered added or removed.

Read the result before acting on it

For the example, the terminal reports:

Added: 1; removed: 1; changed: 1.
Open the report to inspect the actual values.

Open changes.txt. Under CHANGED 1001, you should see status: 'pending' -> 'paid'. The added and removed entries include their full records so you can identify them without searching the source again.

Removed means absent from the later file, not necessarily deleted from the real system. A filtered export, date range, or failed export can also remove records from a snapshot. Confirm both exports cover the same scope before changing orders or deleting anything elsewhere.

For your own files, manually check at least one reported change against both inputs. A report with all counts zero means the records match under these exact-text rules, even if row or column order changed.

When the script stops

  • Repeated ID: inspect both rows and choose a genuinely unique key. Do not delete one just to make the comparison run.
  • Different column names: export the same fields in both snapshots. Renaming a field is not the same as changing a record.
  • Wrong cell count: use a comma-separated export with proper quoting. A comma inside an item name needs quotes around that cell.
  • Encoding error: obtain UTF-8 exports rather than discarding unreadable characters.
  • Report already exists: use a new name, such as changes-2.txt.

There is no background process to stop. You now have an inspection report, not an automatic synchronization tool. For repeated rows in a single file, the duplicate-checking guide handles that separate task.

← Back to automation guides