How to Import XML Data from Web to Excel

You can have your XML file either on your local disk or on the web. However, as long as you are using Microsoft Excel to retrieve information from your Excel file, you are in no trouble. Excel will offer you to import XML data from any potential source. Anyways, if you are looking for ways to import an XML file from websites, this blog will help you all the way. Here, I will discuss the steps to import XML data from the web to Excel. So, without any delay, let’s begin.

Table of Contents

About the Setup

To show you the steps to import an XML file from the web, I’m going to use the sitemap link of Microsoft’s official website. If you follow the link below, you will end up getting an XML file online. Now let me show you import this XML file to Microsoft Excel.

https://www.microsoft.com/en-us/learning/sitemap.xml

Guide to Import an XML File from Web to Excel

Microsoft Excel has a powerful feature called Power Query which allows you to import data from multiple sources. Here I’m going to use Power Query to import an XML file from a website link I mentioned above.

To begin with,

Step_1: Go to the Data tab in the ribbon.

Step_2: Then click on the From Web command in the Get & Transform Data group.

This will allow you to insert a website link to retrieve information from an online XML file.

Alternatively, you can get the From Web option by following Data ⋙ Get Data ⋙ From Other Sources ⋙ From Web.

Anyways as you select the From Web command, the From Web dialog box appears. Which allows you to insert a weblink.

Step_3: Now insert a weblink that refers to an XML file just below the URL section.

Step_4: After that, hit OK.

2 VBA Codes to Open an XML File in Excel

Now the Navigator window appears. Here, in the left column of the Navigator window, you will see your weblink with a folder sign. If you expand the folder, you will see “url” just beside a tabular icon.

Step_5: Select ‘url’ to expand its content on the right side of the Navigator window.

Step_6: Finally, click on the Transform Data command to proceed.

Now Excel will take you to the Power Query Editor window. Here, you will see the XML file has been extracted in columns and rows. You can edit the XML data here if you like.

Step_7: Then hit the Close & Load button to load the XML data in an Excel worksheet.

Now just look. You have successfully imported XML data from the web to an Excel worksheet. However, the connection between the XML file and the Excel worksheet is semi-dynamic. This means if there any changes occur in the XML data online, the Excel worksheet will be updated automatically if you refresh your worksheet.

On the right side of the Excel worksheet, you will see the Queries & Connections task pane. Under the Queries tab, you will see how many rows have been loaded from the XML file to Excel. In this particular case, it shows “277 rows loaded”.

Conclusion

So, I’ve demonstrated the steps to import an XML file from the web to an Excel worksheet. I hope you will find this article helpful. If you have any further queries regarding XML file importing and exporting, feel free to drop them in the comment section below. I will try to respond as soon as possible. Thanks for the read.

Related Articles