Batch Converting XLS Files to XLSX With LibreOffice

A shared drive holds eight years of reports saved as .xls, the binary format Excel used before 2007. Every tool that reads them has complications: xlrd is the only maintained Python reader, newer Excel features are absent, and some security policies block the old format. Converting the archive to .xlsx once removes the problem. Doing it by opening and saving each file is out of the question at a few thousand files. This post shows a bulk conversion with LibreOffice, two alternatives and the checks that tell you the converted files can be trusted. It is a spoke of automating Excel work without clicking.

Why convert at all

The .xls format is a binary compound file. It limits sheets to 65,536 rows and 256 columns, has no support for features added since Excel 2007, and is read by fewer libraries. Python's pandas needs xlrd for it, and that library only supports xls (fixing the Python xlrd error covers the history). The .xlsx format is a zip of XML parts that every modern tool reads, with a million rows and 16,384 columns.

Converting is worthwhile when you will read the files repeatedly, when you want to retire an old reader, or when security rules block legacy formats. It is less useful for files you will never open again. The decision is about future use, not about the files.

LibreOffice in headless mode

LibreOffice can convert without a graphical session. Install it, then run:

mkdir -p converted
soffice --headless --convert-to xlsx --outdir converted /data/archive/*.xls

--headless runs without a window, --convert-to xlsx chooses the target format and --outdir keeps the results apart from the originals. Each file produces name.xlsx in the output folder, and the command prints a line per file. Some practical points:

• One instance at a time. Running several soffice processes at once with the same user profile makes later ones fail or hang. Use one process for a list of files, as above, or give each parallel process its own profile with -env:UserInstallation=file:///tmp/lo_profile_N.

• Shell limits. A glob with thousands of files can exceed the argument length limit. Use find with xargs, in batches: find /data/archive -name '*.xls' -print0 | xargs -0 -n 100 soffice --headless --convert-to xlsx --outdir converted.

• Timeouts. A corrupt file can hang the converter. Wrap the call with timeout 120 per batch, and run again on the failures one by one.

• Filter names. For macro-enabled workbooks, convert to xlsm: --convert-to xlsm. Check the supported targets for your version.

• Subfolders. --outdir is flat. If names collide across folders, convert folder by folder, or prefix names.

The LibreOffice project documents the options in its help for command-line parameters. The tool runs on Linux servers, which makes it the usual choice for pipelines.

What can change

Conversion maps one format to another through an application's interpretation, and fidelity is not perfect.

For data archives, the values are what matter, and they convert well. For presentation-quality reports with charts, check visually. The date system can also be a concern for old Mac files that use the 1904 base: confirm that dates match the originals (Mac and Windows date systems in Excel).

A Python route for simple files

For plain data files, you can skip office software. Read with xlrd and write with openpyxl or pandas:

from pathlib import Path
import pandas as pd

src, dst = Path("archive"), Path("converted")
dst.mkdir(exist_ok=True)
failed = []

for path in sorted(src.glob("*.xls")):
    try:
        sheets = pd.read_excel(path, sheet_name=None, dtype=object, engine="xlrd")
        with pd.ExcelWriter(dst / f"{path.stem}.xlsx", engine="openpyxl") as xw:
            for name, df in sheets.items():
                df.to_excel(xw, sheet_name=name[:31], index=False)
    except Exception as e:
        failed.append((path.name, repr(e)))

print(len(failed), "failed")

sheet_name=None reads every sheet into a dictionary. This keeps values and sheet names, and drops all formatting, formulas (you get the cached results) and charts. It suits archives of data tables and is the wrong tool for finished reports. dtype=object keeps cell types as read, rather than guessing. Dates come through as datetimes, and cells formatted as dates but holding numbers can be misread, so inspect samples.

Excel itself on Windows

Excel produces the most faithful conversion, because it is the reference implementation. A script can drive it, with the caveats of unattended Office automation:

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
Get-ChildItem C:\archive -Filter *.xls | ForEach-Object {
    $wb = $xl.Workbooks.Open($_.FullName)
    $out = Join-Path C:\converted ($_.BaseName + ".xlsx")
    $wb.SaveAs($out, 51)          # 51 = xlOpenXMLWorkbook
    $wb.Close($false)
}
$xl.Quit()

The constant 51 is xlOpenXMLWorkbook (xlsx); 52 is the macro-enabled xlsm. Files with macros or links may show prompts, so test with DisplayAlerts off on a sample first. This route is for a one-time batch on a desktop, not for a server, since Microsoft does not support unattended server use of desktop Office.

Verify the conversion

A converted archive that has not been checked is a hope. Compare each pair:

import pandas as pd

def same_values(old, new):
    a = pd.read_excel(old, sheet_name=None, dtype=str, engine="xlrd")
    b = pd.read_excel(new, sheet_name=None, dtype=str, engine="openpyxl")
    if a.keys() != b.keys():
        return False, "sheet names differ"
    for name in a:
        if a[name].shape != b[name].shape:
            return False, f"{name}: shape {a[name].shape} vs {b[name].shape}"
        if not a[name].fillna("").equals(b[name].fillna("")):
            return False, f"{name}: values differ"
    return True, "ok"

Comparing as strings is crude and effective for values. Count the files: every original should have one output or an entry in the failure list. Check sheet names, row counts and a control total. For formula-heavy files, compare the formulas with openpyxl in formula mode on a sample. For visual files, open a few in Excel next to the originals. Record the result in a log, one line per file.

Handle failures

Expect a small percentage to fail: password-protected files, corrupt files, files that are not really xls (HTML or CSV renamed), and files with unsupported content. Move failures to a separate folder with the reason. Identify the real type from the first bytes (why Excel says the file format or extension is not valid). Protected files need the password, from the owner. Do not delete originals until the log shows every file accounted for.

Leave the originals alone, and never convert in place. Keep them as the evidence, in a read-only archive, until you are sure of the results, and longer if the files have legal value.

When converting the files is not necessary

You only need the data once. Read the xls with xlrd directly and load into a database, skipping the intermediate files.

The files are never opened again. An archive nobody reads is cheaper to leave alone.

The format is the requirement. A regulator or a counterparty may want the original bytes unchanged. Convert a copy.

The files contain macros that must keep working. Convert to xlsm and test the macros, or rewrite them; do not convert to xlsx.

See the LibreOffice documentation on command line parameters and Microsoft's page on file formats supported in Excel. To convert a small result to JSON, use the CSV to JSON converter.