In previous section, we have seen that what is an XML file and how we can use it in data validation. You can check the previous section from this link. In this section we are going to see how we can import an XML file to Excel. In Excel, in order to use import and export feature, you need to activate Developer tab from the options. You can do this by following the below steps;
Note that in the time this section was written, Microsoft Office 365 was used. So, please check your MS Office version to be able to do the followings.
1- Go to File tab,
2- Find Options on your screen. (It is on the below left corner of the screen.)
3- After you click Options, you will see a screen called Excel Options.
4- On the Excel Options, click Customize Ribbon. (It is on the left pane.)
5- After you click it, you will get a screen like this one;
6- You need to activate Developer tab by clicking it on the list which in on the right pane.
7- Then, click OK and close the Excel Options.
After you complete all the steps above, you can see the Developer tab as shown below;
At last, we can start to import an XML file 😊.
How to Import an XML File to Excel?
First, we need an empty Excel file. Go ahead and open one. Seco nd, we need an XML file ready so that we can use above XML Import option. I already prepared a simple XML file which contains only one row and 6 columns. We will use the one below.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><EmployeeTable> <EmployeeRow> <EmployeeName>Elleanor Carter</EmployeeName> <Age>37</Age> <StartDate>05/05/2020</StartDate> <AnnualIncome currency="GBP">45000</AnnualIncome> <Department>IT</Department> </EmployeeRow></EmployeeTable>
When you go to Developer tab and click Import, Excel asks you to select an XML file. After you do that and click OK, Excel shows you this information;
We do not have a schema because we did not create our column names in our Excel. If we do that, then when we import an XML file, all related fields can match with our columns and all values goes to the right cells. That what Excel means by this information box.
For now, we can just click OK to this. After that, Excel asks where to start copying your data in Excel.
We again click OK to this and see how our XML file is imported in your Excel sheet.
Note: You can also check this blog post and some other interesting topics on my personal web page.
