Now you can’t use the XML file directly, as it’s not meant to be read by humans (but machines). In such a case, it would help to know how to convert the XML file to Excel so you can easily work with the data and analyze it. In this tutorial, I will show you two really simple ways to import an XML file into Excel using Power Query.
What is an XML File?
XML stands for Extensible Markup Language. An XML file can hold data in a format that can easily be read by apps and systems. But it’s not as easy to read for humans, which is why we may have to transform it into a format that’s easier to use. If it contains a lot of text data, then you can use a text editor to read the XML file, and if it contains data, then you can import that XML file into Excel and then work with the data. XML is quite widely accepted as a file format to store and transmit data over the web. A lot of popular file formats, such as Microsoft Office Open XML, LibreOffice, OpenDocument, XHTML, and SVG, also use the XML file format. Most popular websites on the Internet have their sitemap in an XML format. This is a file that contains the details of all the important pages and categories on a website. Here is an example of the sitemap from Forbes. Now let’s see how to convert an XML file to Excel using Power Query.
Import XML File to Excel
If you already have an XML file (either downloaded on your system or a link to it on the web), you can easily convert it into data in an Excel file. Thanks to Power Query (now called ‘Get & Transform’)
Import XML File that is Saved On your System
For the purpose of this tutorial, I’ll use an XML file that contains the sitemap for Forbes. You can download the file by going to this link, then right-click and save the file. Once you have the XML file on your system, follow the below steps to get the XML file data into Excel: The above steps would insert a new worksheet in the Excel file, and load all the data from the XML file into the new worksheet.
The great thing about using Power Query to fetch the data from an XML file into Excel is that in case the XML file updates and there are new records in it, you don’t have to repeat the same process. You can simply right-click on any cell in the table and refresh the query.
Import XML File into Excel using the web URL
In the above example, we first downloaded the XML file on the system and then imported the data into Excel. In case you have a web URL that contains the XML file (such as this one – https://www.forbes.com/sitemap_index.xml), you don’t even need to download the file. you can connect Power Query to that URL and extract the XML data into Excel. Below are the steps to connect power query do a web URL that contains the XML data and import that data into Excel: The above steps would insert a new worksheet in the Excel file, and load all the data from the XML file into the new worksheet. And again, in case the data updates in this URL, simply refresh the query to get the new data in Excel. So these are two simple ways that you can use to convert an XML file into Excel. If you have the XML file on your system, you can import the data easily using Power Query. And if you have a web URL of the XML file, then you can also fetch that data into Excel. I hope you found this tutorial useful. Other Excel tutorials you may also like:
8 Ways to Reduce Excel File Size (that actually work) How to Automatically Open Specific Excel File on Startup How to Recover Unsaved Excel Files [All Options + Precautions] Microsoft Excel Won’t Open – How to Fix it! (6 Possible Solutions) How to Convert Excel to PDF Using VBA Split Each Excel Sheet Into Separate Files (Step-by-Step) Combine Data from Multiple Workbooks in Excel (using Power Query)
