PPT Excel: How to load XML Files into Excel

Extensible Markup Language is known as XML. An XML file can store data in such a way using tags which is not easy to read by the user. So, you need to load XML files into excel to read or further analyze.

In this post, you learn how to use power query to convert an XML file to Excel or CSV.

XML Data file

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<student-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<record>
		<Products>David</Products>
		<Sales_H1>6138</Sales_H1>
		<Sales_H2>11805</Sales_H2>
	</record>
	<record>
		<Products>John</Products>
		<Sales_H1>9195</Sales_H1>
		<Sales_H2>10205</Sales_H2>
	</record>
	<record>
		<Products>Anton</Products>
		<Sales_H1>12237</Sales_H1>
		<Sales_H2>6810</Sales_H2>
	</record>
	<record>
		<Products>Patel</Products>
		<Sales_H1>8200</Sales_H1>
		<Sales_H2>5052</Sales_H2>
	</record>
	<record>
		<Products>William</Products>
		<Sales_H1>13862</Sales_H1>
		<Sales_H2>13808</Sales_H2>
	</record>
</student-data>

Open Notepad or any other text editor. Copy and Paste the above XML code into the text editor. Save the file name it Data.xml

How to convert XML file into excel

Please follow the below steps to convert XML to Excel

1. Go to the Data menu
2. Select Get Data
3. Choose From File
4. Select From XML – to open the Import Data dialog box

5. Select the input XML file Data.xml
6. Press Import

7. Select record
8. Press Load

You can see the XML data as an excel table in a new worksheet named record.

You can press Transform Data instead of Load. if you need to apply any transformation to your data before loading it into excel.

Suppose you need to add an additional column to calculate Total Sales

1. Press Transform Data – Open Power Query editor window
2. Press Add Column
3. Choose Custom Column

4. Type Total Sales in the New column name input box
5. Enter the formula = [Sales_H1] + [Sales_H2] in Custom column formula box
6. Press OK

You can see an additional column added in your table Total sales

7. Go to Home
8. Choose Close & Load
9. Press Close & Load

Conclusions

You might occasionally need to interact with the data in an XML file. It is a widely used and commonly accepted file format to store and transmit data on the internet.

Using the Power Query in Excel, you can input data from several sources and then clean, transform, and analyse it. You can use Power Query for the ETL Process in excel.