What actually breaks a CSV import (and how to check in 30 seconds)

Every "the import failed" ticket I've seen comes from one of six things. None of them are the parser's fault.

1. Full-width digits

A form on a phone with a Chinese IME writes １３８００００１２３４ (U+FF11...), not 13800001234. parseInt returns NaN, Number() returns NaN, and your validation says "invalid phone" while the number looks fine on screen.

Fix: map U+FF01–U+FF5E down by 0xFEE0 before validating.

2. Country prefixes and punctuation in phone numbers

+86 138-0000-1234, 138 0000 1234, (86)13800001234 — all the same person. Strip non-digits, then drop a leading 86 only if what remains is exactly 11 digits and starts with 1.

3. Zero-width characters

A copy from a web page or a PDF often carries U+200B / U+FEFF. "[email protected]\u200b" fails an email regex and looks identical in the browser. Strip [\u200b\u200c\u200d\ufeff\u00a0] globally.

4. Excel serial dates

45000 is a date to Excel and a number to your importer. If a column is supposed to be a date and values sit between 20000 and 60000, convert from the 1899-12-30 epoch.

5. Thousands separators without quotes

This one bites at the CSV level, before your code runs:

order,amount
A-01,¥1,280

That is two columns, not one. Your header says 2 columns, the row has 3, and every downstream field shifts. Either quote the value, or use tabs (which is what you get when a user copies straight out of a spreadsheet).

A cheap defensive move: if a row has more fields than the header, merge the overflow back into the last column instead of dropping the row.

6. Duplicates that only match after normalisation

Two rows are the same person, but one has +86 and the other doesn't. Deduplicate after normalising, and keep a list of what was merged — silently dropping rows is how you lose a customer record and never find out.

A normalisation pass, in one function

const ZW = /[\u200b\u200c\u200d\ufeff\u00a0]/g;
const norm = (v) => String(v).replace(ZW, "").replace(/\u3000/g, " ").trim();
const phone = (v) => {
  let d = norm(v).replace(/\D/g, "");
  if (d.length > 11 && d.startsWith("86")) d = d.slice(2);
  return /^1[3-9]\d{9}$/.test(d) ? d : null;  // null = flag it, don't drop it
};

The important design decision is the last line: return null and keep the row, with the reason. A cleaning step that deletes rows it doesn't understand is worse than no cleaning step.

If you just want to see what your file looks like after this treatment, the same logic is behind a small page — paste rows or drop a CSV, get a clean table plus a list of every value that could not be fixed: https://getsnapnote.com/workbench.html (free, no signup, runs in memory).

If you're dealing with thousands of rows and custom rules (merge these, match by that), that's the part worth automating once and reusing.