Your spreadsheet lookup fails because of three invisible characters

#debugging #productivity #software #python

A reader sent me a spreadsheet last week with a lookup that returned #N/A for every row. The values were obviously there. You could see Widget in column A and Widget in the lookup table. Identical, as far as the screen was concerned.

They were not identical. One of them was Widget followed by a non-breaking space, a character that renders as nothing and survives every copy-paste, every save, and every "just retype it" attempt.

This is the most common spreadsheet bug I know of, and it is invisible by construction. Here are the four characters responsible, how to find them in ten seconds, and a normaliser you can paste into your own workflow.

Why the values look identical

A spreadsheet shows glyphs. Your comparison runs on code points. Between those two things sits a whole category of characters that occupy no visible width:

U+FEFF, the byte-order mark. Excel writes it at the start of every "CSV UTF-8" export. If it lands in the first header cell, your column is not called Name, it is called \ufeffName. Every lookup against Name fails, and the header looks completely normal.
U+00A0, the non-breaking space. Web pages and HTML-to-Excel exports are full of them. It looks like a space, sorts like a space, and is not " ". strip() in most languages does not remove it by default.
U+200B through U+200D, zero-width characters. Copy text out of a PDF, a chat window or a CMS and you can pick these up. They are not spaces at all; they are formatting marks with no width.
Duplicate header rows. Concatenate twelve monthly exports and the header row appears in the middle of your data, splitting types and breaking lookups halfway down the file.

Finding them in ten seconds

Stop staring at the cells. Ask the machine what is actually in them:

import unicodedata

def show_invisible(value):
    return "".join(
        f"\\u{ord(ch):04x}"
        if unicodedata.category(ch) in ("Cc", "Cf", "Zs") and ch != " "
        else ch
        for ch in value
    )

print(show_invisible("\ufeffName\u00a0"))   # \ufeffName\u00a0
print(len("\ufeffName"), len("Name"))       # 5 4

The len() line is the one that ends arguments. If the header is five characters and you typed four, you have found your bug. In Excel the same check is =LEN(A1), which is the fastest diagnostic available and works on any machine without installing anything.

The normaliser

Once you know what you are dealing with, cleaning is a few lines. This handles the BOM, the non-breaking space, zero-width characters, and stray padding:

import re

def normalise(value):
    value = value.replace("\ufeff", "")                  # byte-order mark
    value = value.replace("\u00a0", " ")                 # non-breaking space
    value = re.sub(r"[\u200b-\u200d\u2060]", "", value)  # zero-width marks
    return value.strip()

Run it over the header row and every cell you are about to match on. Order matters: replace the non-breaking space with a normal space before stripping, otherwise strip() leaves it in place and you have fixed nothing.

Fixing the file, not the symptom

Doing this by hand is fine for one column and hopeless for twenty thousand rows across forty files. The durable fix is to clean on the way in, once, and never think about it again:

import csv, io

with open("export.csv", "rb") as fh:
    text = fh.read().decode("utf-8-sig")   # "utf-8-sig" eats the BOM for you

rows = list(csv.reader(io.StringIO(text)))
header = [normalise(c).lower().replace(" ", "_") for c in rows[0]]
data = [[normalise(c) for c in row] for row in rows[1:] if any(c.strip() for c in row)]

Three details in there are worth more than they look:

utf-8-sig, not utf-8. The -sig variant strips the byte-order mark as it decodes. Without it you are back to a phantom character in column one.
any(c.strip() for c in row) drops fully empty rows, which otherwise become rows of empty strings that break int() and float() later.
Lowercasing and underscoring headers means " Name " and "name" and "NAME" all become name. Header drift is the second most common cause of broken lookups after invisible characters.

For the mojibake flavour — caf\u00c3\u00a9 instead of café — the cause is different: text was decoded with the wrong encoding, then encoded back. Reversing it is a round trip: encode with the wrong codec, decode with the right one. It works, but only on text that has not been through two wrong conversions, which is why backups matter more than cleverness there.

The habit that prevents all of it

Treat every export as hostile until proven otherwise. Decode with utf-8-sig, normalise every string, and assert on your header names before you do any matching. It takes four lines and it removes an entire class of bug from your life.

The spreadsheets I get are rarely malicious, but they are always from somewhere else: a CRM, a payment processor, a client on Windows 7. Their encoding choices are not your fault, but they are your problem the moment the lookup fails.

The normaliser above is free regardless — go fix your lookups. If you would rather not maintain the scripts yourself, I put the two most common chores from this article into a free sampler: renaming a folder into date order, and merging a pile of spreadsheets into one table, with the setup chapter for people who have never run Python: clairenagase.gumroad.com/l/wqqsal. The full guide and the other six chores live at clairenagase.gumroad.com/l/lnlafw.

DEV Community