Use Converters When Reading Excel with pandas

read_excel guesses a type for every column, and for tidy numeric data the guess is right. For the columns people actually struggle with — account codes that must keep their leading zeros, amounts exported as $1,200.50 or (300.00), flags typed as Y, y, yes and 1 — the guess is wrong in ways that are expensive to undo later. The converters argument lets you attach a small function to a column so each cell is cleaned as it is read, before inference can damage it. This guide is part of Reading Excel Files with pandas.

Prerequisites

pip install pandas openpyxl

This workbook has the four problem columns described above, written with openpyxl so each cell keeps the exact type a real export would give it:

import datetime as dt
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["account", "amount", "active", "booked"])
ws.append([4120, "$1,200.50", "Y", dt.datetime(2026, 3, 1)])
ws.append(["00456", "(300.00)", "n", "2026-03-05"])
ws.append([None, "1,050.25", "yes", None])
ws.append(["07702", "-", "0", "05/03/2026"])
wb.save("ledger.xlsx")

Read it without help and see what inference does:

import pandas as pd

raw = pd.read_excel("ledger.xlsx")
print(raw.dtypes)
print(raw["account"].tolist())   # [4120.0, 456.0, nan, 7702.0]

The account column mixed numbers and text, so pandas turned it into floats — 00456 is now 456.0, and a join against a chart of accounts keyed on text will silently miss.

Step 1 — Keep codes as text

A converter is any callable that takes one value and returns one value. For codes, turn whatever arrives into a string and pad it to the known width:

def account_code(value) -> str:
    if value == "" or value is None:
        return ""
    if isinstance(value, float) and value.is_integer():
        value = int(value)
    return str(value).strip().zfill(5)

df = pd.read_excel("ledger.xlsx", converters={"account": account_code})
print(df["account"].tolist())   # ['04120', '00456', nan, '07702']

Notice the empty cell. The engine hands the converter an empty string, the converter returns an empty string, and pandas then treats that as missing — so blanks stay NaN rather than becoming a fake code. The isinstance(value, float) branch covers engines and files that store whole numbers as floats; without it 4120.0 would become the string "4120.0". The same problem solved with dtype=str works only when the cells were stored as text in the first place, which is the subject of Keep Leading Zeros When Reading Excel with pandas.

Step 2 — Parse currency and accounting text

Money exported as text needs its currency symbol and thousands separators removed, parentheses turned into a minus sign, and placeholder dashes turned into missing values:

import math
import re

def money(value) -> float:
    if isinstance(value, (int, float)):
        return float(value)
    text = str(value).strip()
    if text in ("", "-", "—"):
        return math.nan
    negative = text.startswith("(") and text.endswith(")")
    number = float(re.sub(r"[^0-9.\-]", "", text))
    return -number if negative else number

df = pd.read_excel("ledger.xlsx", converters={"account": account_code, "amount": money})
print(df["amount"].tolist())    # [1200.5, -300.0, 1050.25, nan]
print(df["amount"].dtype)       # float64

The first branch matters: a well-formed file stores amounts as numbers, and the converter should pass them through untouched rather than stringify and re-parse them. Deciding the rules per column this way — here, that a lone dash means "no value" — is exactly the judgement that type inference cannot make for you.

Step 3 — Map flags to booleans

Free-typed yes/no columns arrive in every spelling. Normalise them to real booleans with a lookup, and decide explicitly what happens to values you do not recognise:

TRUTHY = {"y", "yes", "true", "1", "x"}
FALSY = {"n", "no", "false", "0", ""}

def flag(value):
    text = str(value).strip().lower()
    if text in TRUTHY:
        return True
    if text in FALSY:
        return False
    raise ValueError(f"unrecognised flag value: {value!r}")

df = pd.read_excel("ledger.xlsx", converters={"active": flag})
print(df["active"].tolist())    # [True, False, True, False]

Raising on an unknown value is deliberate. A converter that quietly maps "maybe" to False hides a data problem; one that raises stops the read with a message that names the offending value, which is what you want from a scheduled job. The exception propagates out of read_excel, so wrap the call if you would rather log and continue.

Step 4 — Dates that arrive as mixed types

Date columns are the classic mixed column: real Excel dates come through as datetime objects, typed text as strings in whatever format the typist used. A converter can accept both and apply one explicit format to the text:

def booked(value):
    if value == "" or value is None:
        return pd.NaT
    if isinstance(value, dt.datetime):
        return pd.Timestamp(value)
    for fmt in ("%Y-%m-%d", "%d/%m/%Y"):
        try:
            return pd.Timestamp(dt.datetime.strptime(str(value).strip(), fmt))
        except ValueError:
            continue
    raise ValueError(f"unparseable date: {value!r}")

df = pd.read_excel("ledger.xlsx", converters={"booked": booked})
print(df["booked"].tolist())

Listing the accepted formats in order is safer than letting a parser guess day-first versus month-first — 05/03/2026 is March in one country and May in another. For the full treatment of date columns, including serial numbers and time zones, see Parse Excel Dates into Python datetimes with pandas.

Step 5 — Collect bad values instead of stopping

Raising on the first bad value is right for a pipeline that must not publish wrong numbers, but when you are cleaning a file someone sent you, one error at a time is tedious. A converter is just a function, so it can close over a list and record problems while returning a missing value. The row position is not passed to converters, so record the value itself and find it afterwards:

def collecting(parse, problems, column):
    def wrapper(value):
        try:
            return parse(value)
        except (ValueError, TypeError) as exc:
            problems.append({"column": column, "value": value, "error": str(exc)})
            return None
    return wrapper

problems = []
checked = pd.read_excel(
    "ledger.xlsx",
    converters={
        "account": account_code,
        "amount": collecting(money, problems, "amount"),
        "active": collecting(flag, problems, "active"),
        "booked": collecting(booked, problems, "booked"),
    },
)
print(len(problems))            # 0 for this workbook

Write problems to its own sheet in the workbook you return, next to the cleaned data, and the person who owns the source file can fix every issue in one pass. The same pattern — clean what you can, list what you cannot — is expanded into a full validation step in Validate Excel Columns Before Import with pandas.

How converters interact with other options

You can check the dtype rule yourself; the warning names the column:

import warnings

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    pd.read_excel("ledger.xlsx", converters={"account": account_code}, dtype={"account": "string"})
print([str(w.message) for w in caught])

Keys can also be positions, which is useful when the header row is missing or unreliable — converters={0: account_code} targets the first column regardless of its label.

Common pitfalls

• Assuming the converter sees text. It sees whatever the engine produced: int, float, bool, datetime or str. Handle each type you can receive, starting with the well-formed case.
• Returning a value for empty cells by accident. Empty cells arrive as "". If your converter turns "" into 0 or "00000", blanks stop being missing. Return "", None or NaN for blanks.
• Converting the same column twice. A converter plus parse_dates or dtype on the same column produces a warning or double work. Pick one place for each column's rules.
• Swallowing errors. A try/except: return None inside a converter turns every malformed value into a silent gap. Raise, or collect bad values in a list the function can append to, then report them.

Performance and scale notes

A converter is a Python function called once per cell, so a column of 200,000 values costs 200,000 calls. That is typically a second or two — fine for a daily import, noticeable in a loop over hundreds of files. When a sheet is large, read the column as text with dtype={"amount": "string"} and clean it with vectorised string methods (.str.replace, pd.to_numeric), which run in compiled code. The vectorised approach is shown in Convert Excel Text Columns to Numbers with pandas. Keep converters for the columns where each value needs a real decision.

Conclusion

converters attaches your own cleaning function to a column, runs it on every raw cell before type inference, and hands the result to pandas as final. Use it to keep codes as text, parse currency and accounting formats, normalise flags and accept mixed date cells — handling each Python type the engine can produce, returning blanks for empty cells and raising on values you do not recognise. For very large columns, read as text and clean with vectorised methods instead.

Frequently asked questions

What is the difference between converters and dtype in read_excel? dtype tells pandas which type to cast a whole column to after reading. A converter is your own function, called once per cell with the raw value, so it can do anything — strip symbols, pad codes, map words to booleans. If you give both for one column, pandas warns and uses only the converter.

What does a converter receive for an empty cell? An empty string. pandas then treats an empty result as missing and stores NaN, so a converter that returns the value unchanged still produces NaN for blanks — but a converter that transforms '' into something else will keep that something.

Are converters slow? They run in Python once per cell, so on hundreds of thousands of rows they are noticeably slower than reading and cleaning with vectorised pandas string methods afterwards. For small and medium sheets the difference does not matter.

Can I use a converter on a column by position? Yes. The keys of the converters dict can be column labels or zero-based column positions, which helps when a header is missing or unreliable.

Related

• Up one level: Reading Excel Files with pandas — the read_excel options in context.
• Check Excel Data Types with pandas — verify the types you ended up with.
• Cleaning Excel Data With Pandas — the clean-up that comes after the read.
• Read Only Some Rows of an Excel File with pandas — limit what you read in the first place.