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.
Top comments (13)
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.
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.
Fair point, and I agree with your instinct: a deterministic wrong answer is worse than an honest stop. One middle path that keeps the honesty while unblocking the user: when Prism rejects the duplicate keys, suggest the smallest change that would make them unique, like 'adding the amount column would make these unique'. That turns a dead stop into a concrete next step, and the user stays in control of the decision.
That could give the user somewhere to go next. I’d be careful with amount as part of the key if it’s also a value you’re comparing: when it changes, the same row can look like a different one. I’d want to show which column combinations are unique in both files, then let the user decide whether they actually identify the same row.
You are right, that distinction matters. A key should stay stable while the values around it change, so using amount as part of the key can turn a changed row into a new row. One concrete way to handle it: keep amount out of the key, use it only as a compared value after a match, and show how many rows share each candidate key before the user confirms it. That keeps the match honest and still gives the user a clear next step.
Another case that comes before duplicates: the key is unique in both files but written differently, like a trailing space, different case, or leading zeros lost after Excel. Is key matching exact by default, with normalization as an opt-in per column? Doing it automatically seems risky, since stripping zeros or changing case could merge IDs that were actually distinct.
I checked the current prototype: key matching is exact.
AandAwith a trailing space,Aanda, and001and1are different keys, so those differences show up as removed and added rows. There isn’t a per-column normalization option yet. If I added one, I’d want it to be opt-in, with a preview of any distinct keys it would merge. If Excel has already removed leading zeros, normalization alone can’t recover the original ID.Thanks for checking it. Exact by default with a merge preview sounds right. One smaller step before any opt-in: when a removed key and an added key differ only by whitespace, case or leading zeros, flag the pair as a possible match in the diff without merging them. The user can see the likely cause and decide.
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?
Thanks, Sujal. I should clarify that the example is made up. It doesn’t tell me how long a real comparison takes or what I’d pay to fix it.
For now, duplicate keys stop the comparison. You need a unique key or some data preparation before trying again.
When you run into this, is there usually another column that identifies the row, or do you have to look outside the file?
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.
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.
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?
Some comments may only be visible to logged-in visitors. Sign in to view all comments.