Reading Excel XML Spreadsheet 2003 Files With Python

Before Office 2007, Excel could save a workbook as "XML Spreadsheet 2003". The format was meant to be open, and unlike the zipped formats that followed it is a single XML file you can read in a text editor. It turns up in exports from old systems that were told "give us something Excel opens", and nothing but Excel reads it, which is how people end up with a .xls file that is neither the binary format nor xlsx.

What the file looks like

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <Worksheet ss:Name="Orders"><Table>
  <Row><Cell><Data ss:Type="String">id</Data></Cell><Cell><Data ss:Type="String">customer</Data></Cell>...</Row>
  <Row><Cell><Data ss:Type="Number">1001</Data></Cell><Cell><Data ss:Type="String">Ana &amp; Co</Data></Cell><Cell><Data ss:Type="Number">19.99</Data></Cell><Cell ss:StyleID="d"><Data ss:Type="DateTime">2026-03-02T00:00:00.000</Data></Cell><Cell><Data ss:Type="String">02134</Data></Cell></Row>
  <Row><Cell><Data ss:Type="Number">1002</Data></Cell><Cell ss:Index="3"><Data ss:Type="Number">44</Data></Cell><Cell><Data ss:Type="Boolean">1</Data></Cell></Row>
 </Table></Worksheet>
</Workbook>

Three points identify the format. The processing instruction <?mso-application progid="Excel.Sheet"?> tells Windows to open the file with Excel. The root Workbook is in urn:schemas-microsoft-com:office:spreadsheet. And the structure is the grid itself: a Worksheet has a Table, a Table has Rows, a Row has Cells, a Cell has a Data whose ss:Type says how to read its text. Note that the ss prefix is bound to the same URI as the default namespace, so ss:Name is an attribute in the same namespace as the elements.

Reading it

NS = {"s": "urn:schemas-microsoft-com:office:spreadsheet"}; SS = "{urn:schemas-microsoft-com:office:spreadsheet}"
r = etree.parse("ss2003.xml").getroot()
for t in r.iterfind(".//s:Worksheet", NS):
    print("sheet", t.get(SS + "Name"))
    for row in t.iterfind("s:Table/s:Row", NS):
        out = []; col = 0
        for c in row.iterfind("s:Cell", NS):
            idx = c.get(SS + "Index")
            if idx:
                while col < int(idx) - 1: out.append(None); col += 1
            d = c.find("s:Data", NS); out.append((d.get(SS + "Type"), d.text)); col += 1
        print(out)

root: Workbook urn:schemas-microsoft-com:office:spreadsheet | pi: ['progid="Excel.Sheet"']
sheet Orders
[('String', 'id'), ('String', 'customer'), ('String', 'total'), ('String', 'placed'), ('String', 'zip')]
[('Number', '1001'), ('String', 'Ana & Co'), ('Number', '19.99'), ('DateTime', '2026-03-02T00:00:00.000'), ('String', '02134')]
[('Number', '1002'), None, ('Number', '44'), ('Boolean', '1')]

The header row and the first data row read straight: the types are explicit, the ampersand is decoded and the ZIP code 02134 is a String, so its leading zero is safe. This is a point in the format's favor over CSV, where the type is lost. The DateTime is an ISO 8601 text without a zone, which you convert yourself (datetime.fromisoformat), and a Boolean is 1 or 0. Numbers are text in the invariant format with a period, to be read with Decimal and not locale-aware parsing.

The third row shows the trap. The second order has no customer, and the file does not write an empty cell. The Cell with ss:Index="3" says "this cell is in column 3", so the position of the customer is skipped. A reader that takes the cells in order would put 44 in the customer column and 1 in the total column, and the row would look plausible. The loop fills the gap with None until the column index is reached, which gave [('Number','1001'), None, ('Number','44'), ('Boolean','1')]. Note the other direction too: Excel can also write ss:MergeAcross to merge cells and ss:Index on Row to skip rows, which a full reader should handle. The sample has no merged cells; I did not test them.

Why pandas will not read it

pd.read_excel("ss2003.xml")

ValueError: Excel file format cannot be determined, you must specify an engine manually.

read_excel supports xlsx (zip, via openpyxl) and the binary xls format; this file is neither. Renaming it .xls does not help. So you write the reader above, build rows, and hand them to pandas.DataFrame. If you have Excel or LibreOffice available, opening the file and saving as xlsx is the shortcut, with the usual caution that the application interprets values; do the conversion in code if identifiers matter.

Why anyone still has these

The format came from Office XP and 2003, when XML was the new answer to "an open format". Systems that generated reports for Excel 2003 wrote it because it needed no library, only string output. Those systems outlived the Excel version, and their files are still produced and still opened, so a reader for the format is a small, durable piece of code.

Detecting the format

A .xls file might be the binary format (starting with the bytes d0 cf 11 e0), an xlsx (starting with PK), this XML format (starting <?xml and containing the spreadsheet namespace) or an HTML table with an Excel extension, a common export trick. Sniff the first bytes, as in common XML formats you will meet in real work: for XML, read the root name and namespace.

Practical notes

Styles. Formatting is in a Styles section and referenced by ss:StyleID. A date cell's number format lives there, and the Data type already says DateTime, so you rarely need the styles to read values.

Several worksheets. Loop over Worksheet elements, using ss:Name for the sheet name.

Size. The format is verbose: each cell is about 50 bytes of XML. A sheet of a million cells is tens of megabytes, and iterparse on Row keeps memory flat (parsing large XML files with iterparse).

Formulas. A Cell may have ss:Formula with the formula text in R1C1 notation; the Data holds the last computed value.

Security. It is XML from outside: parse without entities or DTDs (XML security problems to close before production).

Turning it into a DataFrame

Once the rows are lists, the rest is ordinary pandas. Use the first row as the header, convert each column by the ss:Type you saw (Number to Decimal or float, DateTime to datetime, Boolean to bool, String untouched), and pad short rows with None to the header width, so that a trailing skipped cell does not make the rows ragged. Keep the type per cell until the end: a column whose cells are Number in some rows and String in others is a column with a problem worth reporting (a note typed into a numeric column), and a reader that coerces everything to one type hides it. Count the rows and the non-empty cells against what the sender says the sheet contains.

The format also lets one workbook hold several sheets with different shapes, so build one DataFrame per worksheet and keep the sheet name as the key.

Related reading

For the cluster see moving XML into and out of spreadsheets; for the modern formats, reading Office Open XML from docx and xlsx files. The XML parsing rules are in the XML 1.0 specification, the namespace rules in Namespaces in XML, and Microsoft documented the format as the "XML Spreadsheet Reference" in its Office XP developer material; I could not confirm a live page for it. Test a path in the XPath tester.

When Excel itself should convert the file

The file has charts, macros or complex formatting. The XML format keeps some and loses others; open it and save as xlsx.

You will only do this once. Opening and saving is faster than writing a reader.

The workbook has formulas you need to keep working. Convert with the application so the formulas are translated.

There are many such files. Write the reader once and test it on several, since exporters differ.