TSV Text Cleaner
The characters that make a file look fine and behave badly. A cell ending in a non-breaking space does not equal the same cell without one, so the join drops it. A zero-width space in the middle of an ID breaks every match it touches. A column of ’ is a UTF-8 file that something opened as Latin-1. None of it is visible in a spreadsheet, which is exactly why it survives all the way into production.
How to use
- Paste or drop the file. The status bar names every kind of problem it found and how many of each — if it says nothing to clean, the file is genuinely clean.
- Switch Output to a before/after report to see what would change, row by row, before you accept it. Invisible characters are printed as visible marks in that view.
- Switch back and download. Nothing is written until you do.
Mojibake, and why the repair is careful
café stored as UTF-8 is the bytes 63 61 66 C3 A9. Read those bytes as
Windows-1252 and you get café; save that as UTF-8 and the damage is now real
data. The repair runs the mistake backwards: take each character's code point as a single
byte and decode the result as UTF-8.
That operation destroys any text it is applied to wrongly, so it is guarded three ways. It
only runs on cells containing one of the tell-tale pairs (Ã, â€,
 and friends). It bails out if any character is outside the Latin-1 and
Windows-1252 ranges, because a genuine 東 or 🙂 cannot have come from
a single byte. And the decode is strict — invalid UTF-8 underneath means the guess was wrong,
so the cell is left exactly as it was. Correctly-encoded café,
naïve, Привет and emoji all pass through untouched.
Windows-1252 gets special handling because it renders the byte range 80–9F as
printable characters — ’, —, € — where Latin-1 leaves
them as controls. Those are mapped back to their bytes before decoding, which is what makes
it’s → it’s work at all.
What each option removes
Invisible characters
No-break space, narrow no-break space, figure space, thin space and ideographic space all become an ordinary space. Zero-width space, zero-width joiner and non-joiner, word joiner, soft hyphen, a byte-order mark that has ended up inside a cell, and the bidirectional override characters are deleted outright. These are the ones that break equality without changing how anything looks.
Smart punctuation
Off by default, because it changes text you can see. On, curly quotes become straight ones, en and em dashes become hyphens, the ellipsis character becomes three dots, and the Unicode minus becomes a hyphen. Worth doing when the file is going into a system that compares strings, and worth leaving alone when the file is prose.
Control codes
Everything below space except tab, newline and carriage return, plus DEL. These arrive from binary data pasted into a spreadsheet, or from a truncated export, and they make files that no parser reads twice the same way.
Normalise
é can be one code point or two (e plus a combining accent), and the
two are not equal to anything comparing strings. NFC composes them, which is
what the web and most databases expect; NFD decomposes. NFKC
also folds compatibility characters — fi to fi, full-width Latin to
ASCII, superscripts to digits — which is right for a search key and wrong for text you have
to reproduce exactly.
The report
One row per changed cell: the row number, the column name, what was found, and the value
before and after — with the invisible characters printed as marks so you can see them:
␠ for a no-break space, ␣ for another exotic space, ·
for a zero-width character, ␡ for a control code, ⇄ for a bidi
control, → for a tab. Capped at 200 rows; the counts in the status bar are for
the whole file.
Privacy
100% client-side. The file is cleaned in your browser; nothing is uploaded.
Where to go next
To see which columns are affected before you clean anything, stats flags leading and trailing whitespace per column. For whole-file encoding conversion rather than per-cell repair, use encoding convert; for a BOM at the start of the file rather than inside a cell, BOM tools. Once the text is consistent, dedupe and join start agreeing with you.