Step by Step: How to Import an XML File to Excel?

Photo by Sara Kurfeß on Unsplash

In previous section, we have seen that what is an XML file and how we can use it in data validation. You can check the previous section from this link. In this section we are going to see how we can import an XML file to Excel. In Excel, in order to use import and export feature, you need to activate Developer tab from the options. You can do this by following the below steps;

Note that in the time this section was written, Microsoft Office 365 was used. So, please check your MS Office version to be able to do the followings.

1- Go to File tab,

2- Find Options on your screen. (It is on the below left corner of the screen.)

3- After you click Options, you will see a screen called Excel Options.

4- On the Excel Options, click Customize Ribbon. (It is on the left pane.)

5- After you click it, you will get a screen like this one;

Microsoft Excel Options Screenshot

6- You need to activate Developer tab by clicking it on the list which in on the right pane.

7- Then, click OK and close the Excel Options.

After you complete all the steps above, you can see the Developer tab as shown below;

Developer tab

At last, we can start to import an XML file 😊.

How to Import an XML File to Excel?

First, we need an empty Excel file. Go ahead and open one. Seco nd, we need an XML file ready so that we can use above XML Import option. I already prepared a simple XML file which contains only one row and 6 columns. We will use the one below.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><EmployeeTable>   <EmployeeRow>      <EmployeeName>Elleanor Carter</EmployeeName>      <Age>37</Age>      <StartDate>05/05/2020</StartDate>      <AnnualIncome currency="GBP">45000</AnnualIncome>      <Department>IT</Department>   </EmployeeRow></EmployeeTable>

When you go to Developer tab and click Import, Excel asks you to select an XML file. After you do that and click OK, Excel shows you this information;

MS Excel information box

We do not have a schema because we did not create our column names in our Excel. If we do that, then when we import an XML file, all related fields can match with our columns and all values goes to the right cells. That what Excel means by this information box.

For now, we can just click OK to this. After that, Excel asks where to start copying your data in Excel.

MS Excel Import Data Options

We again click OK to this and see how our XML file is imported in your Excel sheet.

Imported XML file into Excel Sheet

Note: You can also check this blog post and some other interesting topics on my personal web page.

Next: Step by Step: How to Export an XML File from Excel?




Hi 👋 , I write about Data Science. Let’s connect on LinkedIn | Follow me on GitHub

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

SLAs — Watch Out for the Shades of Gray

Atomic Increment/Decrement operations in SQL and fun with locks

How to create a project outline

A New and Improved Time to SLA for Jira Cloud

NFT Sniper Bot Upgrade

Common Lisp — Entering the Matrix

Stop writing hard-to-read Kotlin

EP18: TL;DR: Risk & Issue Management

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Efe Buyuk

Efe Buyuk

Hi 👋 , I write about Data Science. Let’s connect on LinkedIn | Follow me on GitHub

More from Medium

Using SQL to Organize Spreadsheet for JotForm Autofill documents

Well Euled Machines — 5

How to Install SQL Server on Windows

Understanding Tables & Columns in Microsoft Dataverse