How to Import Multiple XML Files Into Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 5, 2023

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.

importxml getdata

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

importxml selectfile

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

importxml loadto

  1. 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.

importxml table

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.

importxml linkedtable

  1. In the Ribbon, go to 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, go to 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, go to Home > Close & Load > Close & Load To…

importxml closeandload

  1. Choose Table under Select how you want to view this data in your workbook. Then choose New worksheet and 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 XML files into Google Sheets, use the IMPORTXML Function.

Consider the following XML file syntax:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Address1>11 Bridgewater Street</Address1>
<Address2>Paarden Eiland</Address2>
<Address3>New York</Address3>
  1. Create a new Google sheet and in cell A1, type in the formula:
=IMPORTXML("", "customer-data/record")
  1. Press ENTER to fetch the data.

importxml gs 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

AI Formula Generator

Try for Free

See all How-To Articles