How to Import XML to Excel [Examples]

Data is the bloodstream of any business entity. Businesses use different programs and formats to save the data depending on the business data storage requirements. You could have a payroll program powered by a database engine, you could have data in a CSV file or even from a website that you would like to analyse in Excel. This article shows you how you can achieve the above.

What is external data source?

External data is data that you link/import into excel from a source that resides outside excel.

Examples of external include the following

Data stored in a Microsoft Access database. This could the information from a custom application i.e. Payroll, Point of Sale, Inventory, etc.
Data from SQL Server or other database engines i.e. MySQL, Oracle, etc. – This could be information from a custom application
From a web site/web service – this could be information from a Web services i.e. currency exchange rates from the internet, stock prices, etc.
Text file i.e. CSV, tab separated, etc. – this could be information from a third party application that does not provide direct links. Such data could include bank payments exported to comma separated file CSV, etc.
Other types i.e. HTML data, Windows Azure Market Place, etc.

Website(XML data) external data source example

In this example to import XML into Excel, we will assume we are trading the Euro currency and would like to get the exchange rates from the European Central Bank web service. The currency exchange rate API link is http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

How to write a TEST CASE Software Testing Tutorial

Open a new workbook
Click on the DATA tab on the ribbon bar
Click on "From Web" button
You will get the following window

Enter http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml in the address
Click on Go button, you will get the XML data preview
Click on Import button when done

You will get the following options dialogue window

Click on OK button
You will get the following Excel import XML data

How to Import XML to Excel

Let's take another example on how to import XML file in Excel, this time you have local XML not in form of web link. You can download XML File below.

Download the XML File

Here is a step by step process on how to open XML file in Excel:

Step 1) Create a new workbook in Excel

Open a new workbook
Click on the DATA tab on the ribbon bar
Click on "From Other Source"

Step 2) Select the XML as Data source

Then click on "From XML Data Import"

Step 3) Locate and select the XML file

Now select the XML File to Excel sheet

You will get the options dialogue window as above example

Click on OK button
You will get the following data

Summary

Excel has powerful features that allow us to analyse numeric data and create visual reports such as charts. You can take advantage of external data importation to create your own custom reports that meet your business reporting requirements.

ORIGINAL Article