How to Import Multiple XML Files Into Excel & Google Sheets

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

 

importxmlintro

 

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, click on the Ribbon and then select Data > Get and Transform Data > Get Data > From File > From XML.

 

importxml getdata

 

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

 

importxml selectfile

 

  1. Select the data you wish to import. This shows a preview on the right-hand side, and then in the Load drop down, select Load to.

 

importxml loadto

 

  1. Select Table in the select how you want to view this data in your workbook option.
    Then select Existing worksheet in the Where do you want to put your data option. You can also select New Worksheet if you want to create a new worksheet in your workbook and not put the data in the one you have selected.

 

importxml table

 

The data is imported into Excel as a linked table.

 

importxml linkedtable

 

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.

  1. In the Ribbon, select Query > Edit > Edit. This opens the Power Query Editor.

 

importxml powerquery

 

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

 

importxml powerquery importxml

 

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

 

importxml selectfile2

 

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

 

importxml secondfile

 

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

 

importxml record2

 

  1. In the Power Query Ribbon, select Home > Close & Load > Close & Load To…

 

importxml closeandload

 

  1. Select Table in the select how you want to view this data in your workbook option, and then New worksheet. Click OK.

 

importxml new worksheet

 

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

 

importxml imported data

 

Import Multiple XML Files Into Google Sheets

To import an xml file or files into Google Sheets, use a function called IMPORTXML.

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")
  1. Press ENTER to fetch the data.

 

importxml gs data

 

You can 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.