In this tutorial, I am going to show you 4 suitable ways to convert XML to columns in excel. You can use these methods even in large datasets to find out the data cells from XML data values. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
4 Suitable Ways to Convert XML to Columns in Excel
We have taken a relatively concise XML dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 2 unique columns which are Site Location URL and Date. Although we may vary the number of columns later on if that is needed.
1. Using XML from Local System
In this first method, we will convert XML to columns in excel from our local system. Let us see how to do this.
Steps:
- First, go to the Data tab and click on Get Data.
- Next, select From File and then click on From XML.
- Now, select an XML file and click Import.
- Then, in the Navigator window, select the XML data name and click Load.
- Consequently, this will create excel columns from the imported file.
Read More: How to Extract Data from XML File to Excel (2 Easy Ways)
2. Fetching XML from Web
We can also fetch XML data from the web using URL and convert it to columns in excel. Follow the steps below for this.
Steps:
- To begin with, go to the Data tab and click From Web.
- Here, paste the URL of the XML file and click OK.
- Now, select the appropriate file path and click Connect.
- As before, select the XML data name and click Load.
- Immediately, you should get the respective excel columns.
Read More: How to Convert XML to XLSX Without Opening File
3. Applying FILTERXML Function
The FILTERXML function in excel gives us the ability to convert cell data into columns from XML data. Below are the detailed steps.
Steps:
- To start with, copy the XML file data and paste it into cell B4 of an excel sheet.
- Then, go to a new sheet and insert this formula in cell B5:
=FILTERXML(FILTERXML!B5,"//PLANT/COMMON")
- Finally, press Enter and this should generate the excel columns from the XML file as we wanted.
4. Utilizing VBA Code
If you are familiar with VBA in excel, then you can easily convert XML to columns using just a few lines of code.
Steps:
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
Sub XML_to_Column()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wBook As Workbook
With Workbooks.OpenXML("C:\Users\ASUS\Downloads\sitemap_index.xml" _
, , xlXmlLoadImportToList)
.SaveAs ("C:\Users\ASUS\Downloads\Salary_Sheet.xlsx")
.Close
End With
End Sub
- Then, open the macro from the Developer tab by clicking on Macros.
- Now, in the Macro window, select the XML_to_Column macro and click Run.
- As a result, the VBA code will create the columns inside cells B4 to C10.
Read More: How to Convert Large XML to Excel (2 Effective Ways)
How to Convert XML to CSV in Excel
If you want to convert an XML file to CSV, then there is a very easy option to do this. Let us see that.
Steps:
- Firstly, click on the File option in the top-left corner of the window.
- After that, select Export and then click on Change File Type.
- Here, select CSV under Other File Types and click Save As.
- Next, give a name to the file and click Save.
- As a result, this will generate the CSV file which you can open with Notepad or any other text editor.
Read More: How to Open XML File in Excel (2 Easy Ways)
How to Convert XML to Excel Table
We can also convert an XML file directly into an excel table. The process is somewhat similar to what we have seen already. So, let us follow the steps below.
Steps:
- As previously, navigate to the Data tab and select Get Data.
- Then, click From XML under From File.
- Next, select the XML file and click Import.
- Now, select the XML data name and click Load To.
- Then, in the Import Data window, select Table and click OK.
- Finally, this will convert the XML file into an excel table.
Read More: How to Convert Large XML to Excel (2 Effective Ways)
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to convert XML to columns in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
