How to Convert XML to Excel | Import XML File

You might occasionally need to interact with the data in an XML file because it is a widely used file format on the internet.

Now since the XML file isn’t intended for human reading, you can’t utilize it directly (but machines).

Knowing how to convert the XML file to Excel in such a situation would be beneficial so you can quickly deal with the data and perform analyses on it.

In this lesson, I’ll demonstrate two really easy ways to use Power Query to import an XML file into Excel.

This instruction explains: 1. What does XML stand for? 2. Excel XML File Import 2.1 Import an XML file stored on your computer 2.2 Using the site URL, import the XML file into Excel.

1. What does XML stand for?

Extensible Markup Language is known as XML. Data may be stored in an XML file in a manner that is simple for apps and systems to access.

However, it’s not as simple for people to read, which is why we might need to change it to a more user-friendly style.

If the XML file is mostly text-based, you can view it using a text editor. If the XML file is data-based, you can import it into Excel and deal with the data there.

The use of XML as a file format for storing and sending data via the internet is fairly common.

The XML file format is also used by several widely used file formats, including Microsoft Office Open XML, LibreOffice, OpenDocument, XHTML, and SVG.

The majority of well-known websites on the Internet have XML sitemaps.

This is a file that lists all of a website’s significant pages and categories. Here is a Forbes sitemap illustration.

Let’s now examine how to use Power Query to convert an XML file to Excel.

2. Excel XML File Import

You can easily convert data from an XML file that you already have (either downloaded to your computer or accessed via a web link) into data in an Excel file.

Power Query, now known as “Get & Transform,” is appreciated.

2.1 Import an XML file stored on your computer

I’ll utilize an XML file that contains Forbes’ sitemap for the duration of this course. By clicking this link, you may download the file, then save it with a right-click.
When you have the XML file on your computer, take the following actions to import the data into Excel:

To obtain data from the XML file, launch the Excel file that you wish to do so in.
Go to the Data tab.
Select the “Get Data” option under the “Get & Transform” group.
Select “From file” from the menu.
Then select “From XML.”
Find the XML file you wish to import and choose it in the input data dialogue box that appears.
To import, click. This will launch the Navigator dialogue box and import the XML file into the power query.
Choose the information you wish to import from the XML file. I would in this instance click on “sitemap” on the left pane.
[Optional] If you want to transform the data before loading it into Excel, click the Transform Data button.
Select “Load”

The aforementioned procedures would add a new worksheet to the Excel document and fill it with all of the information from the XML file.

The benefit of using Power Query to import data from an XML file into Excel is that you won’t need to repeat the procedure if the XML file is updated and contains new information.

Simply perform right-click on any table cell to update the query.

2.2 Using the site URL, import the XML file into Excel.

In the aforementioned illustration, we first downloaded the system’s XML file before importing the data into Excel.

You don’t even need to download the file if you already have access to it via a web URL, like https://www.forbes.com/sitemap index.xml. You may use Power Query to connect to that URL and download the XML information into Excel.

The procedures to link Power Query to an XML data URL and import that data into Excel are listed below:

The Excel file you wish to import the data into should be opened.
Go to the Data tab.
Select the “Get Data” option under the Get & Transform group.
Access the “From Other Sources” menu.
Select “From Web”
Copy and paste the URL for the page containing the XML data into the ‘From Web’ dialogue box.
Select OK. By doing so, the Navigator dialogue box will open, allowing you to select which XML data to import.
To get the XML data that I require in Excel, click on “sitemap.”
[Optional] If you wish to convert the data before putting it into Excel, click the Transform Data button (such as change the column names or remove some columns)
Select “Load”

The aforementioned procedures would add a new worksheet to the Excel document and fill it with all of the information from the XML file.

Again, to receive the most recent data in Excel if the data at this URL changes, just reload the query.

There you have it, two straightforward methods for Excel conversion from XML files.

If you have the XML file on your computer, Power Query makes it simple to import the data. Additionally, you can download that information into Excel if you know the XML file’s web URL.