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 impr...
For further actions, you may consider blocking this person and/or reporting abuse
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?