The Invisible Characters That Break Your CSV Import

The value in the cell is SKU-1042. The value in the database is SKU-1042. The lookup returns nothing. You retype it by hand and it works.

That’s an invisible character. Something in the string occupies no visual space, or looks exactly like a character it isn’t, and every comparison you run is correctly telling you two different strings are different.

The ones you’ll actually hit

That last row is the worst class, because the character is fully visible and still wrong. A homoglyph. Cyrillic а, е, о, р, с and Greek ο render indistinguishably from their Latin twins in most fonts. You can stare at the cell for a minute and see nothing.

The BOM, specifically

This one deserves its own paragraph because it’s so common in CSV work and so confusing when it happens.

Save a file as “UTF-8 with BOM” — which Excel on Windows does by default — and three bytes go on the front of the file. Your first column header stops being id and becomes id. The import then reports “column id not found” while showing you a file whose first column is plainly called id.

The fix is to save as plain UTF-8, or strip the leading U+FEFF on read. Most CSV libraries have a flag for it; the encoding is usually named utf-8-sig.

How to find them

Three approaches, in increasing order of usefulness:

1. Compare lengths. If len(value) is longer than the number of characters you can see, something is hiding in there. Tells you it exists, not what or where.
2. Dump the code points. Definitive, and painful past a few dozen characters.
3. Use something that names them. The Invisible Character Detector shows each one, where it is, and what it is — including homoglyphs, which a plain “strip whitespace” pass will never touch because they aren’t whitespace.

If you don’t need the forensic detail and just want the string cleaned, the Text Cleaner removes them and reports the count. Both use the same detection, so they always agree about the same text.

Where they come from

• Word and Google Docs. Smart quotes and non-breaking spaces by default. Ctrl+Shift+V pastes without them.
• Web pages.   used for layout, which becomes a real U+00A0 when copied.
• PDFs. Non-breaking spaces and soft hyphens from the print layout. Along with a line break on every line.
• AI chatbots. Typographic quotes and dashes as standard, sometimes zero-width characters.
• Spreadsheets. The BOM, and non-breaking spaces inside numbers formatted as text — which is why a column of numbers sums to zero.

Stopping it before the import

If you’re building something that accepts pasted text or an uploaded CSV, normalise on the way in. Cheap, and it removes an entire category of support ticket:

• Strip the BOM if it’s the first character.
• Replace U+00A0 with a plain space, then trim.
• Remove U+200B, U+200C, U+200D, U+FEFF and U+00AD.
• Normalise curly quotes to straight ones, for identifiers and codes at least.
• Apply Unicode NFC normalisation so é as one code point and é as two compare equal.

Do this on the key you match on, not on the text you display. Someone’s name may legitimately contain characters you’d strip from a SKU.

The short version

Two things you cannot see cause almost all of these: characters with no width, and characters that look like other characters. Trimming whitespace catches neither reliably. Check the string, name what’s in it, then normalise the field you match on.