Read Excel 2003 XML Spreadsheet Files

A file called statement.xls arrives from a bank portal or an ERP export. Excel opens it, perhaps after a warning that "the file format and extension don't match". pandas does not: read_excel says the format cannot be determined, xlrd complains that it expected a BOF record and found <?xml, and calamine cannot detect the format either. The file is not an old binary .xls at all. It is an XML Spreadsheet 2003 document — a plain-text XML format that Excel introduced in 2002 and that many systems still generate because it is easy to write without any library. No pandas engine reads it, but it is simple XML, and a short parser turns it into DataFrames. This page shows how to recognise the format, reads it with the standard library, handles the details that trip up naive parsers, and converts the result to a real .xlsx. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install pandas openpyxl xlrd

xlrd is only installed here to show its error message; the parser uses the standard library's ElementTree. The sample is a two-sheet statement of the kind a bank portal exports: a merged title row, a header, transactions with a skipped cell and a skipped row, and a formula.

Step 1 — Recognise the format

import pandas as pd
from pathlib import Path

for engine in (None, "xlrd", "calamine"):
    try:
        pd.read_excel("statement.xls", engine=engine)
    except Exception as e:
        print(f"{engine or 'default':9} {type(e).__name__}: {e}")

def is_xml_spreadsheet(path):
    head = Path(path).read_bytes()[:2048].lstrip(b"\xef\xbb\xbf").lower()
    return head.startswith(b"<?xml") and (b"excel.sheet" in head or b"office:spreadsheet" in head)

print("XML Spreadsheet 2003:", is_xml_spreadsheet("statement.xls"))

The three error messages are worth recognising, because they all mean the same thing when the file starts with XML. pandas could not determine the format from the content, xlrd expected the binary record that starts a real .xls file and found the text <?xml ve instead, and calamine could not detect a format it knows. Looking at the first bytes settles it: a real .xls starts with the binary signature D0 CF 11 E0, an .xlsx with PK (it is a zip archive), and an XML spreadsheet with <?xml, usually followed by the processing instruction <?mso-application progid="Excel.Sheet"?> and the namespace urn:schemas-microsoft-com:office:spreadsheet. The check skips a UTF-8 byte-order mark, which some exporters write before the XML declaration.

How the XML is organised

Everything lives in one namespace, urn:schemas-microsoft-com:office:spreadsheet, which appears both as the default namespace for elements and with the ss: prefix for attributes. ElementTree reports elements and attributes with the full namespace in braces, so the parser keeps the namespace in a small mapping for findall and a prefix string for attribute names. The Styles section holds number formats such as Short Date, referenced from cells by ss:StyleID; since every value already carries its type, the styles are not needed to read the data.

Step 2 — Parse every worksheet into a DataFrame

import xml.etree.ElementTree as ET
from datetime import datetime

NS = {"ss": "urn:schemas-microsoft-com:office:spreadsheet"}
A = "{urn:schemas-microsoft-com:office:spreadsheet}"

def convert(data):
    kind, text = data.get(f"{A}Type"), data.text or ""
    if kind == "Number":
        return float(text)
    if kind == "DateTime":
        return datetime.fromisoformat(text[:19])
    if kind == "Boolean":
        return text.strip() == "1"
    return text                                            # String and Error

def read_xml_spreadsheet(path):
    root = ET.parse(path).getroot()
    sheets = {}
    for ws in root.findall("ss:Worksheet", NS):
        rows, r = [], 0
        for row in ws.findall("ss:Table/ss:Row", NS):
            r = int(row.get(f"{A}Index", r + 1))           # rows can skip ahead
            values, c = {}, 0
            for cell in row.findall("ss:Cell", NS):
                c = int(cell.get(f"{A}Index", c + 1))      # cells can skip ahead too
                data = cell.find("ss:Data", NS)
                if data is not None:
                    values[c] = convert(data)
                c += int(cell.get(f"{A}MergeAcross", 0))   # a merged cell occupies extra columns
            rows.append((r, values))
        n_rows = max((r for r, _ in rows), default=0)
        n_cols = max((c for _, v in rows for c in v), default=0)
        grid = [[None] * n_cols for _ in range(n_rows)]
        for r, values in rows:
            for c, v in values.items():
                grid[r - 1][c - 1] = v
        sheets[ws.get(f"{A}Name")] = pd.DataFrame(grid)
    return sheets

sheets = read_xml_spreadsheet("statement.xls")
for name, df in sheets.items():
    print(f"--- {name}\n{df.to_string()}")

The structure is straightforward — a Workbook holds Worksheet elements, each with a Table of Row elements containing Cell elements whose Data child holds the value and its type — but three details decide whether the result is right. First, cells are positional, not addressed: a cell's column is one more than the previous cell's, unless it has an ss:Index attribute, which jumps ahead and leaves the skipped columns empty. In the sample, the second transaction has no description, so its amount carries ss:Index="3". Rows work the same way, which is why the third transaction lands on row 6 after an empty row 5. Second, a cell with ss:MergeAcross="3" spans four columns, so the next cell starts four columns later. Third, every value has a type: Number becomes a float, DateTime an ISO timestamp, Boolean a 1 or 0, and everything else text.

Formulas are stored in R1C1 notation in an ss:Formula attribute, alongside a Data element with the last calculated value. The parser reads the value, which is what an export is for; the formula itself is ignored. If the formulas matter — to rebuild a template, say — they can be read from the attribute, but they would need translating from R1C1 to A1 notation before openpyxl could write them back, since =R[-2]C*2 means "twice the cell two rows up in the same column".

Step 3 — Tidy the table and save it as .xlsx

tx = sheets["Transactions"]
tx = tx.iloc[2:].set_axis(tx.iloc[1].tolist(), axis=1).dropna(how="all").reset_index(drop=True)
tx = tx.astype({"Date": "datetime64[ns]", "Amount": "float64", "Cleared": "boolean"})
print(tx.dtypes.to_dict())
print(tx.to_string())

with pd.ExcelWriter("statement.xlsx") as xw:
    tx.to_excel(xw, sheet_name="Transactions", index=False)
    sheets["Summary"].to_excel(xw, sheet_name="Summary", index=False, header=False)
print("total:", tx["Amount"].sum())

The raw grid has the title row and the header row as data, which is how the file was laid out. Taking row 1 as the header, dropping the title and any fully empty rows, and setting proper types gives a clean table: dates as datetimes, amounts as floats, and the cleared flag as a nullable boolean. Writing it to a real .xlsx means the rest of the pipeline, and anyone downstream, can read it with ordinary tools. For files that arrive with untidy layouts, the techniques in Find the Header Row in an Excel Sheet with a Shifting Layout apply to the grid just as they do to a normal sheet.

Converting with LibreOffice instead

LibreOffice can open XML Spreadsheet 2003 files, so soffice --headless --convert-to xlsx statement.xls turns one into a real workbook without any parsing code. That is handy for occasional files, but check typed columns afterwards: in a test with the sample file, LibreOffice's conversion kept the dates, text and numbers but left the Boolean Cleared column empty. The parser above is a few dozen lines, has no external dependency and handles every type, so for a recurring import it is the more reliable route.

If the files come from outside the organisation, parse them with defusedxml.ElementTree instead of the standard library module — it has the same interface and refuses XML tricks such as entity expansion bombs. Some systems also produce HTML tables saved with an .xls extension; those start with <html or <table, and pandas.read_html reads them.

Common pitfalls

• Trusting the extension. Check the first bytes; .xls may be XML, HTML or real binary.
• Ignoring ss:Index. Values shift left into the wrong columns.
• Ignoring MergeAcross. Every cell after a merged title is one column off per merged cell.
• Everything as text. Use the ss:Type attribute to convert numbers, dates and booleans.
• Converting blindly. Check typed columns after a LibreOffice conversion.

Conclusion

An .xls that pandas, xlrd and calamine all reject, and that starts with <?xml, is an XML Spreadsheet 2003 file. Read it with ElementTree: walk worksheets, rows and cells, honour ss:Index on rows and cells and ss:MergeAcross on cells, and convert each Data value by its ss:Type. Turn each grid into a DataFrame, tidy the header and types, and write a real .xlsx for everything downstream.

Frequently asked questions

Why does pandas say the Excel file format cannot be determined? The file is neither a binary .xls nor a zipped .xlsx; check its first bytes — <?xml means an XML Spreadsheet 2003 file and <html an HTML table.

What does "Expected BOF record; found b'<?xml ve'" mean? xlrd expected a binary Excel file but found XML; the file is an XML spreadsheet with an .xls extension.

Can pandas read Excel 2003 XML files? Not with read_excel; parse the XML with ElementTree into a DataFrame, or convert the file with LibreOffice first.

How do I handle skipped cells in SpreadsheetML? A cell with ss:Index starts at that column; columns in between are empty. Rows with ss:Index work the same way.

Related

• Up one level: Handling Excel File Formats and Conversions — formats and conversions.
• Pick an Excel Engine for .xlsx, .xlsm, .xls, .xlsb and .ods — which engine reads what.
• Convert XML to Excel with pandas read_xml — other XML data.
• Read Untrusted Excel Files Safely in Python — files from outside.