Import XML Files into Excel

2018-07-15

XML stands for eXtensible Markup Language and is a common data storage and transmission format. It’s widely used across the web and in many apps and software.

It’s a great data structure for computers to read, but it’s a bit less accessible for a human to read.

In this post we’ll take a look at how to import an XML file into Excel and turn it into a more friendly table format. Excel has support for XML files and it’s actually really easy to import and covert them to tables using power query.

You can download the example files used in this post here.

Video Tutorial

XML File Examples

You don’t need to look very far to find an example, Excel files are really just a collection of XML files in a zip folder.

Try it for yourself by taking any Excel file and changing the extension from .xlsx to .zip and then exploring the files in the zip folder. You’ll see a ton of XML files. You can open any of the XML files in your text editor of choice to view the inner workings of an Excel file.

Another common example can be found across the web. Most websites have a sitemap.xml file which is an XML file of the website’s link structure. This is literally a map of the website for bots like Google to crawl and index.

Check out the XML sitemap on one of my favourite Excel sites, https://www.vertex42.com/sitemaps/pages.xml. It lists all the pages on the site and includes information about when the page was last updated, how often the page is updated and how much weight search engines should give that page.

Create an XML Query

In this example we will use a simple XML file containing mock contact information with names, emails, cities and countries.

We can quickly create a new query that imports the XML file into Excel.

  1. Go to the Data tab in the ribbon.
  2. Select Get Data found in the Get & Transform Data section.
  3. Choose From File.
  4. Choose From XML.

This will open a file selection window and we can navigate to the location of the XML file and press the Import button.

Expand Fields in the Query Editor

The Navigator window will now open and we can see a preview of the data from the XML file. Notice that it’s in a nice table format? That’s automatically done by power query.

Since the Name and Email fields in the XML data contain sub-fields we see Table listed underneath the column headings in the preview. For example, the Name field contains a First and Last field for the first and last names of the person. If our data had a simple structure without any sub-fields, we could skip the query editor and press the Load button to directly load the data into the workbook.

To expand these tables out in the power query editor, press the Transform Data button.

In the query editor, click on the expand column toggle for the Name column which contains the Table elements. Choose the Expand option and press the OK button.

Repeat the process for the Email column as well. Depending on the data structure, we may need to do this several times.

Load the Data into a Table in the Workbook

Now our data is ready to be loaded into a table in the workbook.

Go to the Home tab in the query editor and press the the Close & Load button to load the data into a table in the workbook.

Conclusions

XML data is a very common format. You’re likely to come across it sooner or later if you work with data.

It can be hard to interpret what’s going on in an XML file, but luckily Excel can handle this type of data with ease.

In a few clicks, we can use power query to get our XML data into a nice table format.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

7 Comments

  1. Nick

    Have you worked with Spreadsheet XML files? They’re in the following format:

    JobNumberPONumberMRNumberWrittenDateDueDateRequiredDateNetDaysVendorNameDescriptionApproxValuepoh_committedCOMMDownloaded
    1010010100-020AG-3132010-12-21T00:00:00.0002010-12-23T00:00:00.00012/23/102WYLE E. COYOTELARGE NET350yes111/30/18 10:30:00
    1010010100-030AG-3142011-02-17T00:00:00.0002011-02-17T00:00:00.00002/17/110ACME, INC1 ROUND HOLE470yes111/30/18 10:30:00
    1010010100-055AG-3152012-02-15T00:00:00.0002012-03-13T00:00:00.00003/13/1227ROADRUNNER EXPRESSMAIL DELIVERY6373.41yes111/30/18 10:30:00
    1010010100-062AG-347-PF2632012-03-01T00:00:00.0002012-02-28T00:00:00.00002/28/12-2FUDD’S FUDGE1 POUND FUDGE18602.83yes111/30/18 10:30:00
    1803N1803/0136AK-15892018-11-29T00:00:00.0002018-12-04T00:00:00.00012/04/185MARTIAN CHRONICLEP-38 SPACE MODULATOR152538.50no011/30/18 10:31:43

    When dealing with thousands of rows, what’s the best way to get that back into table format?

    • John

      I’m not familiar with “spreadsheet XML” format. It doesn’t look like there is any delimiter. Maybe there is some sort of non-printing character that is delimiting the data? If so, you can use power query to split by delimiter. Otherwise I don’t have a suggestion.

  2. Nick Osdale-Popa

    It looks as though the commenting software stripped out the xml tags.
    Any way I can get a sample file to you?
    Or alternatively, take any Excel sample file and Save As XML Spreadsheet 2003 (*.xml)

    This will give you the same basic output.

    • John

      Wrap the text in code tags.

  3. Nick Osdale-Popa

    Here you go:

    JobNumberPONumberMRNumberWrittenDateDueDateRequiredDateNetDaysVendorNameDescriptionApproxValuepoh_committedCOMMDownloaded
    1010010100-020AG-3132010-12-21T00:00:00.0002010-12-23T00:00:00.00012/23/102WYLE E. COYOTELARGE NET350yes111/30/18 10:30:00
    1010010100-030AG-3142011-02-17T00:00:00.0002011-02-17T00:00:00.00002/17/110ACME, INC1 ROUND HOLE470yes111/30/18 10:30:00
    1010010100-055AG-3152012-02-15T00:00:00.0002012-03-13T00:00:00.00003/13/1227ROADRUNNER EXPRESSMAIL DELIVERY6373.41yes111/30/18 10:30:00
    1010010100-062AG-347-PF2632012-03-01T00:00:00.0002012-02-28T00:00:00.00002/28/12-2FUDD'S FUDGE1 POUND FUDGE18602.83yes111/30/18 10:30:00
    1803N1803/0136AK-15892018-11-29T00:00:00.0002018-12-04T00:00:00.00012/04/185MARTIAN CHRONICLEP-38 SPACE MODULATOR152538.50no011/30/18 10:31:43

  4. Nick Osdale-Popa

    It looks like it still stripped the XML tags. 🙁

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest