What I learned about matching rows between CSV files
Why I started I started building a CSV comparison tool for my own use and to explore a business idea. I wanted to see where I could improve on standard comparison tools. One thing I found frustrating was ambiguous mat
Why I started
I started building a CSV comparison tool for my own use and to explore a business idea. I wanted to see where I could improve on standard comparison tools.
One thing I found frustrating was ambiguous matches. Before comparing values, you need to know which rows belong together.
What makes a match ambiguous?
Here's a made-up example to illustrate the problem.
Suppose these are two exports, and I'm using customer_id to match rows.
First export
customer_id |
item | amount |
|---|---|---|
| 101 | Hosting | 20 |
| 101 | Support | 50 |
Second export
customer_id |
item | amount |
|---|---|---|
| 101 | Support | 55 |
| 101 | Hosting | 20 |
Both files have two rows with customer_id 101. That ID identifies the customer, but it doesn't identify an individual row.
Matching by row order would compare Hosting with Support and report misleading changes. Looking at the item names makes the likely matches clearer, but customer_id alone doesn't provide enough information.
How I approached it
I settled on matching rows by unique keys, then showing additions, removals, and changes.
For the example above, a subscription ID could identify each record:
subscription_id |
customer_id |
item | amount |
|---|---|---|---|
| sub-001 | 101 | Hosting | 20 |
| sub-002 | 101 | Support | 50 |
If those subscription IDs stay the same in the next export, the rows can be matched even when their order changes. The comparison can then show that Support changed from 50 to 55.
The key needs to be unique within each file and identify the same record across both files. A row number might be unique, but it won't reliably identify the same record after rows are reordered.
My approach is to reject duplicate keys rather than guess. The tradeoff is that the person comparing the files needs to find a suitable key or prepare the data first.
What I'm still trying to understand
Tracking rows by unique keys to show additions, removals, and changes is the part I find most useful so far.
- What kinds of datasets do you compare repeatedly?
- How often do you compare them?
- What do you use to identify the same record across exports?
- What do you do with the differences afterward?
I'd be interested in concrete examples, especially where matching gets messy or your current tools fall short.
Written from my own notes, with AI assistance for organization, wording, and the illustrative example.
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.