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

Importing XML data files into Excel allows you to integrate structured data from various sources into your spreadsheets. Utilizing this strategy, you may use Excel’s research and visual features to benefit yourself while dealing with XML-based data. The main advantage of importing XML data file is you can analyze data from diverse sources in a user-friendly environment.

Today in this article, I will guide you on how to import XML data file into Excel including using data tab, web URL, developer tab with source and import feature, and importing multiple XML data file.

In the following, you will find an overview of how to import XML data file into Excel.

Download Practice Workbook

Import XML.xlsx

What Is an XML Data File?

An XML data file meaning Extensible Markup Language 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

Below I am sharing 3 simple tricks to import data file into Excel. Suppose we have a text file consisting of some Book Names, Authors, and Published Years saved as XML data. First, we need to convert the text documents to XML data file so that we can import it into Excel.

Selecting the file right click the mouse button to open the Advanced option. From the Advanced options click Open.

As you can see, we have the data stored in XML format.

Now, visiting the File option click the Save As option.

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

As a result, we have the XML data file in our hands. Now let’s import the XML data file into our spreadsheet.

Read More: How to Open XML File in Excel

1. Using Data Tab

With proper utilization of the Data feature, you can sort and filter pivot tables and data validation for accurate values. Here we will import XML data inside a worksheet.

To do so, open a workbook, visit the Data tab, from the Get Datalist click the From File option and select From XML.

Next, choose the XML file and hit Import.

Immediately, the power query window will open from which we will click the book tab and hit the Load to option to navigate the values in a desired location of your worksheet.

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

Finally, we have successfully imported XML data file into Excel. It’s that simple.

If you want to edit the imported data then you need to click the Transform Data option from the Navigator window.

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

2. Using Web URL

While exploring multiple sites, you won’t be able to download the XML file directly. Well, you don’t need to be afraid of that. If you want you can import XML data using the URL link. Without moving here and there let’s get into it right now.

Here, we have imported XML data from Forbes. You can click this link to access the data.
Visiting the link select the full site link and copy it.

Next, open your workbook, visit the Data tab, open the Get Data list, and From Other Sources list choose From Web.

In the URL section, paste the link and hit OK.

From the newly popped window, hit the Connect option to continue.

Hence, from the power query tool choose the tab from the left section and hit Load to import the data into a new worksheet.

In conclusion, we have successfully imported XML data into our spreadsheet.

3. Using Developer Tab

The Developer tab in Excel is primarily used for more advanced tasks related to Excel customization, automation, and macro development. Here, we will use this developer feature to import XML data file. Lying inside the Developer tab, there are 2 techniques for importing data quickly.

3.1 Using Source Option

In this part, we will utilize the Source tab to import XML data stored in the device storage.

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

Now, from the right pane, click XML Maps.

Inside the XML Maps window, press the Add option.

Thereafter, choose your XML file and click Open.

Now, you will see the file is added in the XML Maps Close the window by hitting 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.
It’s time to import data. For this choose any cell from the source table open the Advanced options, and choose Import from the XML tab.

Hence, select the XML file and hit Open.

Within a blink of an eye, the XML data file is imported into the spreadsheet.

3.2 Using Import Feature

If you want, you can import data directly utilizing the Import feature. But you must keep in mind that the XML file needs to be added in the XML Maps window.

Simply, visit the Developer tab and click the Import option.

Select the desired file and click Import.

In conclusion, the XML file is imported into Excel. Simple isn’t it?

How to Import Multiple XML Files into Excel

Similar to the previous methods, you can also import multiple files into Excel. Suppose we have a folder containing 2 XML files with Book List. Now, we will import both files into a workbook.

Start by, visiting the Developer tab, and clicking the Import option lying inside the XML

Now, choose both files by pressing the SHIFT key.
After both the files are selected click Import.

As a result, both files will be imported into the spreadsheet. It’s that simple.

Things to Remember

While importing XML files you must check if it’s valid or not. 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, Excel provides options to customize the XML import process. 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. You can 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.

Conclusion

In summary, the ability to import XML files into Excel is a useful tool that will enable you to easily include structured data from many sources into their spreadsheets. This method simplifies data management, improves analytical capabilities, and makes it possible to automate data updates. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

Import XML to Excel: Knowledge Hub

<< Go Back to Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!