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 – Import an XML File to Excel

1.1 Using the 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 the 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. 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

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.

• Copy the link that contains the XML data.
• Paste the link into the URL box.
• Click OK. It will open the Navigator box.

• In the Navigator box, select the XML data that you need to extract. We clicked on “sitemap” in the left pane.
• Click on the Load option.

• You will see the locations of the pages in a new worksheet.

Download the Practice Workbook

Related Articles

• Difference Between XML and Excel Files
• [Solved]: XML File Not Opening in Excel
• How to Convert XML to Excel Table
• How to Edit XML File in Excel
• VBA Code to Convert XML to Excel

<< Go Back to Import XML to Excel | Importing Data in Excel | Learn Excel