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.

extract data from xml to excel


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.

Extract XML File from Your System to Excel

  • 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.

Extract XML File from Your System to Excel

  • 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.

Extract XML File from Your System to Excel


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.

Extract XML File from Your System to Excel

  • 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.

Extract XML File from Your System to Excel


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:

Import XML Data from Web to Excel

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.

Import XML Data from Web to Excel

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


Download the Practice Workbook


Related Articles

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

 

 

 

 

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo