Compare an Excel File with a Database Table

A customer list lives in a database, and a copy of it lives in Excel where the sales team edits it — fixing phone numbers, changing credit limits, adding new accounts. Before anything is loaded back, someone needs to know exactly what differs: which customers exist only in the spreadsheet, which only in the database, and which fields changed on the rest. Comparing the two naively produces a wall of false differences, because the spreadsheet adds trailing spaces, turns 00412 into 412, shows 1500 where the database stores 1500.00, and holds dates as Excel dates where the database stores text. This page compares a sheet with a table properly with pandas: normalise both sides, join on the key, separate real changes from noise, and produce a highlighted report plus the SQL statements that would apply the changes. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas sqlalchemy openpyxl

crm.db is a SQLite database with a customers table of five customers. customers_edited.xlsx is the sales team's copy: one customer was removed, one added, a few fields edited — and, as always, Excel changed some things nobody edited: the IDs lost their leading zeros, one name has a trailing space, a credit limit carries floating-point noise. The same code works with any database SQLAlchemy can connect to.

Step 1 — Load both sides into the same shape

Read the table and the sheet, and rename the sheet's columns to the database names so the two frames line up:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///crm.db")
db = pd.read_sql("SELECT * FROM customers", engine)
xl = pd.read_excel("customers_edited.xlsx").rename(columns={
    "Customer ID": "customer_id", "Name": "name", "Email": "email",
    "Credit limit": "credit_limit", "Customer since": "since", "Region": "region",
})
print(db.dtypes.to_dict())
print(xl.dtypes.to_dict())

The types already disagree: the database returns the ID and the date as text, Excel gives an integer ID and a datetime. Comparing these columns as they are would report every row as changed. The mapping of column names belongs in one dictionary like this, kept next to the code, so a renamed column in either source shows up as a clear error rather than as silent mismatches.

Step 2 — Normalise both sides

Decide, column by column, what counts as "the same". Then apply exactly the same normalisation to both frames:

def normalise(df):
    out = df.copy()
    out["customer_id"] = out["customer_id"].astype(str).str.strip().str.zfill(5)
    for col in ("name", "region"):
        out[col] = out[col].astype(str).str.strip()
    out["email"] = out["email"].astype(str).str.strip().str.lower()
    out["credit_limit"] = pd.to_numeric(out["credit_limit"]).round(2)
    out["since"] = pd.to_datetime(out["since"]).dt.date
    return out.set_index("customer_id").sort_index()

db_n, xl_n = normalise(db), normalise(xl)
print(xl_n.head(3))

IDs become five-character strings with leading zeros restored, so Excel's 412 matches the database's 00412. Text is trimmed, emails are lower-cased because email addresses are case-insensitive in practice, credit limits are rounded to cents so that 5000.004 equals 5000.00, and dates become plain dates on both sides. Each rule is a business decision: if a change of capitalisation in a name should count as an edit, do not lower-case names. Normalising both sides with the same function is what guarantees they are compared like for like. Leading zeros are discussed further in Keep Leading Zeros When Reading Excel with pandas.

Step 3 — Rows on only one side

An outer join on the key with an indicator column separates the three groups of rows — only in Excel, only in the database, and in both:

joined = db_n.join(xl_n, how="outer", lsuffix="_db", rsuffix="_xl")
in_db = joined.index.isin(db_n.index)
in_xl = joined.index.isin(xl_n.index)
only_xl = joined.index[in_xl & ~in_db].tolist()
only_db = joined.index[in_db & ~in_xl].tolist()
both = joined.index[in_db & in_xl].tolist()
print("only in Excel:", only_xl, "| only in database:", only_db, "| in both:", len(both))

Customer 00502, Greenway Garden Centre, exists only in the spreadsheet — a new account to insert. Customer 00450, Haddad & Sons, exists only in the database — deleted from the sheet, perhaps on purpose, perhaps by accident; a comparison should report it, never act on it silently. The four customers in both are compared field by field next. If either side could contain duplicate keys, check for them before joining, since duplicates multiply rows — see Validate a Merge to Avoid Duplicate Rows.

Step 4 — Field-level differences

For customers on both sides, compare each column and collect every real difference with its old and new value:

fields = ["name", "email", "credit_limit", "since", "region"]
changes = []
for cid in both:
    for f in fields:
        old, new = joined.at[cid, f + "_db"], joined.at[cid, f + "_xl"]
        if old != new:
            changes.append({"customer_id": cid, "field": f, "database": old, "excel": new})
changes = pd.DataFrame(changes)
print(changes.to_string(index=False))

After normalisation only genuine edits remain: Café Señor's credit limit went from 800 to 1,000, Zenith's name gained "Ltd", and Lindqvist's customer-since date moved by a day. The trailing space, the upper-case email, the missing leading zeros and the floating-point noise no longer appear — they were never edits. A long-format list of changes like this, one row per changed field, is easy to review, filter and approve; a wide side-by-side table is easier to scan when many fields change per row, and both come from the same data.

Step 5 — A highlighted report and the SQL it implies

Write the comparison to Excel for review: the sheet's rows with changed cells highlighted, plus the change list and the one-sided rows. Then generate — but do not run — the SQL that would apply the changes:

from openpyxl.styles import PatternFill, Font

report = xl_n.reset_index()
amber, green = PatternFill("solid", fgColor="FFEB9C"), PatternFill("solid", fgColor="C6EFCE")
with pd.ExcelWriter("comparison_report.xlsx", engine="openpyxl") as xw:
    report.to_excel(xw, sheet_name="Excel rows", index=False)
    changes.to_excel(xw, sheet_name="Changes", index=False)
    pd.DataFrame({"customer_id": only_xl, "status": "only in Excel"}).to_excel(xw, sheet_name="One side", index=False)
    pd.DataFrame({"customer_id": only_db, "status": "only in database"}).to_excel(
        xw, sheet_name="One side", index=False, startrow=len(only_xl) + 2)
    ws = xw.sheets["Excel rows"]
    col_of = {c: i + 1 for i, c in enumerate(report.columns)}
    for r, cid in enumerate(report["customer_id"], start=2):
        if cid in only_xl:
            for c in range(1, len(report.columns) + 1):
                ws.cell(r, c).fill = green
        for ch in changes[changes["customer_id"] == cid].itertuples():
            ws.cell(r, col_of[ch.field]).fill = amber
            ws.cell(r, col_of[ch.field]).font = Font(bold=True)

statements = [f"UPDATE customers SET {c.field} = :value WHERE customer_id = :id;  -- {c.database} → {c.excel}"
              for c in changes.itertuples()]
statements += [f"INSERT INTO customers (customer_id, ...) VALUES (:id, ...);  -- {cid}" for cid in only_xl]
print("\n".join(statements))

In the report, the new customer's row is green and each changed cell amber and bold, so a reviewer sees the edits in context; the Changes and One side sheets hold the same information as lists. The generated statements are a preview with bound parameters, not text to paste into a console — once approved, apply them through SQLAlchemy with the values passed as parameters, which also avoids SQL injection from cell contents. Applying changes properly, with an upsert, is covered in Upsert Excel Rows into a Database Table.

Run the comparison again after the changes are applied: a clean result — nothing only on one side, no field differences — is the proof that the database and the spreadsheet now agree.

Common pitfalls

• Comparing raw values. Leading zeros, spaces, case and float noise create false differences; normalise both sides.
• Different normalisation on each side. Use one function for both frames.
• Inner joins. They hide rows that exist on only one side; use an outer join.
• Deleting rows missing from the sheet automatically. Report them for a decision.
• Pasting generated SQL with values inlined. Use bound parameters.

Conclusion

Comparing a spreadsheet with a database table is mostly about deciding what counts as a difference. Load both into frames with the same column names, apply one normalisation function to both — restoring leading zeros, trimming text, rounding numbers, converting dates — and outer-join on the key to find rows on only one side. Compare the remaining fields to list genuine edits with old and new values, write a report with changed cells highlighted for review, and generate parameterised SQL to apply only what has been approved.

Frequently asked questions

How do I compare an Excel sheet with a SQL table in Python? Read the table with pandas read_sql and the sheet with read_excel, rename columns to match, normalise both the same way, then join on the key column and compare each field.

Why does my comparison show differences that are not real? Formatting differences such as missing leading zeros, trailing spaces, letter case, floating-point noise and different date types. Normalise both sides before comparing.

How do I find rows that are only in Excel or only in the database? Join the two frames on the key with an outer join and check which side each key came from; keys present on only one side are new or deleted rows.

Should the comparison update the database automatically? It is safer to produce a report and a preview of the changes for approval, then apply approved changes with parameterised SQL.

Related

• Up one level: Moving Data Between Excel and Databases — getting data in and out.
• Compare Two Excel Files for Differences with Python — the same idea between two workbooks.
• Find Rows in One Excel File Missing from Another — the one-sided rows in depth.
• Query a Database for IDs Listed in an Excel Sheet — fetching only the rows you need.