Fixing Duplicate Column Names When Reading Excel Data

Two columns in the sheet are both called Amount. Read it with pandas and nothing fails: you get Amount and Amount.1. Open it in Power Query and the second becomes Amount.1 there too. Load it into a database table straight from the header and the load stops with an error about a column being specified more than once. Duplicate headers are common in exported sheets, and each tool handles them differently. This post shows what the tools do, how to read the sheet so that you control the names, and how to decide what the duplicates mean. It is a spoke of understanding Excel errors when data comes from code.

What the tools do

Take a sheet with this header row:

pandas. pd.read_excel renames later duplicates by appending .1, .2 and so on, giving Order, Amount, Date, Amount.1. Older versions had a mangle_dupe_cols argument to switch this off, which was removed in pandas 2.0, so the renaming is always on. It means no error and a silently different name, and any code that expects Amount to be unique gets only the first.

Power Query. Promoting headers renames duplicates the same way: Amount and Amount.1. If you rename a column to a name that already exists, the editor refuses.

openpyxl. It does not care. It reads cells, and the header row is just values. Code that builds a dict from the header row loses the first Amount because the second overwrites the key. That is the quiet version of the bug.

SQL. CREATE TABLE t (order text, amount numeric, date date, amount numeric) fails with column "amount" specified more than once in PostgreSQL. A loader that builds the table from the header row fails the same way.

Excel Tables. Creating a Table from a range with duplicate headers renames them Amount and Amount2.

The common thread is that automatic renaming turns a visible problem into an invisible one. The suffix makes the code run, and nobody asks what the second Amount was.

Read without headers and name the columns yourself

Take control. Read the header row as data, build unique names with your own rule, and apply them:

import pandas as pd

raw = pd.read_excel("sales.xlsx", header=None, dtype=str)
header = raw.iloc[0].tolist()
body = raw.iloc[1:].reset_index(drop=True)

seen = {}
names = []
for h in header:
    h = str(h).strip()
    seen[h] = seen.get(h, 0) + 1
    names.append(h if seen[h] == 1 else f"{h}_{seen[h]}")

body.columns = names
print(names)

['Order', 'Amount', 'Date', 'Amount_2']

header=None stops pandas from interpreting the first row, so you see what the sheet holds. The loop numbers repeats, and you can pick another rule: a suffix, a position, or a name that carries meaning. Add a check that len(set(names)) == len(names) and fail the run if not. This also fixes empty header cells, which pandas names Unnamed: 3; map them to something intentional or reject them (pivot table refresh errors and missing fields shows how blank headers also break pivots).

Work out what the duplicates mean

Renaming is mechanical. Understanding is the real task, because the right names depend on why the columns repeat.

A repeated group of columns. Amount appears twice because the sheet has Amount for 2024 and again for 2025. The year is in a header row above, spread over several lines. The unique names are 2024 Amount and 2025 Amount, built by combining the header levels (cleaning Excel header rows spread across several lines).

Two roles with one label. Amount is the net amount and Amount the tax amount, distinguished by position or color in the sheet. Rename by role. The sheet's author knows which is which; ask.

A copy and paste mistake. The second column is a duplicate of the first, or an accidental copy. Compare the values: body["Amount"].equals(body["Amount_2"]). If they are identical, drop one. If they differ slightly, find out which is current.

A report layout with side-by-side blocks. Two tables sit next to each other in the same rows, each with its own Amount. They are two datasets on one sheet. Slice the columns into two frames and process separately.

A pivoted export. Repeated names like Sales for each month under a header row of months. This is wide data to be unpivoted (unpivoting columns back into rows in SQL).

Write down the decision as a mapping from position to name. If the same layout repeats monthly, the mapping becomes part of the loader.

Name by position when you must

When the header is unreliable but the layout is fixed, name by position and ignore the header:

cols = ["order", "amount_net", "date", "amount_tax"]
df = pd.read_excel("sales.xlsx", header=0, names=cols, usecols="A:D", dtype=str)

The names argument replaces the header row with yours (with header=0 it replaces the existing header). Check that the sheet has exactly four columns in that range; assert on the shape. If the producer reorders columns the positional names become wrong silently, so combine this with a check on a few known values, such as the first column being all integers.

In Power Query

Promote headers, then rename the duplicates before anything else:

Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Renamed = Table.RenameColumns(Promoted, {{"Amount", "Amount Net"}, {"Amount.1", "Amount Tax"}})

Because Power Query has already made the names unique, the second duplicate is Amount.1, and your rename step targets it. This depends on column order, so if the producer swaps the columns, the names swap with them and nothing fails. For safety, rename by position: build the list of new names and apply List.Zip({Table.ColumnNames(Promoted), NewNames}). Add a check step that raises an error if the number of columns differs from what you expect (fixing Power Query errors when source columns change).

In a database load

If you create the table from the sheet's header, sanitize names first. Lowercase them, replace spaces and symbols with underscores, prefix names that start with a digit, truncate to the database's limit (63 bytes in PostgreSQL) and make them unique.

import re

def sql_name(s):
    s = re.sub(r"[^0-9a-zA-Z_]+", "_", s.strip().lower()).strip("_") or "col"
    return "c_" + s if s[0].isdigit() else s

After sanitizing, two different headers can collapse to the same name (Amount ($) and Amount (%) both become amount), so run the uniqueness check again after cleaning, not before. Keep the original header text in a separate table of column metadata so a person can map columns back to the sheet.

A check that stops the run

Make uniqueness a gate:

dupes = [n for n in set(names) if names.count(n) > 1]
if dupes:
    raise ValueError(f"duplicate column names after cleaning: {dupes}")

Run it after every cleaning step that changes names. Also check for empty names and names that differ only by case, which SQL treats as the same unless quoted. A loader that refuses the file is annoying once, and far cheaper than a table in which the second Amount quietly replaced the first.

When duplicate headers carry meaning

Repeating blocks are the point. Some forms repeat a block of fields for each person or period. The duplication is the structure, and the fix is to reshape into one row per block, not to rename.

The sheet is a presentation. Duplicate headers in a report meant for reading are fine. Ask for the underlying data instead of reverse engineering.

The duplicates are across files. If many files have the same columns, duplication within a file is unrelated to the schema across them. Compare headers across the folder before combining (combining many files from a folder with Power Query).

You cannot ask anyone. If nobody knows what the second column is, load it under a neutral name, mark it as unverified and keep it out of reports.

See the pandas reference for read_excel, which documents header, names and the duplicate handling, and PostgreSQL's page on identifiers. To inspect a small table as JSON, use the CSV to JSON converter.