Sometimes you may need to convert XML to an Excel table. In this article, I will explain how to convert XML to an Excel table using Microsoft 365 version.
You can download the practice workbook from here:
What Is an XML File?
An XML file can store data in a readable format for different applications and systems. Basically, XML stands for Extensible Markup Language. Actually, users can’t read the XML file easily. That is why it needs to be extracted into another format. Furthermore, the XML file is mainly used for storing and transporting data. It is similar to the HTML format, but the tags are not predefined.
However, 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.
3 Methods to Convert XML to Excel Table
Here, I will describe the 3 suitable methods for how to convert XML to an Excel table. Also, for your better understanding, I’m going to use two XML files. One is given below.
1. Use of Data Tab to Convert XML to Excel Table
Here, you can use the Data tab to convert XML to an Excel table. Furthermore, you can use two different ways under this Data tab. You may use this method when your XML file is saved on your device. Now, let’s talk about the steps.
Steps:
- Firstly, from the Data tab >> go to the Get Data menu.
- Secondly, from the From File feature >> select From XML option.
At this time, you will get a new window named Import Data.
- Now, select your XML file.
- Then, click on Import.
As a result, you will see the window named Navigator.
- Here, select the record file.
- Then, click on Load.
Subsequently, you will see the following result. If you notice, then you will see the Dates are in General format.
- Now, select the cells C2:C8.
- Then, choose Short Date from Number formatting which is in the Home tab.
So, your dates are now in Date format.
Here, if you want then you can format your Excel table too.
2. Employing Web URL to Convert XML to Excel Table
Now, I will employ the web URL to convert XML to an Excel table. You may use this method when you have the online XML file. Now, let’s talk about the steps.
Steps:
- Firstly, from the Data tab >> go to the Get Data menu.
- Secondly, from the From Other Sources feature >> select From Web option.
At this time, a new dialog box named From Web will appear.
- Now, choose Basic.
- Then, paste your file link to the URL box.
- Here, I used this XML File link.
- Subsequently, press OK.
As a result, you will see the window named Navigator.
- Here, select the url file.
- Then, click on Load.
Lastly, you will get the converted Excel table.
3. Using Import Feature from Developer Tab in Excel
However, you can use the Developer tab to convert the XML file to an Excel table. Let’s see the steps.
Steps:
- At first, from the Developer tab >> choose the Import option.
At this time, a new window named Import XML will appear.
- Firstly, choose your XML file in the File name box.
- Secondly, click on Import.
After that, you may see the following dialog box named Error In XML.
- If you see this, then press OK.
Subsequently, another dialog box named Import Data will appear.
- Here, I choose XML table in existing worksheet: including the position.
- Then, press OK.
Finally, you will see the following data table. Here, the Dates are in General format. So, I’ll change this formatting to Date.
The following is the final format of my converted Excel table.
Conclusion
I hope you found this article helpful. Here, I have explained 3 suitable methods how to convert XML to Excel Table. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.