Nasir Muhammad Munim
We often need to convert large XML files to Excel for better understanding or readability of data. In this tutorial, we will learn how to convert large XML to Excel. Since we can get XML files from both the local storage of our computer and the web link, we will show both of these cases here.
Download Practice Workbook
You can download the practice workbook from here.
2 Effective Ways to Convert Large XML to Excel
Depending on the source of the XML files, we can convert XML to Excel in two ways. One is converting from a .xml file and another from a web link. Both scenarios are described below with suitable steps.
1. Using File Stored in System Memory
For this method, we will use a file stored in our local system or on pc to convert from XML to Excel. To do so, we will follow these steps.
Steps:
- First, we need to go to the Data tab in Ribbon.
- Second, we need to select Get Data in the Get and Transform Data section.
- Third, we need to select From Files and From XML options under the Get Data option.
- Next, a selection box will appear where we will need to locate and select the XML file to import.
- After that, the XML file will be shown in the Navigation box.
- Then we will select the worksheet we want to import. In our case, it’s the sitemap.
- Finally, we will click on Load to show XML data on our sheet.
- Now it will create a new worksheet called sitemap and will have a table with the data shown in the Navigator box.
Optional:
- In case we need to modify data such as deselecting a column, renaming it, or changing values, we will simply select Transform before choosing Load. Selecting Transform will take the table to the Power Query Editor Here we can edit table data, and heading, remove or add data, etc.
Read More: How to Convert XML to Columns In Excel (4 Suitable Ways)
2. Utilizing Web-Link to Convert Large XML to Excel
In this method, we will use a web link instead of a stored file on our computer system to convert XML to Excel. We will follow the steps below.
Steps:
- At first, we will go to the Data tab in the Ribbon and select Get Data as before.
- Now the change here in this method is instead of choosing From Files, we will click on From Other Sources and then select From Web.
- Next, a dialog box will pop up asking for the site link or the XML file’s link. We will copy and paste our XML file’s link there and press OK.
- Again the file will appear in the Navigator box like the previous method. But this time, the source file location is the weblink we input earlier.
- As a result, the rest of the procedures are the same for this method as the previous one. We will select the option sitemap and click on Load.
- Finally, after loading the file into Excel, we will get a separate worksheet named sitemap and we see a table containing all the XML data like the image below.
Read More: How to Extract Data from XML File to Excel (2 Easy Ways)
Things to Remember
- Importing an XLSX file will always create a new worksheet.
- While using Web-link, the internet connection must be on.
- As both of our methods contain the same XML table name, we have renamed the worksheets according to our methods.
- To update the Excel file for any changes in the sources, we will just press on any random cells in the worksheet and select Refresh.
Conclusion
These are the two ways to convert XML to Excel. Hope this article will help you to do so. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel related problems, you can visit our website ExcelDemy for all types of excel related problem solutions.
