[Easy] How to convert and import data from XML to Excel

When exporting data from a system, depending on the system, it may be downloaded in "XML file format"
(for example, a database in a core system or article data in WordPress).

However, the XML file contains a huge amount of data, so even if you just look at the contents, it is difficult to read and understand

Here we will introduce two methods to easily convert data from XML to Excel, create a list, and check and view it

Pattern A

① Prepare a blank Excel file

② Prepare the XML file to be converted

As a sample, we will use the XML file < beyond-test-wordpress-article.xml >, which contains article data in WordPress

3. Drag and drop the XML file into an empty Excel file

Drag and drop the XML file onto the blank Excel sheet you prepared in advance using your mouse

After that, a pop-up window like the one below will appear, so select "Open as XML table"

If a pop-up message appears saying "The specified XML source does not reference a schema. Create a schema based on the XML source data.", click "OK."

After that, the data will be automatically imported into Excel

④ Data in the XML file is automatically reflected in Excel

This completes the data import. The auto-filter function is also automatically enabled

supplement

When using this "Pattern A" method to import data from XML to Excel, if the XML file to be converted is large, such as "tens of MB," the import process may fail or be lost due to factors such as the PC's graphics card slowing down the PC's processing speed or Excel taking a long time to load the XML table

If you are unable to import data using "Pattern A," we recommend " Pattern B, " which is described below, although it requires a bit more work

Pattern B

① Prepare a blank Excel file

② Operate Excel tabs

In Excel, click Data > Get Data > From File > From XML

③ Import the XML file to be converted

As a sample, we will import the XML file < beyond-test-wordpress-article.xml > containing article data from WordPress

After importing the target XML file, the following pop-up will appear, so click "Convert data"

④ Operate on the Power Query screen

The Power Query editor screen will open automatically

Click the left and right arrow icon below

 

Click "Deploy" > "OK"

The data records will be expanded as shown below

Then, for the column you want to expand, click the left and right arrows > Expand > OK in the same way, and other data records will also be expanded

 

Various data will be displayed on the Power Query screen as shown below

Click "Close and Load" in the upper left of the Power Query screen. The data will then be reflected in Excel

The data is expanded and reflected in Excel

summary

This is useful when you want to list data from an XML file and check, view, and share that data in Excel

By the way, there are free generators available online that can convert XML files, but we do not recommend using free generators because XML files may contain confidential company information (e.g., customer information)

If you download and convert an XML file, be sure to convert and save it directly to Excel or similar