How to Extract Data from XML File to Excel: 2 Easy Ways

How to Extract Data from XML to Excel: 2 Easy Ways

We will extract data from the XML file to Excel using both the Data and Developer tabs.

Method 1 – Extract XML File from Your System to Excel

1.1 Using Data Tab

STEPS:

Go to the Data tab and select Get Data. A drop-down menu will appear.
Select From File there to open another menu.
Select From XML from there. It will open the Import Data box.

In the Import Data box, select the XML file and click the Import option.
We selected the “File to Import.xml” file and imported it.

The Navigator box will appear.
In the Navigator box, select the data from the XML file that you need to extract.
We clicked on “student” in the left pane.
Click on the Transform Data option.

The Power Query Editor will appear.
In the Power Query Editor, click on the expand icon of the first column, which is the name.

Select OK.

Click the expand icon of the address column and select OK to proceed.
Repeat the same steps for the columns that have the expand icon to get the full data.

Click the Close & Load option.

You will see the XML data in a new sheet.

1.2 Using Developer Tab

STEPS:

Check if there is a Developer tab in the ribbon.
If you don’t find the Developer tab, click on the File tab >> Options. It will open the Excel Options window.
In the Excel Options window, go to Customize Ribbon and select Main Tabs in the “Choose Commands From” box.
Select Developer and click on the Add option.
If you have issues, you can visit this link to add the Developer tab.
Go to the Developer tab and select Import from the XML section. It will open the Import XML box.

Select the XML file and click on the Import option.
We selected the “File to Import.xml” file and imported it.

A box will appear asking where to put the data.
You can put the data in the existing worksheet or in a new worksheet. Here, we have selected Cell A1 in the existing sheet for importing the data.
Click OK.

You will see the data in the existing worksheet like the picture below.

Method 2 – Import XML Data from Web to Excel

We will import XML data from a website to Excel. For XML data, we are using the Sitemap of Forbes. If you click on this link, you will see the information in the picture below:

STEPS:

Go to the Developer tab and select Get Data. A drop-down menu will appear.
Select “From Other Sources” and it will open another drop-down menu.
Click the From Web option from there. It will open the “From Web” box.