How to Convert Large XML to Excel (2 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Effective Ways to Convert Large XML to Excel

  • 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.

Effective Ways to Convert Large XML to Excel

  • 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.

Effective Ways to Convert Large XML to Excel

  • 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.

Effective Ways to Convert Large XML to Excel

  • 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.

Effective Ways to Convert Large XML to Excel

  • 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nasir Muhammad Munim
Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo