Excel won't export your XML ("list of lists"): why, and what to do

The error message

On the Developer tab, the Export command (or File › Save As › XML Data) stops on an error. The dialog is titled "Cannot save or export XML data" and says "The XML maps in this workbook are not exportable".

In the XML Source pane, verifying the map for export gives the reason. Most often: a "list of lists".

Why Excel refuses

Excel links XML to tables through a map. That map can only be exported if each element keeps an unambiguous place in a flat table. Microsoft lists three structures that prevent it:

• "List of lists": one list of items contains a second list of items. For instance, products that each have several prices, several images, or one description per language.
• "Denormalized data": an element meant to occur once is placed in a repeating table.
• "Choice": a mapped element is part of a choice construct in the schema.
• The file's comments aren't kept either.

"The XML map can be exported but some required elements aren't mapped"

With this second message, the export goes through, but the file may be rejected by the system that reads it next. The causes Microsoft gives:

• Required schema elements aren't linked to any column.
• A recursive structure (an element that contains itself, like a category tree): Excel doesn't support it beyond one level.
• Mixed content: an element holds both text and child elements ("Press <b>here</b>").

Beyond 65,536 rows, the export is cut

According to Microsoft's documentation, Excel's XML export saves at most 65,536 rows. Beyond that, it only exports the first rows, up to the remainder of the row count divided by 65,537: on a 70,000-row file, only 4,463 rows make it into the XML.

An example: most catalogs

Nearly every catalog export and product feed contains lists inside lists. A single product in two languages is enough:

<catalog>
  <product id="P001">
    <description xml:lang="fr">Veste coupe-vent</description>
    <description xml:lang="en">Windproof jacket</description>
  </product>
  <product id="P002">
    …
  </product>
</catalog>

Workarounds inside Excel, and their limits

• Simplify the schema so it has no nested lists: rarely possible, since the system that will import the file (Business Manager, Merchant Center, your PIM) imposes its structure.
• Write a macro or a script that rebuilds the XML: it works, but it has to be built, maintained, and redone for each format.
• Save as "XML Spreadsheet 2003": this format describes an Excel workbook (sheets, cells, styles), not your file's structure; the target system will reject it. And "XML Data", in Save As, goes through the same map as the export: it fails the same way.
• Power Query (Data › Get Data › From File › From XML) reads nested XML very well and flattens it into a table, but it can't write XML: you can read your file, not save it back.

The method without XML maps

ExcelifyXML doesn't go through Excel's maps. It flattens the XML into a CSV, keeping the file's structure aside, then rebuilds the XML from the edited CSV. Nested lists become columns (description[fr], description[en], image[0], image[1]), and reconstruction puts them back in place.

With one row per order, the inner lists become numbered columns: lines.line[0].@sku, lines.line[0].discounts.discount[1].@code, lines.line[0].discounts.discount[1]… Reconstruction puts them back at their level.

One limit: text mixed with tags ("Press <b>here</b>") doesn't fit in a cell. Only the tags' text is kept; the preview and the result screen point it out.

1. Drop your XML file on the Upload page: the free preview shows the detected row and the resulting columns.
2. Confirm (1 credit) and download the bundle: data.csv and skeleton.json.
3. Edit data.csv in Excel, like any table.
4. On the Rebuild page, drop the edited data.csv with its skeleton.json: you get the XML back, nested lists included. No 65,536-row limit on our side: reconstruction accepts up to 1,000,000 rows and 100,000 columns, for an XML of about 30 MB at most.

Variant: regenerate the XML from Excel

Want to keep an export button inside Excel? The standalone Excel workbook (2 credits) holds your data and a "Generate XML" button that can write nested lists. Generation happens in Excel, on your computer, offline. It needs desktop Excel, on Windows or Mac.

• Edit an XML file in Excel and regenerate it in one click

Going further

• Open and edit an XML file in Excel
• Edit an SFCC catalog XML in Excel