How to standardize inconsistent column names across multiple CSV files

The client sends twelve monthly exports and says "just stack them into one file for the year." You append them, and instead of a clean table with ten columns you get a monster with thirty-eight, most of them half-empty. January called the field Customer. February called it customer_name. March, exported by a different person, went with Cust Name, and somewhere in Q3 it became CustomerName. To your eyes they're obviously the same column. To any append operation they're four different fields, and now every total is spread across four columns that never add up.

Why an append trusts the header, not the meaning

When you stack tables, the tool matches columns by their exact header string, character for character. It has no idea that Customer and customer_name mean the same thing — a trailing space, a capital letter, or an underscore is enough to make it treat them as unrelated. So it keeps both, fills each with values only from the files that used that spelling, and leaves the rest blank. Twelve files with three header variants each is how you end up with a thirty-eight-column table where no single column is complete.

The example: four spellings, one field

Here are the header rows from four of the monthly files:

Jan.csv:  Order ID | Customer      | Amount
Feb.csv:  order_id | customer_name | amount
Mar.csv:  Order ID | Cust Name     | Total
Apr.csv:  OrderID  | CustomerName  | Amount (£)

Three real fields, but eleven distinct header strings. The fix is a mapping table: one place that says every messy spelling on the left resolves to one canonical name on the right.

raw               ->  canonical
Customer          ->  customer
customer_name     ->  customer
Cust Name         ->  customer
CustomerName      ->  customer
Order ID          ->  order_id
OrderID           ->  order_id
Amount            ->  amount
Amount (£)        ->  amount
Total             ->  amount

Rename each file's headers through that table before you append, and the twelve files collapse into a clean ten-column table where customer is one complete column, not four leaky ones. In Power Query you'd do this with a Rename Columns step driven by the mapping; in plain Excel, an =XLOOKUP(header, map[raw], map[canonical], header) against the header row does the same job and leaves anything unmapped untouched so you can spot it.

Standardize headers in five steps

1. Collect every header from every file into one list and remove exact duplicates. This is your inventory of the mess.
2. Decide the canonical names once — lowercase, no spaces, no symbols: customer, order_id, amount. Boring is correct.
3. Build the mapping table — every raw spelling on the left, its canonical name on the right, including the ones you haven't seen yet as you find them.
4. Rename before you append, never after. Standardizing headers on twelve small files is trivial; unpicking a thirty-eight-column mess is not.
5. Flag unmapped headers so next month's brand-new spelling ("Client") surfaces as something to add to the map, not a silent new column.

The map is the asset here. You build it once for this client, and every future export — the January that hasn't happened yet — runs through the same table without you re-deciding anything.

The header is a contract, so write it down

Inconsistent column names feel trivial until they cost you an afternoon of manual re-mapping every single month. The analysts who combine multi-file datasets quickly aren't faster typists — they treat the header row as a contract and keep that contract in a mapping table instead of in their head. Do that once and "stack the twelve files" goes from a dreaded afternoon to a two-minute apply. And when the client switches systems next year and every header shifts again, you edit one mapping table instead of rewriting the whole pipeline — the map absorbs the change so your dashboards never notice it happened.