Compare Two CSV Files by Key with a Reproducible Change Report

To compare two CSV files by key, match each record using a stable identifier, then compare its fields. This produces additions, removals and changes even when the rows or columns were reordered. The tutorial below gives you two synthetic files, a local Python comparison and the expected change report. It preserves identifiers as text and stops on duplicate or blank keys instead of guessing how records should pair.

You need two snapshots of the same dataset and a definition of record identity. The code uses Python 3's standard library. It writes a report to your computer; it does not upload the files, merge their contents or decide which source should become authoritative.

Choose a key that survives between exports

A key identifies the record, rather than its position in the file. Product code, customer ID or an immutable record ID can serve this purpose when it is both unique within a file and stable across snapshots. A row number generated by the export generally describes position, so sorting the file can change it without changing the underlying record.

Write the meaning of one record before choosing the key. An order-line export may require order_id and line_number together. Using only order_id would produce duplicates for orders with several products. If your real data uses a composite key, compare the tuple of fields rather than joining values with an ambiguous separator.

Keep identifiers as text. In this example, 001 and 1 are different keys. If a spreadsheet has already converted both to the same number, the comparison cannot recover the original distinction. Return to the unmodified export or obtain a source that preserves the identifiers.

Decide the field comparison policy separately. Exact text comparison treats 12.00 and 12 as different values. A numerical comparison might treat them as equal, but applying that rule to every column could also change IDs or formatted codes. Normalize only named fields with an agreed business meaning, and preserve both raw and normalized values when the report needs traceability.

Prepare a before-and-after fixture

Save the following fictional inventory snapshot as old.csv:

id,name,price
001,Widget,12.00
002,Gadget,9.00
003,Tool,7.00

Save this second snapshot as new.csv:

price,id,name
7.00,003,Tool
12.50,001,Widget
5.00,004,New item

The second file deliberately reorders both records and columns. Its meaning is unchanged for 003, changed for 001, absent for 002 and new for 004. Comparing lines in their physical order would obscure those four outcomes.

The schemas contain the same column names, so the comparison can align fields by name. If your real files add or rename a column, first decide whether that is a schema change to report or a mapping you intentionally accept. Silently comparing only the intersection of columns can hide a missing field.

Run a local comparison that rejects ambiguous inputs

Python's CSV documentation describes dictionary-based reading and writing. The code below uses it to parse quoted fields and address values by header name. It requires the same schema, one unique nonblank id per record and complete row shapes.

Save the code as compare_csv.py in the same folder as the sample files:

import csv
import json

def load(path):
    with open(path, newline="", encoding="utf-8-sig") as file:
        reader = csv.DictReader(file)
        columns = reader.fieldnames
        if not columns or len(columns) != len(set(columns)):
            raise ValueError(f"{path}: missing or duplicate headers")
        if "id" not in columns:
            raise ValueError(f"{path}: id column required")
        records = {}
        for row_number, row in enumerate(reader, start=2):
            if None in row or any(value is None for value in row.values()):
                raise ValueError(f"{path}:{row_number}: invalid row shape")
            key = row["id"]
            if not key.strip() or key in records:
                raise ValueError(f"{path}:{row_number}: blank or duplicate id")
            records[key] = row
    return columns, records

columns, old = load("old.csv")
new_columns, new = load("new.csv")
if set(columns) != set(new_columns):
    raise ValueError("Schemas differ; define a column mapping first")

added = sorted(new.keys() - old.keys())
removed = sorted(old.keys() - new.keys())
changed = []
unchanged = []
for key in sorted(old.keys() & new.keys()):
    differences = [
        {"id": key, "column": column,
         "before": old[key][column], "after": new[key][column]}
        for column in columns
        if old[key][column] != new[key][column]
    ]
    if differences:
        changed.extend(differences)
    else:
        unchanged.append(key)

report = {"added": added, "removed": removed,
          "changed": changed, "unchanged": unchanged}
with open("comparison.json", "w", encoding="utf-8") as file:
    json.dump(report, file, indent=2)
print(json.dumps(report, indent=2))

Run python3 compare_csv.py. The expected result is:

{
  "added": ["004"],
  "removed": ["002"],
  "changed": [
    {"id": "001", "column": "price", "before": "12.00", "after": "12.50"}
  ],
  "unchanged": ["003"]
}

The calculation was executed against the synthetic fixture. It reports one key in each record category, with one changed cell. Those are fixture results, rather than a claim about the contents of your export or the performance of a hosted service.

Read the change report without confusing absence with deletion

An added key exists only in the new file. A removed key exists only in the old file. A changed key exists in both with at least one different field. An unchanged key exists in both with all compared fields equal.

Those categories describe the snapshots. They do not by themselves explain why a record is missing. A filtered export, a failed extraction or a shorter reporting period can look like deletion. Before applying any removal to a live system, verify that both exports cover the same population and that the new file is complete.

Check the record-level reconciliation:

old record count = removed + changed records + unchanged
new record count = added + changed records + unchanged

In the fixture, both counts are three. Remember that changed cells and changed records are different totals: one record can have several changed fields. The script's changed list holds cell changes, so count distinct IDs when reconciling records.

Keep the old and new files alongside the report and record which file was treated as the baseline. Reversing them swaps additions and removals and reverses before/after values. A report filename alone does not establish that orientation.

Check the cases that can invalidate a comparison

The sample validation also checked duplicate IDs, a blank ID and a quoted comma in a description. Duplicate and blank keys were rejected; the quoted description was parsed as a single field. Run these checks on any modified version before using it for a migration or recurring audit.

If duplicate keys are legitimate, revisit the grain. An inventory dataset may be unique by warehouse plus SKU, while a national total may be unique by SKU alone. A comparison that silently pairs duplicates in file order uses a different rule from stable record identity. Choose that rule only if it matches your task and you can explain the pairing.

Missing values also need an explicit meaning. This code treats an empty string as a value for non-key fields, so changing blank to a nonblank value appears in the report. A missing CSV column or a row with too few fields is rejected. Do not collapse a missing column into a blank cell without deciding that the distinction is irrelevant.

Before running a real comparison, save a note identifying the baseline export, replacement export, population filters and selected fields. For a migration check, both files should describe the same moment or an agreed cutover window. If data changed between exports, distinguish those legitimate changes from transformation errors. That context makes a technically correct diff interpretable to the person who must approve it.

For unusually large exports, this small implementation loads both datasets into memory. Test the required size on the intended machine or use a database or a streaming comparison designed for that scale. No file-size or runtime guarantee follows from the small fixture.

Choose a one-off tool or build a recurring review workflow

For an occasional comparison, an existing tool may be enough. CSVDock's comparison page describes key matching, exact text comparisons and rejected incomplete inputs. Read the tool's current limits and processing policy, then try the synthetic fixture before using private data. This tutorial did not execute that hosted tool.

If the same comparison drives a recurring approval, the useful next step is a review workflow: choose the files, display schema and coverage warnings, inspect changed values, record a reviewer decision and export the approved result. Comparison and approval should remain separate actions.

Use the AI App Builder to describe those screens and the exact comparison rules. Ask for a sample-file option, preserved text keys, blocked duplicate keys and a downloadable report. Verify the generated result against the fixture, then test persistence and permission enforcement if review decisions are saved. The local comparison proves the sample's logic; it does not prove a generated app's storage or access control.

For a broader migration, use the spreadsheet to web app guide to define the system of record and repeat-import policy before applying updates.

Conclusion

Match stable keys, compare named fields and reconcile record categories before acting on a CSV diff. Keep schema changes and incomplete exports visible. If you need a repeatable review application, give Atoms the checked fixture and change-report contract, then verify the generated workflow independently.