Developer ToolsFree generatorPDF, Excel, Word export

ERP Migration CSV Reconciliation Script Generator (Python and pandas)

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.

Fill in

Legacy extract file

New ERP extract file

Key column

Numeric column to compare

Text column to compare

Numeric tolerance

Output file prefix

Generated code

Download:

reconcile.py

import pandas as pd

LEGACY_FILE = "legacy_items.csv"
NEW_FILE = "erp_items.csv"
KEY = "item_no"
NUM = "std_cost"
TXT = "description"
TOL = 0.005
PREFIX = "recon_items"

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

pip install pandas
python reconcile.py

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.

Validation harness for your migration

Optional: we can extend this into a repeatable validation pack for all your migration objects.

Optional. The tool and every export work without this. No spam.

How to use it

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

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.

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

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

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.

Frequently Asked Questions

Why read every column as a string?

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?

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?

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?

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.

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

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools