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.
- In a new blank Excel file, click on the Ribbon and then select Data > Get and Transform Data > Get Data > From File > From XML.
- Select the file you wish to import and click Import.
- 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.
- 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.
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.
- In the Ribbon, select Query > Edit > Edit. This opens the Power Query Editor.
- 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.
- Select the file to import and then click Import.
- 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).
- In the Power Query Ribbon, select Home > Close & Load > Close & Load To…
- Select Table in the select how you want to view this data in your workbook option, and then New worksheet. 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 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>
- Create a new Google sheet and in cell A1, type in the formula:
- Press ENTER to fetch the 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.