Header Variants and Normalizing Column Names in CSV Files

The header row looks like the easy part of a CSV, and it is where two sources that mean the same thing fail to match. One system writes Order ID, another order_id, a third OrderID, and a fourth Order Id with a trailing space and a byte order mark in front of it. All four are the same column to a person and four different strings to a join. The second problem is that names a CSV allows are not names a database allows: a header may be empty, may repeat, may start with a digit, may contain brackets and accents. This article builds a header normalizer you can test, explains each rule and says where normalizing helps and where it hides a change you should hear about. It is the naming step that follows the structure fixes in parsing multi line headers in report style CSV files and sits in the family of problems in CSV dialects and variants you will run into.

What goes wrong in headers

Eight problems account for nearly every bad header I have seen described, and each can be detected in code.

A byte order mark. An invisible U+FEFF in front of the first name makes id and id differ (the UTF-8 BOM in CSV files). Surrounding whitespace. Customer with a trailing space, from an export that pads names. Case differences. OrderID, orderid and ORDERID. Separators. Spaces, hyphens and underscores used interchangeably: order id, order-id, order_id. Punctuation and units. Total (EUR), Qty., % Change, #Items. Accents. Café, Größe, where some systems cannot use non-ASCII names. Blank names. A header cell that is empty, common after a deleted column or a trailing delimiter. Duplicates. Two columns with the same name, which a dictionary-based reader silently collapses into one, losing a column.

Dictionary readers are the quiet danger. Python's csv.DictReader with duplicate names keeps only the last value for each name, so a file with two Total (EUR) columns loads with one, and no error is raised. Check for duplicates before you build the dictionary.

A normalizer you can test

Here is a function that handles all eight. It is deterministic and total: every input gives exactly one valid, unique output.

import re, unicodedata

def normalize(names):
    out, seen = [], {}
    for i, raw in enumerate(names):
        name = raw.replace("", "")
        name = unicodedata.normalize("NFKD", name).encode("ascii", "ignore").decode()
        name = re.sub(r"[^0-9a-zA-Z]+", "_", name.strip()).strip("_").lower()
        if not name:
            name = f"col_{i}"
        if name[0].isdigit():
            name = "c_" + name
        if name in seen:
            seen[name] += 1
            name = f"{name}_{seen[name]}"
        else:
            seen[name] = 1
        out.append(name)
    return out

header = ["Order ID", " Customer Name ", "Total (EUR)", "Total (EUR)", "", "2026 Q1", "Café", "order-id"]
print(normalize(header))

['order_id', 'customer_name', 'total_eur', 'total_eur_2', 'col_4', 'c_2026_q1', 'cafe', 'order_id_2']

Going through the rules in order explains the output. The BOM is removed first. Accents are folded with NFKD normalization, which splits é into e and a combining accent, and encoding to ASCII with ignore drops the accent, giving Cafe. Everything that is not a letter or digit becomes an underscore, runs of them collapse to one, and leading and trailing underscores go, so Total (EUR) becomes total_eur. Lowercasing makes Order ID and order-id both order_id. An empty result gets a positional name (col_4, using the zero-based index of the cell). A name beginning with a digit gets a prefix, because 2026_q1 is not a legal unquoted identifier in most SQL dialects. A repeat gets a numeric suffix, so the second total_eur is total_eur_2 and the second order_id, which came from the hyphenated order-id, is order_id_2.

The last of those shows the limit of any such function. Order ID and order-id in one header are almost certainly the same field written twice, or two fields with a naming accident, and suffixing hides which. The function cannot know, and it flags nothing. For files where duplicates are unexpected, make the function raise on a duplicate after normalizing instead of suffixing.

What to do about non-ASCII names

Folding to ASCII is a choice, and not always the right one. It loses information, and the loss is uneven. In the run for this article, Größe became Groe (the ö decomposes and the ß does not, so the ß vanished), Łódź became odz, Ørsted became rsted and 日本 became an empty string, which the function then names col_N. Those are not names anyone would choose. If your database and tools support Unicode identifiers, keep the letters and normalize only the separators and case. PostgreSQL and MySQL both allow non-ASCII identifiers, though quoting in some tools becomes awkward. The choice depends on the downstream: a warehouse that your analysts query with ASCII-only habits wants ASCII, and a dataset in one non-Latin language is better with the native names plus an English alias in the documentation.

If you fold, transliterate properly where it matters. A library like unidecode maps ß to ss and Ł to L, which the standard-library approach does not. Whatever you choose, apply it once, in one place, and test it with names from your real files.

Keep the original names

Normalization is lossy and one-way, so keep a mapping from the original header to the normalized one. Write it out with the data, or into the run's log:

original            normalized
Order ID      order_id
 Customer Name      customer_name
Total (EUR)         total_eur

The mapping answers "what was this column called in the file?" when someone checks a number against the source, and it makes a changed header visible: if next month's file has Order No where Order ID was, the normalized name becomes order_no, and the comparison below catches it.

Normalizing is not a substitute for checking

Here is the trap. A normalizer that maps many spellings to one name is useful for tolerating harmless variants, and it can also paper over a real change. If a provider renames total to total_net and your normalizer is generous about suffixes, you may load net amounts into a column that used to hold gross and see nothing wrong. Normalize, then compare against the expected list of normalized names, and fail on any difference:

EXPECTED = ["order_id", "customer_name", "total_eur"]
names = normalize(raw_header)
missing = [n for n in EXPECTED if n not in names]
extra = [n for n in names if n not in EXPECTED]
if missing:
    raise SystemExit(f"missing columns: {missing}; new columns: {extra}")

That keeps the tolerance (case, spacing, punctuation) where it is harmless and the strictness where it matters. Handling a CSV provider who changes columns without warning shows the report you want when the check fails.

A word on reading headers by position

Some feeds have no header, or an unreliable one. For those, assign names by position from a documented layout and treat the file's header, if any, as a comment to be ignored or checked (does a CSV file need a header row?). Position-based mapping is fragile to column reordering, so check at least the count, and a sample of values from each column against its expected type, before trusting it.

When the original names matter

Keep the original names when the file is a published dataset that others will cite, when column names are part of a contract with a regulator or a partner, and when the names carry units or meaning you would lose (Total (EUR) says the currency). In those cases, rename in a separate, documented layer and leave the raw file untouched, so you can always show the source column behind a number.

Also keep them when you are writing a tool for people who know the data by its original headers: an analyst who typed Total (EUR) in an Excel header for years will not enjoy total_eur in a report. Normalize for storage and joins, and map back for display. The Python modules used above are documented in the unicodedata reference and the re reference, and pandas has its own approach in str methods for column names.