How To Import XML File into Excel & Convert XML to Excel (XLS or XLSX)Defined with Examples & More

Written By:
Lisa Borga
Reviewed By:
FundsNet Staff

If you work with databases, there is a strong chance that you have come across XML files and may have to work with the data.

XML, or extensible markup language, is an extremely common database file format used for storage and transmission.

XML uses custom tags which enables it to effectively search and retrieve information quickly and easily, making it ideal for structured databases.

However, the data inside an XML file is intended for machines and cannot be directly accessed or used by humans, unlike formats such as PDF, XLS, or XLSX.

Fortunately, by importing these files to Excel and converting them into Excel’s XLS or XLSX file formats, we can access and analyze the data they contain.

This is easy to do, and here we will show you how.

What Is XML

XML is a markup language designed as an efficient means for encoding data for both humans and machines.

It does this by using tags that instruct computers on both how the data should be stored and transported, making it perfect for use on the internet.

It does this through tags that you may be familiar with through the better-known HTML (Hypertext Markup Language) file format.

HTML files use predefined tags to encode data and instruct how it should be presented.

Unlike HTML, however, XML files do not use predefined markup language and instead allow users to create their symbols and describe content allowing virtually unlimited flexibility.

As a result, it is perfect for storing data and is used as the basis for hundreds of other file formats.

In fact, the Microsoft Office suite has used this file format ever since Office 2007 for document structure. XLSX files are essentially built from a collection of XML files inside a zipped folder which is part of why Excel is the perfect platform for accessing them.

Importing XML Into Excel with “Get Data”

If you already have an XML file saved that you would like to access with Excel, you can simply use the “Get Data” button on your ribbon to import it.

This makes it very easy to load the data into Excel and save it how you choose.

Here is how:

  1. Open a new workbook in Excel, where you will save the data contained in the XML file.
  2. Navigate to the “Data” tab on the ribbon and within the “Get & Transform Data” group, select “Get Data.”
  3. This will open a drop-down menu. Hover over “From File,” and then on the options that appear, select “From XML.”
  4. This will open a new dialog box where you can navigate to the location of the XML file you wish to import on your computer.
  5. Once your file is ready in the dialog box, select “Import,” and Excel will handle the process of importing your file and open “Navigator,” where you can see a preview of the data within the XML file.
  6. In “Navigator,” you can select the date you would like to import and if you would like to work with the data, such as changing column names. You can select the “Transform Data” option.
  7. Once you are ready, select “Load,” and Excel will load all of the data you have selected from the XML file into a new worksheet.

The Excel Get Data feature is an easy way to import data from an Excel file without any need to perform complex operations or use third-party programs to convert file types.

This also has the advantage of allowing you to update the data in Excel if the XML file is updated without the need to go through the entire import process again.

All you need to do is right-click anywhere in the data and select “Refresh.”

This will tell Excel to repeat the process and refresh the data allowing you to easily ensure it is up to date.

How To Import an XML File with a Web URL

In some cases, you may wish to import and access XML files that are not located on your system.

This is common with XML files on the web, which, in addition to storing and transporting data, are often used to provide functions for websites, such as organizing web pages and instructing search engines on how the site is structured.

When you have the URL containing the XML file you wish to import, it is easy to use Excel’s “Get Data” command to import it without even downloading the file.

This is very similar to how you would import an XML file located on your system, and here are the steps you can use to import an XML file located on the web.

  1. Open an Excel workbook where you would like to save the data contained in the XML file.
  2. Navigate to the “Data” tab and select “Get Data” from the “Get & Transform Data” group.
  3. In the drop-down menu that appears, hover over “From Other Sources” and select “From Web.”
  4. This will open a new dialog box where you can input the URL where your XML file is located and select “OK.”
  5. The “Navigator” dialog box will open, and here, you can select what data you would like Excel to import. Select the data you want, and if you would like to make changes to the data first, you can do so by selecting “Transform Data.”
  6. When you are ready, select “Load,” and Excel will finish importing the XML data into a new worksheet.

As you can see, loading a web-based XML file into Excel is similarly very easy. You can also easily refresh the data to ensure it is up to date by right-clicking and selecting “Refresh.”

Converting an XML File into XLSX or XLS

When you go to save your data, it is easy to save it as an XLS or XLSX file type.

Simply select the “File” tab on the top-left of the ribbon and then “Save As.”

Select the arrow on the right-hand side of the second line under the file name box, and this will open a drop-down menu with all of the file types that Excel can use when saving your file.

Here you can select XLSX, which has been the standard file type for Excel workbooks since Office 2007.

You can also choose XLS, which was Microsoft’s former file type for Excel workbooks.

In general, it is better to choose XLSX when possible because of its superior structuring and features; however, you can choose to save your XML file by selecting it and then “Save.”

Conclusion

XML files are extremely common, and if you work with databases or web files, chances are you will come across them.

Though you cannot work on XML files directly with Excel, you can easily import these files and access them with ease.

By following the steps above, you can import your files and convert them to XLS and XLSX in no time.