Excel structured-data mapping

Convert XML to CSV in Excel by Selecting Repeated Records

Import XML with Power Query, flatten the intended repeated elements and attributes, preserve textual identifiers and verify the table before exporting CSV.

By Uddhav Gopani, FounderPublished 27 September 2026Updated 29 September 2026

Convert XML to CSV in Excel by Selecting Repeated Records

To convert XML to CSV in Excel, import the XML with Power Query, select the repeated element that represents a record and expand its required fields into a flat table. Load the table into a worksheet and export that sheet as CSV. XML can express a hierarchy, so the conversion needs an explicit decision about which relationships the table will retain.

Use the XML connector for table extraction

In a supported Excel for Windows installation, choose Data, Get Data, From File, From XML. Select the source, inspect its collections in the Navigator and choose the data to transform. Microsoft’s import instructions describe this connector path.

This workflow extracts a table for CSV. It does not require a legacy XML Map merely to read the XML through Power Query. If the command is unavailable, check the installed platform and connector support rather than substituting an unrelated Save As option.

Identify elements, attributes and repetition

<catalog>
  <item code="0012"><name>North &amp; East folder</name><price>12.50</price></item>
  <item code="0047"><name>René notebook</name><price>0.00</price></item>
</catalog>

The root catalog contains two item elements. Each item has a code attribute and name and price child elements. For this export, one row represents one item, and the code, name and price text become columns.

The ampersand in North & East folder is escaped in XML syntax as &. After parsing, the actual name contains an ordinary ampersand. Do not carry the entity spelling into the CSV as if it were part of the name, or manually replace every ampersand in the source before parsing.

Expand the chosen collection

In the query preview, navigate to the repeating item collection and expand the fields needed for each item. Attribute names and nested-table displays may have connector-specific labels, so inspect the values to confirm which column contains the code. Rename the final columns to Code, Name and PriceText.

A root-level summary is not a substitute for the repeated item rows. Check the row count after each expansion. If a child itself repeats, decide whether that should create more rows, a separate table or an explicitly combined field.

Compare the flattened values

Code,Name,PriceText
0012,North & East folder,12.50
0047,René notebook,0.00

The intended CSV contains two records. The identifiers keep four characters, the decoded ampersand remains in the first name and the accented letter remains in the second. PriceText intentionally preserves the original textual forms 12.50 and 0.00 instead of converting them into a numeric display.

If the recipient expects numeric prices rather than the original text, choose that contract explicitly and verify the exported representation. A decimal value of 12.5 and the text 12.50 can represent the same amount while differing as character strings. The correct comparison depends on the destination requirement.

Review automatic type conversions

Set Code and PriceText to text before a conversion step discards zeros. Inspect the query’s Applied Steps for automatic type detection. Formatting a worksheet cell to display extra zeros after import does not necessarily restore the original source text.

For date or time elements, retain the source representation until the receiving requirement is clear. Converting a timestamp into a local display and exporting that display can lose an offset or precision. That is a data-model decision, not merely a cosmetic spreadsheet format.

Recognize XML that is not a simple record table

A document containing prose mixed with child elements needs special care. For example, a description with a bold element embedded between words is not equivalent to a set of independent scalar columns. Extracting only the child elements can omit meaningful text around them.

Namespaces also identify element names; similar visible local names can belong to different vocabularies. Preserve the intended namespace distinctions in the extraction logic. Do not strip prefixes or flatten unrelated fields solely because their displayed labels look alike.

Export only the reviewed worksheet

Load the final table into a worksheet and save a workbook version that retains the query. Export the intended sheet as CSV using the recipient’s text encoding and delimiter. The CSV holds the flattened field values; it does not preserve XML comments, element nesting, namespace declarations or the transformation steps.

Inspect the actual CSV text after saving. A comma inside a future product name will require correct CSV quoting even though commas have no special meaning in ordinary XML element text. Let the CSV writer handle that serialization.

Reconcile before using the result

Compare source item count with output record count and inspect both fixture records field by field. Include an optional missing element and a repeated child in a deliberate test set if those structures occur in the real XML. Do not infer their behavior from a simple two-record example.

Finally, import a small sample into the destination with the agreed column types. Local XML parsing and a valid CSV establish structure, while the destination check establishes interpretation. Retain the original XML so that any information intentionally omitted during flattening remains available.