How to Import XML Data File into Excel (3 Quick Methods)

Overview of how to import XML data file into Excel

Importing XML data files into Excel allows you to integrate structured data from various sources into your spreadsheets to take advantage of Excel’s research and visual features while dealing with XML-based data. The main advantage to importing XML data is to enable analyzing data from diverse sources in a user-friendly environment. In this article, we will discuss how to import XML data files into Excel in various ways, including using the Data tab, a web URL, the Developer tab with the Source and Import features, and importing multiple XML data files. XML data files will look similar to the image below after importing them into Excel.

Download Practice Workbook

Import XML.xlsx

What Is an XML Data File?

An XML (Extensible Markup Language) data file serves as a structured means of storing and organizing data. It’s a plain-text file format that employs a tag-based system, making it both human-readable and machine-readable. XML data files act as a universal language for data exchange that can communicate between different software systems and programming languages.

How to Import XML Data File into Excel: 3 Quick Methods

In the File Explorer, select the file to be imported.
Right click to open the Advanced context menu.
From the menu click Open.

Clicking the Open option to open the .txt file

The data is already stored in XML format.

Sample text file containing XML data

Clicking Save As option from the File tab

After the title of the file add “.xml”.
Click Save to store the file as an XML file.

Changing the file format to XML and hitting Save

As a result, we now have an XML data file. Let’s import it into our spreadsheet.

Sample data created by converting .txt documents into .xml

Method 1 – Using Data Tab

Open a workbook.
Go to the Data tab.
From the Get Data list, click the From File option and select From XML.

Clicking the From XML option from the Data tab

Selecting the XML file from the device storage

The Power Query window will open.

Click the book tab.
Click Load to to import the values to the desired location in the worksheet.

Clicking the book tab and hitting Load to option

From the Import Data window that opens, select Existing worksheet and choose a cell (B4) where the data will be inserted. You can also import data as a PivotTable Report, PivotChart, or Only Create Connection from the tab.

Selecting a new location inside the worksheet to import data

We have successfully imported the XML data file into Excel.

Final result with importing XML data file into Excel

To edit the imported data, click the Transform Data option from the Navigator window.

Clicking the Transform Data feature from the Navigator window

The Power Query Editor will open, where you can edit the data file to make changes to the data table.

Opening the Power Query Editor to edit the imported data

Method 2 – Using Web URL

While exploring multiple sites, you won’t be able to download the XML file directly. But you can import XML data using the URL link. Here, we will import XML data from Forbes.

STEPS:

Click this link to access the data.
Select the full site link and copy it.

Selecting the URL and copying it

Open your workbook and go to the Data tab.
Open the Get Data list, and from the From Other Sources list choose From Web.

Selecting From Web option by visiting the Developer tab

A From Web dialog box opens

Pasting the URL and hitting OK

An Access Web Content dialog box opens
Click the Connect button to continue.

Clicking Connect from the Access Web content window

From the Power Query tool, choose the tab from the left section and hit Load to import the data into a new worksheet.

Selecting the data from the left pane and hitting the Load option

We have successfully imported XML data into our spreadsheet.

Final result with importing XML data from URL

Method 3. Using Developer Tab

The Developer tab in Excel is primarily used for more advanced tasks related to Excel customization, automation, and macro development. We can use it to import XML data files using 2 different techniques.

3.1 – Using Source Option

Select the Developer tab, open the XML list, and press the Source option.

Clicking the Source option from the Developer tab

From the right pane, click XML Maps.

Clicking the XML Maps from the right pane

Pressing the Add option from the <a href= XML Maps window" width="452" height="238" />

Selecting the XML file and hitting Open

The file is added in the XML Maps.

Clicking OK after adding the XML file

Coming back to the workbook, drag the file inside the worksheet.

Dragging the XML file inside the spreadsheet

As a result, the table consisting of data will be inserted inside the worksheet.

To import the data, select any cell in the source table.
Right click to open the Advanced options.
Choose Import from the XML option.

Selecting the Pivot table and inserting XML file by pressing Import

Selecting the XML file and hitting Open

The XML data file is imported into the spreadsheet.

3.2 Using Import Feature

We can import data directly utilizing the Import feature. The XML file will need to be added in the XML Maps window first.

STEPS:

Go to the Developer tab and click the Import option.

Select the desired file and click Import.

Choosing the XML file and hitting Import

The XML file is imported into Excel.

Final result with importing the XML file

How to Import Multiple XML Files into Excel

Similarly, we can also import multiple XML files into Excel. Suppose we have 2 XML files containing a Book List in a folder. Let’s import both files into a workbook.

Sample dataset with 2 XML files inside a folder

STEPS:

Clicking the Import option from the Developer tab

Select both files while pressing the SHIFT key.
With both files selected, click Import.

Holding SHIFT key and selecting both the XML file and hitting Import

Both files will be imported into the spreadsheet.

Final result with importing multiple XML files into Excel

Things to Remember

While importing XML files ensure that the xml code is valid, otherwise errors in the XML structure can cause import issues. You may need to work with the data provider or validate the XML file before importing it.

Frequently Asked Questions

1. Can I customize the import process for XML data?

Yes, you can map XML elements to specific Excel columns, set data types, and configure refresh settings to keep the data up-to-date.

2. What should I do if the XML file has a complex structure or namespaces?

Complex XML structures or namespaces may require more advanced handling. Use Excel’s tools to manage namespaces and map elements to columns manually during the import process.

3. What if there are errors during the XML import process?

If errors occur during import, Excel will provide error messages that describe the issue. You may need to review the XML file’s structure, mapping, or data quality to address these errors.

Import XML to Excel: Knowledge Hub

Difference Between XML and Excel Files
Convert Large XML to Excel
Convert XML to XLSX Without Opening File
Convert XML to Excel Table
Convert XML to Columns In Excel
Open XML File in Excel for Income Tax
Edit XML File in Excel
VBA Code to Convert XML to Excel
[Solved]: XML File Not Opening in Excel