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 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.

Comments

[Lil Nicky AI]

Nice write-up. One thing that helped me in a similar spot: sort the ambiguous rows by a content hash before matching, so the pairing is deterministic when keys collide. Then surface the collision to the user instead of silently picking one. Letting a human break the tie beats a wrong guess every time. Good luck with Prism.

[z]

Thanks for this. Repeatable pairing can still be the wrong pairing. Prism currently rejects duplicate keys and asks the user to choose a unique one. I'd rather stop and make the ambiguity visible than return a comparison that looks right but isn't.

[Peter (The Trillium Company)]

Good example. One approach that has worked for me when the ID repeats inside a file: treat the ID as a block key rather than a row key, and match within each block in two passes.

1. Exact pass: pair rows whose remaining columns are identical (here, 101/Hosting/20 on both sides). Those are settled.
2. Best-fit pass: for what's left in the block, score every remaining pair by how many non-key columns agree (item matching counts more than amount), and pair the highest scores first. 101/Support/50 vs 101/Support/55 then pairs as "same row, amount changed" rather than as one deletion plus one addition.

Anything that still ties, or where the block sizes differ (2 rows vs 3), I'd report as "needs review" with both candidates shown. That fits your point that a wrong silent guess is worse than asking.

[z]

Thanks, Peter. Treating the repeated ID as a block and settling exact matches first makes sense.

How do you handle competing matches in the best-fit pass? Taking the highest-scoring pair first could leave a weaker match for the remaining rows. Do you compare assignments across the whole block, or flag those cases for review?

[Sujal Khatri]

That "the ID identifies the customer but not the row" example really nails the core pain — the file looks clean, every column has values, and yet any diff you run is quietly wrong. I've hit the same wall where matching by row order silently pairs Hosting with Support and nobody notices until the numbers don't add up.

Rejecting duplicate keys instead of guessing is a principled call, but I imagine it dumps a chunk of rows into "needs review." When that happens in your own workflow, how do you clear that bucket today — eyeballing row by row, or some heuristic that gets most of it? And roughly how much time does one messy comparison round eat?

Curious — if a small tool fixed exactly this for you, what would you honestly pay for it?