Flattening Hierarchical XML for Excel Users With Python

A spreadsheet user wants rows and columns. XML offers orders that contain items that contain prices. The conversion between them is flattening, and it is the single most useful thing you can do for someone who has to work with an XML export in Excel. This article shows two layouts, what each is good for, and the checks that prove nothing was lost.

Two layouts

Normalized: one sheet per level. An orders sheet with one row per order, and an items sheet with one row per item and the order id repeated on each. The two sheets are related by order_id. Each fact appears once: the customer's name is on the order row, the SKU on the item row.

Wide: one sheet. One row per item, with the order fields (id, customer, date) repeated on every row. Easy to filter and pivot in a single place, and every order-level number is repeated, so summing an order-level column counts it once per item.

Neither is better in general. The normalized form is how a database holds it, it survives a count of orders, and it takes a lookup to bring the two together. The wide form is what most spreadsheet users expect, and it makes order-level aggregates wrong unless they know. I usually produce both in one workbook: the normalized sheets as the source of truth, and a wide sheet for browsing, labeled as such.

The script

root = etree.parse("orders.xml").getroot()
wb = openpyxl.Workbook(); o = wb.active; o.title = "orders"; i = wb.create_sheet("items")
o.append(["order_id", "customer", "placed", "item_count"]); i.append(["order_id", "sku", "qty", "price"])
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"))])
wb.save("flat.xlsx")

The loop visits each order once. It writes the order row, including an item_count that is computed in the script, and then one row per item with the order id. The order id is the key between the sheets, and it is written as a string so that an id like 001001 would survive. qty is an integer and price a float so that Excel can sum and sort them; for money you may prefer to write the amount with a currency column and a number format, as in converting XML to Excel with Python.

Reading the workbook back to check it:

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

Three order rows, three item rows, and the item_count column sums to 3, the number of rows on the items sheet. That sum is the reconciliation: if the column and the sheet disagree, a row was dropped or duplicated. Also compare with the source: xmllint --xpath 'count(//item)' orders.xml gives 3.

The order with nothing in it

Order 1003 has an empty <items/>. In the normalized layout it is a row on the orders sheet with item_count 0 and no rows on items, which is the honest representation. In the wide layout, one row per item would drop it entirely, so a spreadsheet user would believe there are two orders. The fix in the script: if an order has no items, write one row with the order fields and blank item columns. This is the same left-join decision as in the earlier flattening article, here made for a person who will not see the code. Say in a note on the sheet what a blank item column means.

Making it usable in Excel

A few small things turn the output from a dump into something people use:

• Header row frozen, filters on, widths set. Three lines of openpyxl, no effect on the data.

• A key people can search. Keep order_id as the first column on both sheets.

• Types. Numbers as numbers. Dates as real dates with a format, converted from the XML text at write time. Identifiers as text.

• A notes sheet. One sheet with the source file name, its date, the row counts and what a blank means. When a number is questioned, that sheet answers it.

• No merged cells. They break sorting, filtering and pivot tables, which are the reasons for using a spreadsheet.

To combine the sheets inside Excel, a lookup column on items (XLOOKUP on order_id, or VLOOKUP in older versions) brings the customer onto each item row. A pivot table can take both through the data model. Power Query can merge them with a join. All three need only the key.

A third option: JSON or CSV per level

If the next step is not Excel but a database, write one CSV per level with the same key instead of a workbook. The structure is identical, and it loads with COPY. The workbook is for people.

Documenting the layout for the reader

The person who opens the workbook was not in the conversation. Put three lines on the notes sheet: what a row is on each sheet (one order, one item), how the sheets relate (order_id), and what a blank means (an order with no items). Add the generation date and the name of the source file. A reader who knows that a row on items is one line of one order will not sum the order total column of a wide sheet, and the one who finds an order missing from items will know it had none.

If the workbook is regenerated, write it to a new file name with the date and never overwrite a file somebody may have edited. Edits to a generated workbook are lost at the next run, so say so on the notes sheet.

Related reading

For the cluster overview see moving XML into and out of spreadsheets, and for the XML-side view of repeating elements mapping XML attributes to columns without mistakes. The library is documented in the openpyxl tutorial, and the join functions in Microsoft's XLOOKUP documentation. Test a path in the XPath tester.

When the person needs the hierarchy, not a grid

The reader works with the tree. Someone checking the structure of an export wants the XML, formatted, not a table.

There are three or more levels. Flattening to a sheet per level still works and the workbook gets crowded. Consider a database.

Order matters inside the parent. Sheets do not preserve the sequence of mixed children unless you add a position column.

The data will go back as XML. Flattening loses the structure the receiver needs. Keep the keys, and rebuild the tree in code (exporting Excel data as XML with a schema).