Import Data from XML

Excel in 2016 has a new set of features known as Get & Transform or in earlier versions, it is known as Power Query. It provides a platform to perform a series of steps to help you to transform your Excel data. There is one step which will allow you to Import data from XML if your data source is not in your Excel spreadsheet.

 Previously in Power QueryHow to Filter Records Using Power Query?

Nowadays to import the data from a company’s accounting or sales system in the XML format are a very common thing so, don’t worry it’s very easy to import data from XML right into Power Query or Get & transform.  

NOTE 1: You can create random excel workbook online here for practice purposes as I did in this tutorial.

NOTE 2: If You use Microsoft Excel Prior to 2016 Version, then You Need to download Power Query Separately.

Here to show you as an example I’ll be using this set of data from the XML file:

Import Data from XML

STEP 1: Follow the written below:

If you’re using Excel 2016:

Follow this; first Go to Data > Get Data > From File > From XML

Import Data from XML

Or if you’re using Excel 2013 or Excel 2010

Then you need to follow this; Go to Power Query > From File > From XML

Click Import just after selecting the XML file that contains the data.

Import Data from XML

Now after clicking import you need to select the XML Data Source. You will be shown a preview of the XML data if you found it good then just press Transform Data.

Import Data from XML

STEP 2: now you will get an open window of Power Query Editor.

So, go ahead and manipulate your data but here for my example I’m going to keep the data as it is.

After that from the Home tab choose the Close & Load option to get a brand new worksheet with the imported data in your Excel.

Import Data from XML

Here you go with a new table in your Excel from the XML file.

Import Data from XML