In this article, we will learn to extract data from an XML file to Excel. The XML format is mainly used on the web to store data. Also, we can save it on our system. Sometimes, users need to extract data from an XML file in Excel. Today, we will show 2 different methods. Using these methods, you can easily extract data from an XML file to Excel.
What Is XML File?
An XML file can store data in a readable format for different applications and systems. XML stands for Extensible Markup Language. Users can’t read the XML file easily. That is why it needs to be extracted into another format. XML file is mainly used for storing and transporting data. It is similar to the HTML format but the tags are not predefined.
You can easily open the XML file with a text editor if it contains text data and make the necessary changes. On the other hand, if it contains both text and numerical data, then you can extract the XML file to Excel and read it easily.
How to Extract Data from XML to Excel: 2 Easy Ways
To explain the methods, we will use two different datasets. In the first method, we will use an XML file that contains the firstnames and lastnames of some students along with their Street, City, and Mail addresses. We will extract data from the XML file to Excel using both the Data and Developer tabs.
In the second method, we will use an XML link that contains the sitemap of a website. A sitemap is a file that contains the important pages of a website. Let’s go through the following sections for learning the methods.
1. Extract XML File from Your System to Excel
In the first method, we will extract an XML file from the system to Excel. We can use two different tabs for that purpose. In the first section, we will use the Data tab and in the second section, we will use the Developer tab.
1.1 Using Data Tab
In the case of extracting an XML file, we can use the Data tab in Excel. Here, we will also use the Power Query Editor. But the technique is simple. So, let’s follow the steps below to learn the method using the Data tab.
STEPS:
- Firstly, go to the Data tab and select Get Data. A drop-down menu will appear.
- Select From File there to open another menu.
- Then, select From XML from there. It will open the Import Data box.
- In the Import Data box, select the XML file and click on the Import option.
- Here, we have selected the “File to Import.xml” file and imported it.
- As a result, the Navigator box will appear.
- In the Navigator box, select the data from the XML file that you need to extract.
- In our case, we clicked on “student” in the left pane.
- After that, click on the Transform Data option.
- In the following step, the Power Query Editor will appear.
- In the Power Query Editor, click on the expand icon of the first column which is the name.
- After that, select OK.
- Also, click on the expand icon of the address column and select OK to proceed.
- You need to repeat the same thing for the columns that have the expand icon to get the full data.
- Now, click on the Close & Load option.
- Finally, you will see the XML data in a new sheet.
Read More: How to Convert XML to Columns In Excel
1.2 Using Developer Tab
In the second section, we will try to import the XML file using the Developer tab. The Developer tab is not included in the ribbon by default. But you can easily add it. Let me show you the process in simple steps below.
STEPS:
- First of all, check if there is a Developer tab in the ribbon.
- If you don’t find the Developer tab, then click on the File tab >> Options. It will open the Excel Options window.
- In the Excel Options window, go to Customize Ribbon and select Main Tabs in the “Choose Commands From” box.
- Then, select Developer and click on the Add option.
- If you face problems, then you can visit this link to add the Developer tab.
- Now, go to the Developer tab and select Import from the XML section. It will open the Import XML box.
- After that, select the XML file and click on the Import option.
- Here, we have selected the “File to Import.xml” file and imported it.
- As a result, a box will appear asking where to put the data.
- You can put the data in the existing worksheet or in a new worksheet. Here, we have selected Cell A1 in the existing sheet for importing the data.
- Click OK to move forward.
- Finally, you will see the data in the existing worksheet like the picture below.
Read More: How to Open XML File in Excel for Income Tax
2. Import XML Data from Web to Excel
In the second method, we will import XML data from a website to Excel. For XML data, we are using the Sitemap of Forbes. If you click on this link, you will see the information in the picture below:
Let’s follow the steps below to see how we can import the XML data into Excel easily.
STEPS:
- Firstly, go to the Developer tab and select Get Data. A drop-down menu will appear.
- Select “From Other Sources” and it will open another drop-down menu.
- Click on the From Web option from there. It will open the “From Web” box.
- Secondly, copy the link that contains the XML data.
- Paste the link into the URL box.
- Click OK to proceed. It will open the Navigator box.
- In the Navigator box, select the XML data that you need to extract.
- In our case, we clicked on “sitemap” in the left pane.
- After that, click on the Load option.
- Finally, you will see the locations of the pages in a new worksheet.
Read More: How to Convert Large XML to Excel
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In this article, we have demonstrated 2 easy ways to Extract Data from XML to Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- Difference Between XML and Excel Files
- How to Open XML File in Excel
- [Solved]: XML File Not Opening in Excel
- How to Convert XML to XLSX Without Opening File
- How to Convert XML to Excel Table
- How to Edit XML File in Excel
- VBA Code to Convert XML to Excel
<< Go Back to Import XML to Excel | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
