A CSV cleaner landed on my desk this week: an MIT-licensed, browser-only tool that repairs broken quotes, fixes garbled characters and masks emails and phone numbers. It came with 50 tests, and all 50 passed.
Every one of those tests fed it broken data and checked that it got fixed. None of them fed it good data and checked that it stayed the same. So I wrote 23 of those.
Why I cared
The broken-quote problem is real and common. Shopify's own community forum has several threads titled some version of "Illegal quoting in line 2", usually caused by an inch mark or a quote inside a product title or description: https://community.shopify.com/t/illegal-quoting-in-line-2-and-missing-headers-title/10418
A tool that fixes that is useful. A tool that fixes that and quietly changes other cells is worse than no tool, because nobody checks a 5,000-row file by eye.
What failed
1. Two identical rows became one. Duplicate removal was switched on by default. That sounds tidy until you remember that two identical order lines (same order, same item, same quantity) are real data.
order,item,qty
7,Pen,1
7,Pen,1
Output: one row. The fix was to make it opt-in.
2. "SÃO PAULO" became "SÁO PAULO". The mojibake fixer was a table of find-and-replace pairs: é to é, ’ to ', and so on. One entry was just à to Á (the real pair is à plus an invisible control character, which had probably been lost), so every à in correct Portuguese text got changed.
The fix was to stop guessing with a table. Mojibake is UTF-8 bytes read as Windows-1252, so you can reverse it exactly: take a suspicious run of characters, turn each one back into its Windows-1252 byte, and decode those bytes as UTF-8 with a strict decoder. If they are not valid UTF-8, the text was never mojibake, and you leave it alone.
const decoder = new TextDecoder('utf-8', { fatal: true });
function fixRun(run) {
const bytes = toCp1252Bytes(run); // null if a character has no Windows-1252 byte
if (!bytes) return run;
try { return decoder.decode(new Uint8Array(bytes)); }
catch { return run; } // not valid UTF-8: it was real text
}
3. Curly apostrophes came back straight. The same table mapped ’ to a plain '. The original character was ’. The round trip above gives back exactly what was there.
4. An ISBN got masked as a phone number. With masking on, 9780306406157 became (***) ***-6157. The phone pattern matched any long run of digits. Order numbers and account numbers had the same risk. Now it only masks numbers written like phone numbers (a +, brackets, or groups separated by spaces or dashes), and card masking checks the card checksum.
The other thing the tests didn't cover
The tool had six landing pages, each with a little demo box. They didn't use the engine at all. Each one had its own short script, and its line-split regex was written as /\\r?\\n/ with doubled backslashes, so it matched a literal backslash followed by n and never split anything. The tests only covered the engine, so nobody noticed. All six now use the same engine as the main page.
What I took from it
For any tool that changes data, half the tests should be inputs it must not touch: valid escaped quotes, quoted commas, line breaks inside fields, correct accented text, real duplicate rows, numbers that only look like phone numbers. Those are the bugs users never report, because they never notice.
The fixed version is here, free, and it runs in your browser: https://mianazeemdaula.github.io/csv-shield/
Source: https://github.com/mianazeemdaula/csv-shield
Tested in Node 22 and headless Chrome 155. Not tested in Firefox, Safari or on large files yet.
Top comments (2)
Your leave-alone test suite is the real contribution here. Most cleaning tools only get exercised on data that was already broken, so nobody notices the tool quietly introducing new damage — the four failures you found are exactly what slips through when tests only assert on broken input.
One concrete habit that's saved me: keep a golden file of leave-alone rows (escaped quotes, accented names, long digit strings like ISBNs and order IDs) and diff the tool's output against it byte-for-byte in CI. The key detail: build the golden file from real-world edge cases, not hand-written ones — real exports, real Portuguese product names. Hand-written cases only cover what you already thought of.
A second one: run each transformation as a separate named stage instead of one composed pass. Masking, dedupe and encoding fixes all touching the same column in one run makes failures untraceable; staged runs mean the diff points straight at the stage that did the damage.
I run a small service called Web Data Runs that does exactly this kind of scrape-and-clean work, and leave-alone checks like yours are now the first tests I add for any new pipeline.
The duplicate one is the scariest failure here, because it's silent AND looks like success. Two identical order lines are real data. My rule after getting burned the same way: dedupe is only safe when it's opt-in and shows you a "these N rows will be removed" preview BEFORE applying — plus a singles-only view (=UNIQUE(range, FALSE, TRUE) in Sheets) when you actually want to audit what's repeated.
Encoding changes without a diff view are the same category of sin.
Genuine question for folks here: if a browser cleaner guaranteed "nothing changes silently — every single change previewed before it applies," would you pay for that as a one-time purchase? What price wouldn't make you think twice?