Convert CSV Files to Excel with Python

CSV is how most systems export data, and Excel is how most people want to receive it. Opening a CSV directly in Excel is the source of many familiar problems — dates flipped between day and month, codes stripped of their leading zeros, accented names garbled — because Excel guesses at every column. Converting with Python moves those decisions into code, where they are made once, explicitly, for every file. This guide, part of Handling Excel File Formats and Conversions, converts single files and folders of files, and handles the limits Excel imposes on the result.

Prerequisites

pip install pandas xlsxwriter

The examples create three small CSVs that behave like real exports — different encodings, one with semicolons, and a code column with leading zeros:

from pathlib import Path

Path("exports").mkdir(exist_ok=True)
Path("exports/sales_2026_01.csv").write_text(
    "customer_id,name,order_date,amount\n00123,Zoë Adams,2026-01-05,1200.50\n00456,Ben Okafor,2026-01-19,980.00\n",
    encoding="utf-8")
Path("exports/sales_2026_02.csv").write_text(
    "customer_id,name,order_date,amount\n00789,José Núñez,2026-02-02,455.25\n",
    encoding="cp1252")
Path("exports/sales_2026_03.csv").write_text(
    "customer_id;name;order_date;amount\n00123;Zoë Adams;2026-03-11;310,00\n",
    encoding="utf-8")

Step 1 — Convert one file, with explicit types

The one-liner works, but it lets pandas infer every type. State the ones that matter: identifiers as text, dates as dates.

import pandas as pd

df = pd.read_csv(
    "exports/sales_2026_01.csv",
    dtype={"customer_id": str},
    parse_dates=["order_date"],
)
df.to_excel("sales_2026_01.xlsx", index=False)
print(df.dtypes)
print(df["customer_id"].tolist())   # ['00123', '00456']

dtype={"customer_id": str} keeps 00123 intact — read as a number it would become 123, and a lookup against a customer list keyed on text would miss every row. index=False stops pandas from writing its row numbers as an extra first column.

Step 2 — Detect the encoding and the delimiter

Exports from different systems disagree on both. A small reader that tries the likely encodings in order and lets Python's CSV sniffer choose the delimiter copes with most of them:

import csv

def read_export(path, **kwargs) -> pd.DataFrame:
    raw = Path(path).read_bytes()
    for encoding in ("utf-8-sig", "cp1252"):
        try:
            text = raw.decode(encoding)
            break
        except UnicodeDecodeError:
            continue
    else:
        raise ValueError(f"{path}: not UTF-8 or Windows-1252")
    dialect = csv.Sniffer().sniff(text.splitlines()[0], delimiters=",;\t|")
    return pd.read_csv(path, encoding=encoding, sep=dialect.delimiter, **kwargs)

for f in sorted(Path("exports").glob("*.csv")):
    frame = read_export(f, dtype={"customer_id": str})
    print(f.name, frame.shape, frame["name"].iloc[0])

utf-8-sig comes first because it reads plain UTF-8 and also strips the byte-order mark that Excel adds when it saves "CSV UTF-8". Windows-1252 is the usual fallback for files saved by older Excel on Windows. Order matters: almost any bytes decode as Windows-1252, so trying it first would "succeed" on UTF-8 files and garble them.

Step 3 — Fix decimal commas

The March file uses semicolons and a decimal comma (310,00) — the European convention. Tell read_csv about both, and the amount arrives as a number:

march = read_export("exports/sales_2026_03.csv", dtype={"customer_id": str}, decimal=",")
print(march["amount"].tolist())    # [310.0]

Pass decimal="," only for files that use it: applied to a comma-delimited file with ordinary decimals it does nothing useful, and applied to a semicolon file with thousands separators you also need thousands=".". Knowing each source's conventions and recording them in code is what makes the conversion reliable.

Step 4 — Many CSVs, one workbook

Readers usually want one workbook, not a folder of them. Write each CSV to its own sheet, named after the file, through a single writer:

with pd.ExcelWriter("sales_q1.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
    for f in sorted(Path("exports").glob("*.csv")):
        decimal = "," if f.name.endswith("_03.csv") else "."
        frame = read_export(f, dtype={"customer_id": str}, parse_dates=["order_date"], decimal=decimal)
        sheet = f.stem.replace("sales_", "")[:31]          # '2026_01'
        frame.to_excel(writer, sheet_name=sheet, index=False)
        ws = writer.sheets[sheet]
        ws.freeze_panes(1, 0)
        ws.set_column(0, len(frame.columns) - 1, 16)

print(pd.ExcelFile("sales_q1.xlsx").sheet_names)   # ['2026_01', '2026_02', '2026_03']

Sheet names are limited to 31 characters and may not contain [ ] : * ? / \; a file name is usually safe after trimming, but see Create a Sheet for Each Group in a DataFrame for a helper that cleans and de-duplicates arbitrary names. If the files share columns and should be one table rather than several sheets, concatenate them first with a source_file column instead. One long table is easier to filter, pivot and chart than a dozen tabs with identical headers, and it reads back into pandas in a single call; separate sheets suit readers who look at one period at a time.

Step 5 — Files bigger than one sheet

An Excel sheet holds 1,048,576 rows including the header, so 1,048,575 data rows at most. pandas checks the data rows against the full limit, which leaves an off-by-one gap: a frame of exactly 1,048,576 rows passes the check, and the xlsxwriter engine then writes the header plus every row but the last — silently. The last row is simply missing. Check the limit yourself and split large files across sheets:

MAX_DATA_ROWS = 1_048_576 - 1          # one row is the header

def write_split(frame: pd.DataFrame, path: str, base: str = "data") -> list[str]:
    names = []
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        for part, start in enumerate(range(0, len(frame), MAX_DATA_ROWS), start=1):
            name = f"{base}_{part}"
            frame.iloc[start:start + MAX_DATA_ROWS].to_excel(writer, sheet_name=name, index=False)
            names.append(name)
    return names

small_demo = pd.DataFrame({"n": range(10)})
MAX_DATA_ROWS = 4                       # tiny limit just to show the split
print(write_split(small_demo, "split_demo.xlsx"))   # ['data_1', 'data_2', 'data_3']
MAX_DATA_ROWS = 1_048_576 - 1

The demonstration uses a tiny limit so the split is visible; with the real limit, the same function turns a 2.5-million-row CSV into three sheets. The underlying error when you exceed the limit by more than one row is explained in Fix "This sheet is too large" When Writing Excel with pandas.

Step 6 — Verify the conversion

A conversion job should prove it lost nothing. Read the workbook back and compare, per source file, the row count and the total of a numeric column with the CSV it came from. It takes a second and catches every silent failure described above — a dropped last row, a mis-detected delimiter that collapsed columns, a decimal comma read as text:

checks = []
workbook = pd.read_excel("sales_q1.xlsx", sheet_name=None)
for f in sorted(Path("exports").glob("*.csv")):
    decimal = "," if f.name.endswith("_03.csv") else "."
    source = read_export(f, dtype={"customer_id": str}, decimal=decimal)
    written = workbook[f.stem.replace("sales_", "")]
    checks.append({
        "file": f.name,
        "rows_match": len(source) == len(written),
        "amount_match": abs(source["amount"].sum() - written["amount"].sum()) < 0.005,
    })
result = pd.DataFrame(checks)
print(result)
assert result[["rows_match", "amount_match"]].all().all()

Keep the assertion in the scheduled version of the job: a conversion that fails loudly on a bad day is far better than one that delivers a workbook with a missing month nobody notices until the quarter closes.

Common pitfalls

• Letting Excel open the CSV instead. Double-clicking a CSV applies Excel's own guesses; converting in Python is the only way to control types.
• Trying Windows-1252 before UTF-8. It decodes almost anything, so it silently garbles UTF-8 text. Try utf-8-sig first.
• Dates as text. Without parse_dates, dates are written as strings, which Excel cannot sort or filter as dates. Parse them, and set datetime_format on the writer for a clean display.
• Formulas injected from data. A CSV value starting with =, +, - or @ is still text when written by pandas; it only becomes a formula if someone re-imports the CSV in Excel. Converting through pandas is the safer path for untrusted exports.

Performance and scale notes

Reading a CSV is fast; writing xlsx is the slow part, because every cell becomes XML. For hundreds of thousands of rows use the xlsxwriter engine with engine_kwargs={"options": {"constant_memory": True}}, which streams rows to disk instead of holding the whole sheet in memory. For millions of rows, ask whether the reader really needs them in Excel — often a summary sheet plus the full data as CSV or Parquet serves them better. The trade-offs are in Excel vs CSV vs Parquet for Python Data Pipelines.

Conclusion

Convert CSVs with explicit settings: identifier columns as text, dates parsed, the encoding tried in the right order, the delimiter and decimal mark matched to the source. Combine a folder into one workbook with a sheet per file through a single writer, and respect the real sheet limit of 1,048,575 data rows — checking it yourself, because a frame of exactly 1,048,576 rows loses its last row silently with xlsxwriter.

Frequently asked questions

What is the simplest way to convert a CSV to Excel in Python? pd.read_csv("data.csv").to_excel("data.xlsx", index=False). The rest of this guide is about the details that one line gets wrong — encodings, delimiters, codes with leading zeros, dates, and files too big for one sheet.

Why are my accented characters garbled after converting? The CSV was not saved as UTF-8 — Excel on Windows often writes Windows-1252. Pass the right encoding to read_csv, or try "utf-8-sig" first and fall back to "cp1252".

How many rows can one Excel sheet hold? 1,048,576 rows including the header row, so at most 1,048,575 data rows under a header. Larger files must be split across sheets or kept in another format.

Why did my ZIP codes lose their leading zeros? read_csv inferred the column as numbers. Read identifier columns as text with dtype={"zip": str} so 01234 stays 01234.

Related

• Up one level: Handling Excel File Formats and Conversions — every format in and out.
• Convert Excel to CSV with Python — the reverse direction.
• Keep Leading Zeros When Reading Excel with pandas — the same problem on the Excel side.
• Write Large DataFrames to Excel with write_only Mode — memory-friendly writing.