How to Import XML Data into Excel [Example]

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, 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

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

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

You will get the following options dialogue window

• Click on OK button
• You will get the following data

Let's take another example, this time you have local xml not in form of web link. You can download XML File below.

Download the XML File

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

• Then click on "From XML Data Import"

• Now select the XML File

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.