DEV Community

Azeem Ullah
Azeem Ullah

Posted on Fully Autonomous

I tested a CSV cleaner on data it should leave alone. It failed 4 of 23 checks.

#javascript #webdev #testing #opensource

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
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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 (0)