Opening XML Files in Excel Without Mangled Data or Loss

Double-clicking an XML file in Windows opens it in a browser or an editor, or in Excel if you set it so. People do it to look at data: a vendor export, a list of orders, a feed saved to disk. Excel will show it, with some choices and some conversions along the way, and the conversions are where data gets damaged without anyone noticing.

What Excel offers

When you open an .xml file from the File menu, Excel asks how. As described in Microsoft's documentation, the choices are to open it as an XML table, as a read-only workbook, or to open it using the XSL stylesheet reference in the file, if there is one. They differ in what you get.

As an XML table. Excel reads the file, and if it does not point to a schema, infers one from the data, creates an XML map and shows the data in a table. You can then refresh and export through the map (importing XML into Excel with an XML map). This is the useful option for flat, repeating data.

As a read-only workbook. Excel shows the data in a sheet without creating a map, for viewing only.

With a stylesheet. If the file names an XSLT stylesheet in a processing instruction, Excel can apply it first, and show the result. The stylesheet decides the layout. Stylesheet-based rendering is covered in transforming XML to HTML with XSLT.

The right choice depends on whether you need to edit and send the data back (the table) or only to read it.

Where the data changes

The risk is the same as with CSV: a spreadsheet interprets values. XML is all text, and Excel's cells have types, so something decides which text becomes a number, a date or a string. For an XML table that something is the schema, and if the schema was inferred, it is a guess from the sample rows.

The identifiers are the standard victim. A ZIP code 02134, a part number 000417 or an account number with leading zeros looks like a number, and a number cell shows 2134. Long digit strings (a 16-digit card-like identifier, a 20-digit order number) lose precision beyond about 15 digits in a numeric cell, and display in scientific notation. Text that looks like a date may be turned into one. The CSV articles describe the same damage, and the lesson carries over: identifiers are strings (Excel drops leading zeros when opening a CSV). I could not observe Excel's exact choices here, so test your own files: open one that has a ZIP code and a 16-digit id and look at the cells.

What a script does instead is explicit. I wrote a workbook with a string cell and a number cell:

ws.append(["02134", 44])   # text stays text, number stays number

Reading it back, 02134 was still the text 02134 and 44 was a number. A script writes the type you choose into each cell, and that is the whole difference from a double-click.

Nesting

Excel shows a grid, and XML is a tree. A flat list of records opens as a table. A document with repeating items inside repeating orders does not, as described in the map article: only one level of repetition per table. Depending on the version and the mapping Excel creates, you may see one table of the innermost repeats with the outer fields repeated or missing, a table with some columns empty, or a refusal. Whichever you get, check the row count against the number of innermost elements in the source with xmllint --xpath 'count(//item)'; if they differ, Excel dropped or merged something. The reliable approach is to flatten before opening (flattening hierarchical XML for Excel users).

What disappears

A grid has no place for several things that XML carries: comments, processing instructions, the declaration and its encoding, mixed content (text with inline tags), namespace declarations and attribute-versus-element distinctions. Excel drops or flattens them. That is fine when you are looking at data and a problem when the XML is a document: a paragraph with a bold word shows as plain text, or as separate cells. If the file is a document, open it in an XML-aware viewer or a browser instead.

A safer routine

For a file you only need to look at: open it in a browser or run xmllint --format file.xml | less. Nothing is interpreted.

For a file you need as a table: convert it with a script that writes a workbook with the types you choose, then open the workbook (converting XML to Excel with Python). The script is the documentation of what you decided about each column.

For a file you will receive every week: build the map once with a schema that types identifiers as strings, or, better, do the conversion in a scheduled script, and give users the workbook.

Whichever you use, compare the row count and one total with the source. A silent loss on opening shows up there.

Security

An XML file from outside is untrusted input. Opening it in Excel runs the application's own XML parser, and a file can reference external resources. Current Office versions restrict this, and the safest habit is the same as everywhere: do not open XML files from unknown sources in an application that might fetch things, and prefer a viewer that does not (XML security problems to close before production). A workbook with macros is a different risk; an XML file does not carry macros, but a stylesheet reference can pull in transformation logic you did not write.

Related reading

For the cluster overview see moving XML into and out of spreadsheets. For why Excel sometimes says there is no schema, why Excel complains about an XML source with no schema. Test a path in the XPath tester.

When Excel is the wrong viewer for the file

The file is large. Excel has a row limit of 1,048,576 per sheet and slows long before it. Load it into a database or convert to CSV for analysis.

The file is a document. Paragraphs, footnotes and inline markup do not survive a grid.

Identifiers matter. If one wrong digit is costly, do not let a spreadsheet interpret the file.

You need to share the result. A converted workbook with typed cells is easier to send than an XML file and instructions.