# ERP Migration CSV Reconciliation Script Generator (Python and pandas)

> Source: https://netray.co/tools/erp-migration-file-reconciliation-python-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a pandas script that compares two CSV extracts by key and writes missing, extra and mismatch files.

## Category

developer

## Audience

- Data migration lead
- ERP developer
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** python

**Fields:**

#### Legacy extract file

**Key:** legacy_file

**Type:** text

**Default:** legacy_items.csv

#### New ERP extract file

**Key:** new_file

**Type:** text

**Default:** erp_items.csv

#### Key column

**Key:** key_col

**Type:** text

**Default:** item_no

#### Numeric column to compare

**Key:** num_col

**Type:** text

**Default:** std_cost

#### Text column to compare

**Key:** text_col

**Type:** text

**Default:** description

#### Numeric tolerance

**Key:** tolerance

**Type:** number

**Default:** 0.005

#### Output file prefix

**Key:** out_prefix

**Type:** text

**Default:** recon_items

**Blocks:**

#### reconcile.py

**Body:** import pandas as pd

LEGACY_FILE = "{{legacy_file}}"
NEW_FILE = "{{new_file}}"
KEY = "{{key_col}}"
NUM = "{{num_col}}"
TXT = "{{text_col}}"
TOL = {{tolerance}}
PREFIX = "{{out_prefix}}"

legacy = pd.read_csv(LEGACY_FILE, dtype=str).fillna("")
new = pd.read_csv(NEW_FILE, dtype=str).fillna("")

for name, df in (("legacy", legacy), ("new", new)):
    df[KEY] = df[KEY].str.strip().str.upper()
    dupes = df[df.duplicated(KEY, keep=False)]
    if not dupes.empty:
        dupes.to_csv(PREFIX + "_" + name + "_duplicate_keys.csv", index=False)
        print(name, "has duplicate keys:", dupes[KEY].nunique())

merged = legacy.merge(new, on=KEY, how="outer", suffixes=("_legacy", "_new"), indicator=True)

missing = merged[merged["_merge"] == "left_only"]
extra = merged[merged["_merge"] == "right_only"]
both = merged[merged["_merge"] == "both"].copy()

both["num_legacy"] = pd.to_numeric(both[NUM + "_legacy"], errors="coerce")
both["num_new"] = pd.to_numeric(both[NUM + "_new"], errors="coerce")
num_bad = both[(both["num_legacy"] - both["num_new"]).abs() > TOL]
txt_bad = both[both[TXT + "_legacy"].str.strip() != both[TXT + "_new"].str.strip()]

missing.to_csv(PREFIX + "_missing_in_new.csv", index=False)
extra.to_csv(PREFIX + "_extra_in_new.csv", index=False)
num_bad.to_csv(PREFIX + "_numeric_mismatch.csv", index=False)
txt_bad.to_csv(PREFIX + "_text_mismatch.csv", index=False)

print("Legacy rows:", len(legacy), "New rows:", len(new))
print("Missing in new:", len(missing))
print("Extra in new:", len(extra))
print("Numeric mismatches:", len(num_bad))
print("Text mismatches:", len(txt_bad))

#### Run it

**Body:** pip install pandas
python reconcile.py

**Notes:** Assumes both files are CSV with a header row and a single unique key column. Rename columns that differ between systems before the merge. Composite keys need a combined key column. Test on a small sample before running on a full extract.

## Content

**Intro:** Not every migration check can be done in SQL. Often you have a legacy CSV export and an extract from the new ERP and need to prove they match. This generator writes a short pandas script that does a keyed comparison and writes one file per kind of difference.

**Sections:**

#### How to use it

**Body:** Set the two file names, the key column, one numeric and one text column to compare, and a tolerance. Copy the script to a file, install pandas and run it in the folder holding both extracts. The script prints counts and writes CSV files for rows missing in the new system, extra rows, numeric mismatches and text mismatches, which you can hand directly to the data owners.

#### What the script does

**Body:** It reads everything as text to avoid pandas changing leading zeros, normalizes the key by trimming and uppercasing it, flags duplicate keys, and then performs an outer merge with an indicator column. The indicator separates records that exist on one side only from records on both sides, which are then compared field by field.

**Bullets:**

- Strings preserve part numbers like 000123
- Duplicate keys are written out before they distort the merge
- Numeric compare uses a tolerance, not exact equality
- Output files are small enough to review by hand

#### Reading the output

**Body:** Missing rows usually mean a load rule rejected a record, such as an invalid unit of measure. Extra rows suggest the new system created defaults or you loaded test data. Numeric mismatches clustered on one value often point to a unit or currency conversion. Text mismatches commonly come from field length truncation.

#### Common mistakes

**Body:** Comparing without normalizing keys creates false missing rows because of case or trailing spaces. Reading numbers as floats can hide leading zeros and create phantom differences. Extend the script column by column rather than comparing everything blindly, and keep each rehearsal output as evidence for sign-off.

**Cta:** Need this extended to every migration object with a dashboard of results? Netray can build a repeatable validation harness for your ERP.

**Faq:**

#### Why read every column as a string?

**Answer:** Reading as text prevents pandas from dropping leading zeros or converting codes to numbers. You then convert only the columns that are truly numeric, which keeps part numbers and account codes intact.

#### Can I compare more than one column?

**Answer:** Yes. Copy the numeric or text comparison lines and change the column name for each field you need. For many columns, loop over a list of names and append the results to the mismatch files.

#### Will it handle large files?

**Answer:** pandas handles millions of rows on a typical workstation if memory allows. For very large extracts, compare in chunks by key range or run the comparison in the database instead using the SQL generator.

#### What about composite keys?

**Answer:** Create a combined key column in both files first, for example plant plus item, and use that column as the key. The merge then behaves exactly as with a single key.

## Search terms

compare two csv files python data migration, pandas reconcile legacy and new ERP data, data migration validation script, csv diff by key python, ERP migration reconciliation script, find missing rows between two csv files

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json