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

This is Ohara from the China office.

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

However, even if you look at the contents of the XML file, which contains a huge amount of data, it is difficult to understand.

Therefore, we will introduce two methods for easily converting data from XML to Excel, converting it into a list, checking it, and viewing it.

pattern A

① Prepare a blank Excel file

② Prepare the XML file for data conversion

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

③ Drag and drop the XML file to empty Excel

Drag and drop the XML file from earlier onto the "blank Excel sheet" prepared in advance using your mouse.

After that, a selection pop-up will appear as shown below, select "Open as XML table".

Click OK when the pop-up "The specified XML source does not reference a schema. Create a schema based on the XML source data" appears as shown below.

After that, data import into Excel will begin automatically.

④ Data in the XML file is automatically reflected in Excel

Data import is now complete. It also automatically includes an autofilter function.

supplement

When importing data from XML to Excel using this "Pattern A" method, if the XML file to be converted has a large data capacity of "several tens of megabytes", the PC's graphics board may The processing operation becomes slow, or it takes time for Excel to load the XML table, which may cause some data import processing to fail or be lost.

If you are having trouble importing data using the "Pattern A" method we recommend the " Pattern B " described below, although it requires some operations

pattern B

① Prepare a blank Excel file

② Operate Excel tabs

In the Excel tab, click Data > From File > From XML.

③ Import the XML file for data conversion

As a sample, import the article data XML file < beyond-test-wordpress-article.xml > in WordPress.

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

④ Operate on the Power Query screen

The "Power Query" editor screen will automatically open.

Click the icon that looks like the left and right arrows below.

 

Click "Extract > OK".

The data records will be expanded as shown below.

After that, if you proceed in the same way to the column you want to expand by clicking the left and right arrows > Expand > OK, other data records will also be expanded.

 

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

Click Close & Load at the top 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 the data in an XML file and check, view, and share that data in Excel.

By the way, there are free generators that can convert XML files online, but since XML files may contain confidential company information (e.g. customer information), it is best to use a free generator. Not recommended.

When downloading and converting an XML file, be sure to convert and save it directly to Excel, etc.