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.
How to Convert Large XML to Excel: 2 Effective Ways
Depending on the source of the XML files, we can convert XML to Excel in 2 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 navigate to Data > Get Data > From File.
- Then, Select From XML.
- 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 headings, remove or add data, etc.
Read More: How to Convert XML to Columns In Excel
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
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.
Download Practice Workbook
You can download the practice workbook from here.
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.
