How to Import Multiple XML Files Into Excel & Google Sheets

This tutorial demonstrates how to import multiple XML files into Excel and Google Sheets.

Import XML Files With Power Query

If you have obtained data off the internet in the form of multiple XML files, you can import these files into Excel using Power Query.

1. In a new blank Excel file, in the Ribbon, go to Data > Get and Transform Data > Get Data > From File > From XML.

2. Select the file you wish to import and click Import.

3. Select the data to import. This shows a preview on the right, and then in the Load drop down, click Load To…

4. Choose Table under Select how you want to view this data in your workbook.
   Then choose Existing worksheet under Where do you want to put your data?
   Tip: Choose New worksheet instead if you want to avoid editing any existing sheets.

The data is imported into Excel as a linked table.
The Ribbon in Excel now displays two additional tabs: Table Design and Query. The Query tab enables you to manipulate the data in the table.

5. In the Ribbon, go to Query > Edit > Edit. This opens the Power Query Editor.

6. At this point, you can import an additional XML file into Excel using the Power Query Editor.
   In the Power Query Ribbon, go to Home > New Query > New Source > File > XML.

7. Select the file to import and then click Import.

8. Click on the record on the left to show a preview on the right, and then click OK.

Now in Power Query, you have two queries available: the original record, and now record(2).

9. In the Power Query Ribbon, go to Home > Close & Load > Close & Load To…

10. Choose Table under Select how you want to view this data in your workbook. Then choose New worksheet and click OK.

A new sheet appears in your workbook with a new Queries & Connection showing record(2).

Import Multiple XML Files Into Google Sheets

To import XML files into Google Sheets, use the IMPORTXML Function.

Consider the following XML file syntax:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customer-data>
<record>
<Title>Mr</Title>
<Surname>Bradford</Surname>
<Firstname>Nick</Firstname>
<Address1>11 Bridgewater Street</Address1>
<Address2>Paarden Eiland</Address2>
<Address3>New York</Address3>
<ZipCode>7420</ZipCode>
<Mobile/>
</record>
</customer-data>

1. Create a new Google sheet and in cell A1, type in the formula:

=IMPORTXML("https://f1solutions.co.za/Customers1.xml", "customer-data/record")

2. Press ENTER to fetch the data.

Repeat this formula for each of the XML files you wish to import into your Google sheet, creating a new sheet for each of them.

Other tutorials that use Power Query

• Set Up Automatic Currency Conversion in Excel and Google Sheets
• How to Export Folder and Subfolder Structure to Excel
• How to Import an HTML Table into Excel or Google Sheets