Guide

Getting Started With Python Excel AutomationDeep dive

Convert .xls to .xlsx with Python

Q: Why does my conversion fail with "Invalid Excel character" on a sheet name?

Excel forbids the characters colon, backslash, forward slash, question mark, asterisk and square brackets in sheet names, and caps them at 31 characters. Sanitise each name before passing it to to_excel.

Q: Can I convert in place and overwrite the .xls?

You cannot write .xlsx content into a .xls filename and expect Excel to open it. Write a new file with the .xlsx suffix and keep the original until the conversion is verified — legacy files are often the only copy.

Batch-convert legacy .xls workbooks to .xlsx in Python — a values-only pandas converter, a formatting-preserving LibreOffice path, and the sheet-name and dtype traps.

Legacy .xls files are a slow leak in a reporting pipeline: every downstream step needs a special reader, nothing can write back to them, and they cap out at 65,536 rows. The durable fix is to convert them to .xlsx once at the ingest boundary. This guide gives you two converters — a fast values-only one built on pandas, and a full-fidelity one that shells out to headless LibreOffice — plus the sheet-name and dtype traps that break naive scripts on real files. It builds on the reading techniques in reading .xls files with xlrd and pandas.

Prerequisites

Bash

pip install pandas xlrd xlsxwriter     # the values-only converter

For the fidelity-preserving route you also need LibreOffice installed as a program (not a pip package), exactly as in converting Excel to PDF:

Bash

soffice --version      # Debian/Ubuntu: sudo apt install libreoffice-calc

Decide which you need before writing code. If the .xls is a data export that your pipeline reads and reshapes, the pandas route is right and simpler. If it is a formatted report somebody will open and look at, use LibreOffice.

Step 1 — Convert one file, values only

The core is three lines: read every sheet, open a writer, write each sheet back. The rest is the guard rails.

Python

from pathlib import Path
import pandas as pd

def xls_to_xlsx(src, dest=None):
    """Convert one .xls to .xlsx, preserving every sheet's values."""
    src = Path(src)
    dest = Path(dest) if dest else src.with_suffix(".xlsx")

    # sheet_name=None -> an ordered dict of {sheet name: DataFrame}
    sheets = pd.read_excel(src, sheet_name=None, engine="xlrd")

    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        for name, frame in sheets.items():
            frame.to_excel(writer, sheet_name=name, index=False)
    return dest

print(xls_to_xlsx("legacy.xls"))     # legacy.xlsx

Run that on a real corpus of legacy files and it will fail, usually on the second or third one. Two things break it.

Step 2 — Sanitise sheet names

Excel forbids : \ / ? * [ ] in sheet names and caps them at 31 characters, but .xls files created by other tools routinely violate both. xlsxwriter raises rather than silently truncating, so the converter dies mid-batch.

Python

import re

INVALID = re.compile(r"[:\\/?*\[\]]")

def safe_sheet_name(name, used):
    """Return a name Excel will accept, unique within the workbook."""
    clean = INVALID.sub("_", str(name)).strip() or "Sheet"
    clean = clean[:31]

    # Uniqueness after truncation: "Regional summary north" and
    # "Regional summary south" can collide once cut to 31 chars.
    base, n = clean, 2
    while clean.lower() in used:
        suffix = f"_{n}"
        clean = base[: 31 - len(suffix)] + suffix
        n += 1

    used.add(clean.lower())
    return clean

Excel also treats sheet names case-insensitively, which is why the used set is keyed on the lowercase form. Wire it in:

Python

def xls_to_xlsx(src, dest=None):
    src = Path(src)
    dest = Path(dest) if dest else src.with_suffix(".xlsx")
    sheets = pd.read_excel(src, sheet_name=None, engine="xlrd")

    used, report = set(), {}
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        for name, frame in sheets.items():
            target = safe_sheet_name(name, used)
            frame.to_excel(writer, sheet_name=target, index=False)
            report[target] = len(frame)
    return dest, report

Returning the per-sheet row count gives you something to assert on, which is the cheapest possible defence against a conversion that silently produced an empty workbook.

Step 3 — Keep the values honest

pandas infers dtypes on read, and its inferences are wrong in ways that matter for identifiers. A column of zero-padded account codes becomes integers and loses the padding; a mixed text/number column becomes object and writes fine; a numeric column with one stray blank becomes float, so 1042 writes as 1042.0.

For a pure conversion — where the goal is a faithful .xlsx copy and any analysis happens later — read everything as text. Nothing is inferred, so nothing is corrupted:

Python

# dtype=str: every cell becomes a string, exactly as stored.
sheets = pd.read_excel(src, sheet_name=None, engine="xlrd", dtype=str)

That is the safe default for archival conversions. When you know the schema and want typed output, name the columns you care about instead:

Python

sheets = pd.read_excel(
    src,
    sheet_name=None,
    engine="xlrd",
    dtype={"account_code": str, "units": "Int64"},   # Int64 tolerates blanks
    parse_dates=["invoiced"],
)

Int64 (capital I) is pandas' nullable integer type — it holds a missing value without promoting the column to float, which is exactly the 1042.0 problem. The wider treatment of type coercion on import is in checking Excel data types with pandas.

Step 4 — The full-fidelity route

When the file is a formatted report, pandas is the wrong tool — it will hand you a plain grid where you had merged headers, colours and a chart. LibreOffice re-saves the workbook in the new format, keeping all of it:

Python

import shutil, subprocess, tempfile
from pathlib import Path

def xls_to_xlsx_libreoffice(paths, out_dir="converted", timeout=600):
    """Convert .xls files to .xlsx with headless LibreOffice, keeping formatting."""
    soffice = shutil.which("soffice") or shutil.which("libreoffice")
    if soffice is None:
        raise RuntimeError("LibreOffice not found; install it or use the pandas route")

    out = Path(out_dir).resolve()
    out.mkdir(parents=True, exist_ok=True)
    srcs = [str(Path(p).resolve()) for p in paths]

    # A throwaway profile keeps concurrent runs from deadlocking on the lock file.
    with tempfile.TemporaryDirectory() as profile:
        result = subprocess.run(
            [soffice, f"-env:UserInstallation=file://{profile}",
             "--headless", "--convert-to", "xlsx", "--outdir", str(out), *srcs],
            capture_output=True, text=True, timeout=timeout,
        )
    if result.returncode != 0:
        raise RuntimeError(result.stderr.strip())

    produced = [out / (Path(s).stem + ".xlsx") for s in srcs]
    missing = [p.name for p in produced if not p.is_file()]
    if missing:
        raise RuntimeError(f"no output for: {', '.join(missing)}")
    return produced

Passing every path to one soffice call matters: LibreOffice takes a second or two to start, and a per-file loop pays that tax on every iteration.

Common pitfalls and fixes

Symptom	Cause	Fix
`InvalidWorksheetName`	Sheet name too long or has forbidden characters	Sanitise with `safe_sheet_name` before `to_excel`.
Codes lost their leading zeros	pandas inferred an integer dtype	Read with `dtype=str`, or name the column in `dtype={...}`.
`1042` became `1042.0`	One blank promoted the column to float	Use the nullable `Int64` dtype.
Output opens but is empty	Header row misdetected, all rows read as header	Pass `header=None` and set names yourself.
`ImportError: Missing optional dependency 'xlrd'`	Reader not installed	`pip install xlrd`
LibreOffice hangs under cron	Profile lock held by another instance	Pass a fresh `-env:UserInstallation` per run, as above.
Converted file rejected by Excel	Wrote `.xlsx` bytes to a `.xls` filename	Always write the new suffix; do not convert in place.

Performance and scale notes

The pandas converter is I/O and parse bound, and xlrd is pure Python — expect roughly a second per megabyte of input on ordinary hardware. LibreOffice is slower per file but amortises well when batched.

For a directory of a few hundred files, run the conversions concurrently. The work releases the GIL only partially, so processes beat threads here:

Python

from concurrent.futures import ProcessPoolExecutor
from pathlib import Path

def convert_one(path):
    try:
        dest, report = xls_to_xlsx(path)
        return path.name, sum(report.values()), None
    except Exception as exc:               # keep the batch alive
        return path.name, 0, str(exc)

if __name__ == "__main__":
    files = sorted(Path("legacy").glob("*.xls"))
    with ProcessPoolExecutor(max_workers=4) as pool:
        for name, rows, error in pool.map(convert_one, files):
            status = f"FAILED: {error}" if error else f"{rows} rows"
            print(f"{name:<40} {status}")

Catching per-file exceptions and reporting them is not optional in a batch job — a single corrupt legacy file should not abort a run over three hundred others. The same reasoning drives the retry and reporting patterns in error handling and logging in Excel automation. Note the if __name__ == "__main__": guard: ProcessPoolExecutor re-imports the module in each worker, and without the guard the pool spawns recursively.

Finally, verify rather than assume. A conversion that produced a valid but empty workbook passes every exception check:

Python

before = pd.read_excel("legacy.xls", sheet_name=None, engine="xlrd")
after = pd.read_excel("legacy.xlsx", sheet_name=None, engine="openpyxl")

assert len(before) == len(after), "sheet count changed"
for (a_name, a), (b_name, b) in zip(before.items(), after.items()):
    assert a.shape == b.shape, f"{a_name}: {a.shape} -> {b.shape}"
print("conversion verified")

Conclusion

Converting .xls to .xlsx is a one-line idea wrapped in three practical concerns: sheet names Excel will reject, dtypes pandas will guess wrong, and the question of whether you need the values or the whole formatted document. Read with dtype=str for archival copies, sanitise sheet names, and verify shapes afterwards. When the file is a report rather than a data dump, hand it to headless LibreOffice and let it preserve what pandas cannot see. Convert once at ingest and the rest of your pipeline never has to know the legacy format existed.

Frequently asked questions

Does the pandas converter keep colours, merged cells and formulas? No. pandas reads cell values, so the output is clean data with default formatting. Use the headless LibreOffice path when the appearance or the live formulas matter.

Why does my conversion fail with "Invalid Excel character" on a sheet name? Excel forbids : \ / ? * [ ] in sheet names and caps them at 31 characters. Sanitise each name before passing it to to_excel.

Can I convert in place and overwrite the .xls? You cannot write .xlsx content into a .xls filename and expect Excel to open it. Write a new file with the .xlsx suffix and keep the original until the conversion is verified — legacy files are often the only copy.

Which is faster for a hundred files? The pandas path, comfortably. LibreOffice pays a process startup cost, though passing every file to one soffice invocation amortises it. pandas stays in-process and converts a typical report in well under a second.

How do I check the conversion did not lose rows? Compare shapes sheet by sheet before and after. The converter above returns a per-sheet row count so you can assert on it, which catches both truncation and an accidentally empty sheet.

Up to the parent: Handling Excel File Formats and Conversions — the format map and engine table.
Read .xls Files in Python with xlrd and pandas — the reading half of this workflow.
Convert Excel to CSV with Python — when the target is plain text rather than a workbook.
Convert an Excel File to PDF with Python — the same headless LibreOffice technique aimed at PDF.
Check Excel Data Types with pandas — going deeper on the dtype traps above.