When exporting data from a system, some systems may allow downloads in the “XML file” format.(Examples: Databases of core systems, article data from WordPress, etc.)
However, if the XML file contains a large amount of data, it can be difficult to understand the content clearly just by viewing the file directly.
Therefore, this time we will introduce “two methods” — with simple operations, you can convert XML data into an Excel spreadsheet, making it easier to check and browse the data.
※ During actual operation, the interface may vary depending on your PC environment or software version. Please be aware of this in advance.
Table of Contents
This is an easy way to import an XML file into Excel.
Simply drag and drop the XML file into a blank Excel sheet, then select “Open as XML Table” in the pop-up dialog box. The XML data will be automatically imported into Excel.
This method is ideal for scenarios where you want to quickly view and verify XML data in Excel.
As an example, we’ll select and import the XML file < beyond-test-wordpress-article.xml >, which stores article data from WordPress.
Use your mouse to drag and drop the XML file directly into the pre-prepared blank Excel sheet.
A pop-up with options will then appear; select “Open as XML Table”.
If a pop-up appears with the message: “The specified XML source does not reference a schema. A schema will be created based on the XML source data”, click “OK”.
After that, Excel will automatically start importing the data.
※ If the XML file contains a large amount of data, the import process may take some time to complete.
This completes the data import. The imported data will also have the AutoFilter function applied automatically.
When importing data from XML to Excel using this “Method A”, if the XML file is large (e.g., over several dozen MB), the processing may slow down due to PC performance or graphics card limitations.
As a result, Excel may take longer to load the XML table, or some data may fail to import or be missing.
If you encounter issues with data import, “Method B” (covered later) is recommended, though it requires a few extra steps.
This method imports data from an XML file by using the “Power Query” feature built into Excel.
Power Query is a data processing tool included in Microsoft software such as Excel and Power BI. It mainly enables efficient data import, shaping, transformation, and preprocessing.
With this tool, even large XML files can be successfully imported into Excel.
From the ribbon menu at the top of Excel, open the “Data” tab, then click in the order of “Get Data > From File > From XML”.
As an example, select and import the XML file < beyond-test-wordpress-article.xml >, which stores article data from WordPress.
After importing the target XML file, a pop-up will appear; click “Transform Data”.
The “Power Query Editor” interface will open automatically.
Click the icon that looks like “left-right arrows” displayed on the interface.
Next, click in the order of “Expand > OK”.
After that, the XML data records will expand as shown below.
Move the “slider” at the bottom of the interface left or right to display other columns that need to be expanded.
Subsequently, follow the same steps: “Click the left-right arrows > Expand > OK” to expand data in any column.
As you proceed with the operation, various data will gradually appear on the Power Query interface.
Once you have expanded all desired columns and records in the Power Query interface, click “Close & Load” in the top-left corner of the interface.
After that, the converted data will be reflected in Excel. The process is complete when the expanded data is displayed correctly.
This makes the data much easier to read.
Converting XML file data into a list for checking, viewing, and sharing in Excel is a convenient method.
Additionally, there are free online tools that can convert XML files. However, some companies’ XML files may contain confidential information (such as customer data), so using such free tools is not recommended.
When converting an XML file, it is advisable to import it directly into software like Excel and then save it for processing.
For website creation, server construction/operation
▼ Company WeChat Official Account▼
▼ Contact person in charge WeChat ID ▼
Follow us on WeChat
WeChat Official Account
Person in ChargeWeChat
【Shenzhen】 What Is the “China-Japan Exchange Meeting” That Builds Bridges Between the Two Nations?