Moving XML Into and Out of Spreadsheets Without Losing Data

People who work with data live in spreadsheets, and a great deal of data arrives and leaves as XML. The two meet at awkward moments: a partner sends an XML export and asks you to check it in Excel, a regulator wants XML built from a workbook, an old system emits a "spreadsheet" that is really XML. The conversion looks like a menu command. It is a decision about structure and types, and the places it goes wrong are quiet.

This guide covers both directions and the formats in between. I could run the XML and Python sides here and not Excel itself, so the Excel behavior follows Microsoft's documentation and is marked as such; everything marked as run was run. The spokes of the cluster take each part further and are linked as they come up.

Why the two do not fit

A spreadsheet is a grid. Each cell has a position and a type: number, text, boolean, date. Rows are records and columns are fields. XML is a tree. Elements nest, repeat, carry attributes and have no types at all, since everything is text until a schema says otherwise.

Three mismatches cause most of the trouble.

Nesting. A grid has one level of repetition per table. XML that has orders containing items needs either two tables or one with the order fields repeated.

Types. XML says 02134 and the spreadsheet decides whether that is a number. XML says 2026-03-02 and the spreadsheet decides whether that is a date. Every decision is a chance to change the data.

Information with no cell. Comments, attributes versus elements, namespaces, mixed content and the order of children have no place in a grid.

All the rest of this guide is ways of dealing with these three.

Direction one: XML into the spreadsheet

Excel's own routes

Excel's XML support is built around the XML map, a schema linked to cells. Per Microsoft's documentation, you import through the Developer tab: into a mapped range if you built a map, or by opening or importing a file with no map, in which case Excel looks for a schema reference in the file and, if it finds none, infers a schema from the data and builds a table. Several files can be imported into one mapped range, and a properties dialog decides whether refresh overwrites or appends. The mechanics and the traps (appending the same data twice, files with a shared namespace and different schemas) are in importing XML into Excel with an XML map.

Opening a file directly offers an XML table, a read-only workbook or a stylesheet view. Whichever you pick, the values are typed by the schema, and an inferred schema is a guess (opening XML files in Excel without mangled data). The guess reflects the sample: an element absent from it does not exist in the map, a field that never repeated is single-valued, and a small number becomes a small numeric type. I saw the same behavior from a different inference tool: .NET's XmlSchemaInference typed ids as unsignedByte and left an enumeration as a string. The warning that the XML source has no schema is Excel telling you it is guessing (why Excel complains about an XML source with no schema).

Microsoft also describes XML maps as a legacy feature. For nested or complex XML it recommends Power Query, which can expand nested records and lists as repeatable steps. The XML tools are not available in Excel for Mac or the web.

The route I trust: flatten first

Whatever Excel offers, the most reliable import is one where you decided the layout. Flatten the XML into the grid you want, write a workbook with the types you chose and let people open that. Two layouts cover most needs. Normalized: one sheet per level, linked by a key (orders and items, joined on order_id). Wide: one row per innermost element with the parent fields repeated, easier to filter and wrong to sum at the parent level.

for od in root.iterfind("order"):
    its = od.findall("items/item")
    o.append([od.get("id"), od.findtext("customer"), od.get("placed"), len(its)])
    for it in its:
        i.append([od.get("id"), it.get("sku"), int(it.get("qty")), float(it.findtext("price"))])

orders 3 rows
items 3 rows
check: items per order sum = 3 = 3

Three orders and three item rows from a file with three orders and three item elements, and the item_count column adds up to the rows on the second sheet. The order with an empty items element still has a row on the first sheet, which is the honest representation. The details, including the wide variant and the notes sheet that tells the reader what a blank means, are in flattening hierarchical XML for Excel users, built on the row logic of flattening repeating XML elements into rows.

Types in the workbook

A script writes the type you choose into each cell. I wrote "02134" and 44 into a workbook and read it back: the first came back as a string with data type s, the second as an integer with type n. That is the control a double-click does not give you. The conversion rules from XML text to cell type should be written down: identifiers are strings, amounts are numbers with a currency column beside them, dates are real dates converted from ISO text, booleans are booleans after a mapping that rejects unknown spellings. The same rules appear in converting XML to Excel with Python and the larger conversion guide, converting XML to JSON, CSV, YAML and Excel.

The old XML spreadsheet

Some systems still export "XML Spreadsheet 2003": a single XML document in the namespace urn:schemas-microsoft-com:office:spreadsheet, with Worksheet, Table, Row, Cell and Data elements and an mso-application instruction that makes Windows open it in Excel. It is easy to read and has two gotchas. A Cell with ss:Index jumps to that column, so skipping it shifts every later cell left. In my sample, a row with a missing customer and ss:Index="3" read as [1002, None, 44, 1] once the gap was filled and as the wrong columns otherwise. And pandas.read_excel refused the file with Excel file format cannot be determined, because it is neither xlsx nor binary xls. The Data type attribute is explicit (String, Number, DateTime, Boolean), so the ZIP code stayed a string in the XML; the loss happens when something else interprets it. See reading Excel XML Spreadsheet 2003 files.

Direction two: the spreadsheet into XML

Excel's export

Per the documentation, Excel exports XML from an XML map: add a schema, map its elements to columns, fill the cells and use Export. Limits that decide whether your job is possible: at most 65,536 rows; maps with a choice construct cannot be exported; mixed content and recursion deeper than one level are also out; export validates against the schema by default and writes UTF-8. The article on the row workaround gives two different numbers (65,536 and 65,537), so check any workaround before you trust it. Cells that are not mapped do not appear. See exporting Excel data as XML with a schema.

The script alternative

A script has no row limit and no restriction on the schema. It reads the sheet and writes elements with a library:

for id_, customer, total in ws.iter_rows(min_row=2, values_only=True):
    o = etree.SubElement(root, "order")
    etree.SubElement(o, "id").text = str(id_)
    etree.SubElement(o, "customer").text = customer
    etree.SubElement(o, "total").text = f"{total:.2f}"

On a three-row sheet with customers named Ana & Co and Cy <Ortiz>, the output escaped them as Ana & Co and Cy <Ortiz>, wrote totals with two decimals and passed xmllint --noout --schema orders.xsd, which printed orders_out.xml validates. The library does the escaping and the schema checks the structure. The remaining risks are in the sheet: a ZIP code stored as a number is already 2134 before any XML exists, and a blank cell has to become an element, an absent element or a failure according to the schema's minOccurs. Decide column by column, and test a row with every optional column blank.

The checks that catch silent loss

Spreadsheet conversions fail quietly. These checks catch most of it, and each is cheap.

1. Count the records in the source (xmllint --xpath 'count(//item)') and in the target. Excel's 65,536-row cap, a nested file flattened to the wrong level and a dropped optional element all show up here.

2. Sum one number on both sides, per currency if there is one. It finds converted decimals and dropped rows.

3. Look at three odd records: the one with the most children, the one with the most blanks, the one with the strangest characters.

4. Check the identifiers as text: open the target and confirm that a ZIP code with a leading zero and a long id are intact, as strings.

5. Validate outgoing XML against the receiver's schema before sending (validating XML against an XSD schema).

6. Keep the original file and the script, with the date. A spreadsheet that someone edits by hand has no history; the XML and the script do.

A round trip loses what the grid cannot hold

The best test of any conversion pair is to go there and back. I flattened the three-order file into one wide sheet (a row per item, with the order id and customer on each row), saved it, read it back and rebuilt the XML:

orders in/out: 3 2 | items in/out: 3 3
order 1003 (no items) survives round trip: False

All three items came back. One of three orders did not: order 1003 had an empty items element, so it contributed no rows to the wide sheet, and the rebuilt document had no way to know it existed. The counts of items match, which is exactly why an item count alone would have passed this conversion. The orders count caught it. This is the main structural hazard of the wide layout, and the cure is the one from the flattening article: write a row with blank item columns for an order with no items, or keep the normalized layout with a separate orders sheet. The test to keep is the round trip with a record that has nothing in its repeating part, an element that is empty and an element that is absent.

Other things the grid cannot hold, and so lose in a round trip: the order of siblings of different names, comments, attributes versus elements (a field that was an attribute comes back as an element unless the rebuild code knows), namespaces and the declaration. If the receiver cares about any of them, the XML you send is rebuilt from a schema, not recovered from the sheet.

How big a sheet can be

Two limits bound the grid. Excel's sheets hold 1,048,576 rows, and the XML export through a map is documented as 65,536. Beyond those, you are in database territory. How far a script goes is measurable: I wrote every item of the 200,000-order file as a row, with openpyxl in write-only mode, streaming the XML with iterparse:

400001 rows written in 10.6s, file 8.0 MB, peak 44 MB

400,001 rows in 10.6 seconds, an 8.0 MB workbook, and a peak of 44 MB of memory, because write-only mode streams rows to disk and iterparse streams the XML. The normal workbook mode keeps every cell in memory and uses many times that. The row count is well under the sheet limit, and a file that size opens slowly in a desktop application, which is a usability limit before it is a technical one. For analysis at this size, CSV into a database or a tool like DuckDB is faster for everyone. I did not measure the opening time in Excel.

If a sheet would exceed the row limit, split by a natural key (month, region) into several sheets or files, and record the split in the notes sheet. Do not let the application truncate silently.

A column contract

The most useful artifact in a conversion project is a small table that says, for each column: its name, where it comes from in the XML (an XPath), its target type, what a blank means and what happens on a bad value. Written once, it drives both directions: the flatten script reads it to produce the sheet, the export script reads it to rebuild the XML, and a person reads it to understand the file.

The table is short and answers every question that comes up later: why is the ZIP a text column, why is there a row with no SKU, what happened to the row with a price of 9,50. Keep it with the script, and put it on the notes sheet in the workbook.

Power Query, briefly

Microsoft's own advice for nested XML is Power Query, and it deserves a fair mention. Power Query reads an XML file, shows its structure as nested tables and lets you expand the levels you want into columns, step by step, recording each step so a refresh repeats it. For a recurring import into Excel with no code, it is the best of the built-in routes, and it handles nesting that an XML map cannot. Its weak points are the ones of any recorded-steps tool: the steps are hard to review in version control, type detection can be applied by default and change a column you wanted as text, and behavior varies with the Excel version. I did not run it here. If you use it, set the types of identifier columns to text explicitly, as the first step after the load, and check counts after a refresh.

Governance for shared workbooks

A conversion pipeline often ends with a workbook that many people use, and a few rules keep it healthy. Name one owner. Regenerate on a schedule and say when. Keep the source XML and the script for as long as you keep the workbook. Put the checks (counts, totals) on the notes sheet with their values, so a user can see that the file reconciled. Lock or protect the generated sheets and invite people to copy data into their own workbook, because a user's hand edit in a generated sheet is lost at the next run, and worse, may be mistaken for data. If people must write back, give them a separate input sheet with validation, and treat it as its own source.

A test plan you can reuse

Every XML and spreadsheet pipeline deserves the same small set of fixtures. Build one XML file that contains: a record with several children, a record with one child, a record with none (empty and absent are both worth a file), a value with an ampersand and an angle bracket, an identifier with a leading zero, a number with a trailing zero, a date, a boolean, a very long digit string, a field with an accent and one with an emoji. Run it through the flatten step and the export step and check, in order:

1. The record counts, at every level, before and after.

2. One sum per numeric column, before and after.

3. The identifiers as text, character for character.

4. The escaped characters in the rebuilt XML.

5. The rebuilt XML against the schema.

6. The round trip: source, workbook, rebuilt XML, then compare canonical forms of source and rebuilt where they should be equal (comparing two XML documents for differences) and list the differences where they should not.

The sixth check is the one that found the lost order. It has a useful by-product: the list of differences is exactly the list of things the grid cannot hold, and it belongs in the documentation of the pipeline.

Mistakes I would check for first

A short list of the faults seen in this series, in the order they cost the most.

Trusting the inferred schema. The map Excel builds from a sample is a guess. It fits the file you had and fails on the one you get next.

Counting only the leaf level. The item count matched while an order vanished. Count at every level.

Letting a grid decide types. A ZIP code or a 16-digit id as a number loses data before anything else happens. Set the column to text, or write string cells from a script.

Wide layouts with order-level sums. A total repeated on every item row is summed once per item.

Ignoring a skipped cell. In the 2003 XML format a cell with ss:Index moves the column; reading cells in order shifts the rest of the row.

Assuming the export has no limit. Sixty-five thousand rows is a quiet cap, and a file under it looks no different from a file that was cut.

Editing a generated sheet. The next regeneration removes the edit, or the edit is mistaken for source data.

Skipping the schema check on the way out. The receiver rejects the file and the error message is theirs, a day later.

Most of these are not Excel's faults. They are the consequences of putting a tree into a grid and back, and the habits that avoid them are the same in every tool.

Matching the format to the audience

Who receives the result decides the format. An analyst who will pivot and filter wants a workbook with typed cells, a wide sheet and a notes sheet. A developer who will load it into a database wants CSV per level with a key, and the XML next to it for reference. A partner system wants the XML, validated against its schema, with a manifest of counts. A manager who wants to see the shape wants a rendered report or a screenshot of the first rows. Producing several of these from the same parsed records is cheap, and it is the reason to parse once and keep the writers separate, as in the conversion guide. Picking a single format and asking everyone to cope is how a ZIP code ends up with its zero removed in somebody's report.

Reading the documentation for your version

Excel's XML features have shifted over the years, and the documentation I used marks the XML map feature as legacy, steers toward Power Query for import and notes that the XML tools are absent from Excel for Mac and for the web. Before you design a process around any of it, check three things on the machines where it will run: that the Developer tab and the XML Source pane exist, which version of Excel is installed, and whether the people who will use the workbook are on a platform without the tools. A process that depends on an XML map and is handed to a Mac user has a hidden requirement. A script that produces the workbook has none, which is another point in its favor.

If you do rely on the built-in features, keep a screenshot or a short written procedure of the steps you took, with the file names and the options chosen in the map properties. These dialogs are where the append-or-overwrite decision and the validation switches live, and they are not visible when someone opens the workbook later. The map itself is stored inside the workbook, and Microsoft documents a way to delete the data source information from the map if you do not want to share where the schema came from, which does not prevent exporting the data.

What this costs, and what it buys

The script route takes a day to write and test for the first format, and an hour for the next. It buys repeatability, a column contract, typed cells, checks that run every time and no dependency on a particular Excel version. The built-in route takes minutes for a flat file and breaks on the first nested one. For a one-off look, use the built-in route or a browser. For anything you will do twice, write the script, and keep the fixtures.

Choosing a route

Where to read next

The spokes: importing XML into Excel with an XML map, exporting Excel data as XML with a schema, opening XML files in Excel without mangled data, flattening hierarchical XML for Excel users, reading Excel XML Spreadsheet 2003 files and why Excel complains about an XML source with no schema. For the modern file format see reading Office Open XML from docx and xlsx files.

The sources: Microsoft's pages linked above, the Office Open XML standard (ECMA-376), the openpyxl documentation and the XML Schema primer. Test a path in the XPath tester.

Habits for people who share workbooks

A workbook made from XML is a derived file. Say so on a notes sheet, with the source file, the date and the script version. Do not edit generated sheets in place; put calculations on separate sheets so a regeneration does not destroy them. Do not use merged cells in data sheets. Freeze the header row, turn on filters and set the column widths, which cost nothing and make the sheet usable. And if the recipients are going to send the data back as XML, give them the schema and the rules for each column, because the return trip is where the types and the blanks matter.

When a spreadsheet is the wrong place for the data

The data is deeply nested or document-like. A grid flattens it into something that is neither the source nor useful. Keep the XML and give people a rendered view (transforming XML to HTML with XSLT).

The volume is large. A sheet holds 1,048,576 rows and becomes slow long before. Use a database and give people a query or an extract.

The workflow is a round trip. Edit in Excel, export to XML, load into a system, repeat: every pass is a chance to change a type. A web form or a database front end with validation is safer.

Precision matters. Spreadsheet numbers are binary doubles with about 15 significant digits. Long identifiers and exact decimals belong in text columns, or in a system built for them.