DEVELOPMENT NOTES · PYTHON · DATA

Why is comparing two Excel files not always easy?

“There is an old file and a new file, let's find the differences” sounds simple. The real problem starts when the files do not quite match in real life.

One of the things I focused on while working on ExcelPy is the rule for matching two tables that come from different sources. One might say “Account Code” and the other “Customer No”. Comparing two tables without being sure they represent the same information produces wrong results.

1. When the files do not share column names

Comparing rows one by one in order is not reliable. You need to clarify which business field each column represents and, if necessary, let the user choose the mapping. That is a more transparent process than fully automatic guessing.

2. Which field identifies a record?

A customer name can appear twice, a product name can change and there may be empty cells. That is why you should use a unique, stable key field whenever possible. If there is no unique key, we need to define separately when two rows count as “the same record”.

3. Telling types of change apart

  • New record: In the new file but not in the old one.
  • Removed record: In the old file but not in the new one.
  • Changed record: Certain fields of a matched record have different values.
  • Needs review: A duplicate key, a missing column or an ambiguous match.

How dates, currencies, number formats and empty cells are interpreted should also be defined explicitly. “1.000” and “1000” may not mean the same thing in every context.

4. Where does AI fit in?

It can help with suggesting column names, classifying description text or writing a readable summary of the differences. But especially for data with financial consequences, I do not want to remove clear matching rules and user approval.

5. Not every job needs a big system

If the files are well organised, a simple Python script may be enough. If users need to upload files, choose mappings and review results, a web interface starts to make sense. It is important to start from the real need.

A working example: The ExcelPy project page has a video of the real application and screenshots.

Doing the same Excel work by hand every week?

Using anonymised sample files and the output you expect, we can design a small solution first.

Custom software development ↗︎
Gizem Yılmaz
Written by

Gizem Yılmaz

Full-stack developer · Istanbul

I'm a freelance software developer based in Istanbul. I build websites, admin panels and data-processing tools with Laravel, Next.js and Python. In these articles I share the questions I run into on my own projects and the solutions that worked.