How to Open & Convert XML File to Excel

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 https://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 https://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 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

How to Refresh Imported External Data

The main advantage of imported data over a copy and paste is that it keeps a live connection to the source, so it can be updated without importing again. A web feed such as the ECB exchange rates changes daily, and a single refresh brings in the new figures.

1. Refresh one connection: Click any cell inside the imported table, open the DATA tab, and choose Refresh.
2. Refresh everything: Choose Refresh All on the DATA tab to update every connection in the workbook at once.
3. Refresh automatically: Open Connection Properties and tick “Refresh every” a number of minutes, or “Refresh data when opening the file”, so the report stays current on its own.
4. Manage connections: Use Queries & Connections to see every source, rename it, or remove a connection that is no longer needed.

💡 Tip: If a refresh fails, the source address may have changed or moved behind a login. Open Connection Properties to check the URL, and confirm the feed still opens in a browser.

Power Query: The Modern Way to Import Data

On Excel 2016 and later, the Get & Transform group on the DATA tab, also called Power Query, replaces most of the older import buttons. It imports the same sources but adds a step to clean and reshape the data before it reaches the sheet.

• Get Data: Choose Get Data, then From File, From Database, or From Other Sources, including XML and the web.
• Transform: In the Power Query Editor, remove columns, filter rows, split text, and change data types, all recorded as repeatable steps.
• Load: Load the cleaned result into a table or straight into the data model, and refresh it later with one click.

The older From Web and From XML Data Import buttons still work and are shown above, which is why both approaches remain useful to know.

FAQs