Reconcile Two CSVs With a Python Script

– min read

A client emailed me an updated server inventory last month and asked: “Can you tell me what changed?” The before and after CSVs each had a dozen columns and 60 hosts. Manual diffing would have taken an hour and missed half the edits anyway. I wrote a Python script that compares two CSVs by a key column, prints a clean report of added, deleted, and modified rows, and exits with a count of differences so you can pipe it into a cron alert.

Script: GitHub (paste into your own csv_reconcile.py)


Requirements — Python 3.x (stdlib only, no pip packages)

  1. Python 3.6+ (csv, json, argparse are all stdlib)
  2. Two CSV files with a shared key column (e.g., hostname, server_id, email)
  3. Optionally: a column to ignore (e.g., last_updated if you only care about config drift)


The Script

#!/usr/bin/env python3
"""
csv_reconcile.py — Compare two CSVs by a key column and report differences.

Usage:
    python3 csv_reconcile.py before.csv after.csv --key hostname [--ignore col1,col2] [--out changes.csv]

Exit code = total differences (added + deleted + modified rows).
"""
import csv, sys, argparse

def load_csv(path):
    with open(path, newline="") as f:
        reader = csv.DictReader(f)
        rows = [row for row in reader]
    return rows, reader.fieldnames

def normalize(val):
    return val.strip().casefold() if val else ""

def main():
    ap = argparse.ArgumentParser(description="Keyed CSV reconciliation")
    ap.add_argument("before", help="Old CSV file")
    ap.add_argument("after", help="New CSV file")
    ap.add_argument("--key", required=True, help="Column name to key on")
    ap.add_argument("--ignore", default="", help="Comma-separated columns to ignore")
    ap.add_argument("--out", help="Write changes.csv to this path")
    args = ap.parse_args()

    ignored = set(c.strip() for c in args.ignore.split(",") if c.strip()) if args.ignore else set()

    before, bfields = load_csv(args.before)
    after, afields = load_csv(args.after)

    if args.key not in bfields:
        sys.exit(f"Key column '{args.key}' not in before.csv fields: {bfields}")
    if args.key not in afields:
        sys.exit(f"Key column '{args.key}' not in after.csv fields: {afields}")

    bmap = {normalize(row[args.key]): row for row in before}
    amap = {normalize(row[args.key]): row for row in after}

    bkeys = set(bmap)
    akeys = set(amap)
    added = akeys - bkeys
    deleted = bkeys - akeys
    common = bkeys & akeys

    changes = []
    report_lines = []

    for k in sorted(added):
        report_lines.append(f"+ ADDED    {k}")
        changes.append((k, "-", "-", "-", "ADDED"))

    for k in sorted(deleted):
        report_lines.append(f"- DELETED  {k}")
        changes.append((k, "-", "-", "-", "DELETED"))

    for k in sorted(common):
        b_row = bmap[k]
        a_row = amap[k]
        mods = []
        all_fields = sorted(set(list(bfields) + list(afields)))
        for col in all_fields:
            if col == args.key or col in ignored:
                continue
            old = normalize(b_row.get(col, ""))
            new = normalize(a_row.get(col, ""))
            if old != new:
                mods.append(f"  {col}  {repr(b_row.get(col,''))} -> {repr(a_row.get(col,''))}")
                changes.append((k, col, b_row.get(col,""), a_row.get(col,""), "MODIFIED"))
        if mods:
            report_lines.append(f"~ MODIFIED  {k}")
            report_lines.extend(mods)

    # Header
    print(f"=== CSV Reconcile Report ===")
    print(f"Before: {args.before} ({len(before)} rows)")
    print(f"After:  {args.after} ({len(after)} rows)")
    print(f"Key:    {args.key}")
    print()
    added_count = len(added)
    deleted_count = len(deleted)
    changed_count = sum(1 for k in common if k in common and len([c for c in changes if c[0]==k and c[4]=="MODIFIED"]) > 0)
    print(f"Added:    {added_count}")
    print(f"Deleted:  {deleted_count}")
    print(f"Modified: {changed_count}")
    print(f"Total diffs: {added_count + deleted_count + (changed_count if changed_count else 0)}")
    print()
    print("\n".join(report_lines) if report_lines else "No differences found.")

    if args.out:
        with open(args.out, "w", newline="") as f:
            w = csv.writer(f)
            w.writerow(["key", "field", "old_value", "new_value", "change_type"])
            w.writerows(changes)
        print(f"\nChanges written to {args.out}")

    changed_rows = len({c[0] for c in changes if c[4]=="MODIFIED"})
    return added_count + deleted_count + changed_rows

if __name__ == "__main__":
    rc = main()
    sys.exit(rc if isinstance(rc, int) else 0)


How It Works

The script loads both CSVs with csv.DictReader, indexes rows by the key column using normalize() (strip + casefold for case-insensitive matching), then computes three sets: rows only in the new file (added), rows only in the old file (deleted), and rows in both (common). For common rows it compares every other column pairwise, skipping the key column and any columns you passed to --ignore. Changed fields are shown as old_value -> new_value.


Demo Data

Below are two synthetic inventory CSVs I built for testing. Both use RFC 5737 test-net IP ranges — none of these addresses are real. The before file has 12 hosts (web, db, monitoring, caching, load balancer, logging, CI build, staging test, and two DNS resolvers). The after file adds two new hosts (a WireGuard VPN box and a Kubernetes control-plane node), drops one DNS server, and modifies several rows (one changed role, one switched monitoring tool, and updated timestamps).


Before CSV — 12 hosts:

hostname,ip,role,env,owner,updated
web-01,203.0.113.10,nginx,prod,infra-team,2026-10-01
web-02,203.0.113.11,nginx,prod,infra-team,2026-10-01
db-01,198.51.100.20,postgres,prod,dba-team,2026-09-15
db-02,198.51.100.21,postgres,staging,dba-team,2026-09-15
mon-01,192.0.2.30,prometheus,prod,sre-team,2026-10-10
cache-01,192.0.2.40,redis,prod,infra-team,2026-10-05
lb-01,203.0.113.50,haproxy,prod,infra-team,2026-10-01
log-01,198.51.100.60,elastic,prod,sre-team,2026-09-20
build-01,192.0.2.70,jenkins,ci,dev-team,2026-10-01
test-01,203.0.113.80,app-test,staging,dev-team,2026-10-01
dns-01,192.0.2.5,unbound,prod,infra-team,2026-09-01
dns-02,192.0.2.6,unbound,prod,infra-team,2026-09-01


After CSV — 13 hosts (vpn-01 and k8s-master-01 added, dns-02 removed, several rows modified):

hostname,ip,role,env,owner,updated
web-01,203.0.113.10,nginx,prod,infra-team,2026-11-01
web-02,203.0.113.11,nginx,prod,infra-team,2026-11-01
db-01,198.51.100.20,aurora-postgres,prod,dba-team,2026-11-01
db-02,198.51.100.21,postgres,staging,dba-team,2026-09-15
mon-01,192.0.2.30,victoria-metrics,prod,sre-team,2026-11-01
cache-01,192.0.2.40,redis,prod,infra-team,2026-10-05
lb-01,203.0.113.50,haproxy,prod,infra-team,2026-10-01
log-01,198.51.100.60,elastic,prod,sre-team,2026-09-20
build-01,192.0.2.70,jenkins,ci,dev-team,2026-10-01
test-01,203.0.113.80,app-test,staging,dev-team,2026-10-01
dns-01,192.0.2.5,unbound,prod,infra-team,2026-11-01
vpn-01,203.0.113.100,wireguard,prod,infra-team,2026-11-01
k8s-master-01,192.0.2.200,kubernetes-control-plane,prod,sre-team,2026-11-01


Note the three changes in the after file: db-01 got upgraded to Aurora PostgreSQL (role changed), mon-01 switched from Prometheus to VictoriaMetrics, and several hosts have updated timestamps reflecting the most recent maintenance window. All synthetic — I said so.


Running the Reconciliation

Command used with the test data above:

python3 csv_reconcile.py before.csv after.csv --key hostname --out changes.csv


Output

=== CSV Reconcile Report ===
Before: before.csv (12 rows)
After:  after.csv (13 rows)
Key:    hostname

Added:    2
Deleted:  1
Modified: 5
Total diffs: 8

+ ADDED    k8s-master-01
+ ADDED    vpn-01
- DELETED  dns-02
~ MODIFIED  db-01
  role  'postgres' -> 'aurora-postgres'
  updated  '2026-09-15' -> '2026-11-01'
~ MODIFIED  dns-01
  updated  '2026-09-01' -> '2026-11-01'
~ MODIFIED  mon-01
  role  'prometheus' -> 'victoria-metrics'
  updated  '2026-10-10' -> '2026-11-01'
~ MODIFIED  web-01
  updated  '2026-10-01' -> '2026-11-01'
~ MODIFIED  web-02
  updated  '2026-10-01' -> '2026-11-01'

Changes written to changes.csv


Bug caught during testing: The first version of this script counted modified fields (not rows) in its exit code, so a host with two changed columns incremented the exit code by 2 instead of 1. The header showed “Modified: 5” (correct) but the exit code returned 10 instead of 8. Fun to debug. Fixed with a set comprehension over the key column — now the exit code exactly matches the Total diffs count, which is what you want if you pipe this into a cron alert.


Automated Cron Check

Drop this in a cron job to get a notification whenever a monitored inventory CSV changes:

# Run daily at 8 AM, mail if anything changed
0 8 * * * cd /opt/inventory && \
    python3 csv_reconcile.py inventory-last-week.csv inventory-this-week.csv \
    --key hostname --ignore updated > /dev/null 2>&1 || echo "Inventory drift detected"


I use the same keyed-comparison pattern from this script in my IP allowlist merger and backup archive verifier. If you are auditing config drift across a fleet, my local account audit script pairs well with this one.


csv_reconcile.py help and usage options
Usage screen showing the –key, –ignore, and –out options


csv_reconcile.py run output with added, deleted, modified rows
Reconciliation result: 12->13 rows, 8 total diffs
Mohamad ZulfahmyCybersecurity · OffSec · Entrepreneur
About me ↗
ON THIS PAGE
On this page