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
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
Suppose we have a text file consisting of some Book Names, Authors, and Published Years saved as XML data.
To import this data into Excel, we need to convert the text document to an XML data file.
STEPS:
- In the File Explorer, select the file to be imported.
- Right click to open the Advanced context menu.
- From the menu click Open.
The data is already stored in XML format.
- Click the File menu.
- Select the Save As option.
- After the title of the file add “.xml”.
- Click Save to store the file as an XML file.
As a result, we now have an XML data file. Let’s import it into our spreadsheet.
Read More: How to Open XML File in Excel
Method 1 – Using Data Tab
STEPS:
- Open a workbook.
- Go to the Data tab.
- From the Get Data list, click the From File option and select From XML.
- Select the XML file and click Import.
The Power Query window will open.
- Click the book tab.
- Click Load to to import the values to the desired location in the worksheet.
- 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.
We have successfully imported the XML data file into Excel.
- To edit the imported data, click the Transform Data option from the Navigator window.
The Power Query Editor will open, where you can edit the data file to make changes to the data table.
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.
- Open your workbook and go to the Data tab.
- Open the Get Data list, and from the From Other Sources list choose From Web.
A From Web dialog box opens
- In the URL section, paste the link and click OK.
- An Access Web Content dialog box opens
- Click the Connect button to continue.
- From the Power Query tool, choose the tab from the left section and hit Load to import the data into a new worksheet.
We have successfully imported XML data into our spreadsheet.
Read More: How to Extract Data from XML File to Excel
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
STEPS:
- Select the Developer tab, open the XML list, and press the Source option.
- From the right pane, click XML Maps.
- Inside the XML Maps window, click the Add button.
- Choose your XML file and click Open.
The file is added in the XML Maps.
- Close the window by clicking OK.
- Coming back to the workbook, drag the file inside the worksheet.
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.
- Select the XML file and click 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.
The XML file is imported into Excel.
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.
STEPS:
- Go to the Developer tab, and click XML then Import.
- Select both files while pressing the SHIFT key.
- With both files selected, click Import.
- Both files will be imported into the spreadsheet.
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
<< Go Back to Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
