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.
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:
STEP 1: Follow the written below:
If you’re using Excel 2016:
Follow this; first Go to Data > Get Data > From File > 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.
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.
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.
Here you go with a new table in your Excel from the XML file.