How to open XML in Excel?
XML is an Extensible Markup Language that contains the data in text format inside tags. It is a transmission format that is widely used across the web. The data in XML is not as much as readable in Excel. Excel stores data in table format that is more readable.
This chapter is all about XML file and how they can be opened on Excel software. For this, we will explore different methods. The XML users can use MS Excel software to import the XML data in it.
These XML to Excel topics we are covering in this chapter:
- What is XML?
- Where to open the XML file?
- Why to use Excel for XML?
- Import XML file to Excel
- Convert XML file to Excel format
- Import the XML file from web to Excel
- Import the XML from developer tab
What is XML?
XML is a markup language similar to HTML. Markup is basically a computer language that defines the text in tags (<>). In other words, you can say that XML is a tag-based language. Every file with .xml extension is an XML file.
The XML file is saved with .xml extension. XML is easily readable but not all the time for the non-technical users. Below is a simple code snippet example for XML code:
Remember that the first line of the XML file should contain the below line of code:
<?xml version= “1.0” encoding= “UTF-8”?>
Where to open the XML file?
A question might come to your mind: How can an XML file be opened if you have never worked with XML data. Which app can be used to open the XML file? You do not need to worry about it. It is very simple to open the XML file.
There are several ways in which the XML file can be opened, like in Chrome, any text editor (like Notepad, WordPad), XML explorer, and MS Excel as well. Besides this, the XML file can also be opened/read online. It means that the online XML editor tools are also there to open and read the XML file data.
Our target for this chapter is MS Excel. In this chapter, we will learn how an XML can be opened in Microsoft Excel.
Why to use Excel for XML?
XML is a good format to transmit the data on the internet that may use by developers. But for non-technical users, it is usually difficult to interpret the data in an XML file as it is a simple text-based file.
We know that Excel has a table structure to keep the data. So, this type of data can be easily handled by MS Excel. When the XML file is imported to Excel, a mapping is done between XML data and the Excel table through Power Query Editor.
With the help of a few clicks in Excel Power Query Editor, you can get the data in an easy-to-read format.
Import XML file into Excel
We will learn to import the XML file into Excel by taking an example. If you already have an XML file, you can just follow the below steps. For this example, we have taken a simple XML file to which we will import or convert into MS Excel.
Step 1: Look at the following XML data of book details. It has data such as – book title, description, price, publish date, etc.
We will convert this XML data to Excel format. Open a new Excel file in which to import the XML data.
Step 2: Select a cell to start placing the data and move to the Data tab in Excel ribbon on the Excel sheet.
Here, you see the New Query dropdown button inside the Get & Transform data group.
Step 3: Click the New Query inside the Get & Transform data group. From the list, select the From File and then choose From XML.
This will allow the users to select the XML file from the local storage to import into Excel.
Step 5: Look for the XML file inside your local storage and select it (the XML file), then click Import.
The select XML file will load to the PowerQuery (an advanced feature of Excel), from where you can take it to Excel.
Expand the fields in Query Editor
Step 6: A navigator with a selected XML file will open. Here, click the XML file in the left panel. Select the book to preview the inserted XML file data.
Step 8: The data is now transformed to the Excel Power Query Editor.
If your XML data does not contain the sub-fields, the users can skip the Power Query steps and directly click the Load button to load this data to Excel.
See that the xml data has been imported to the Excel sheet. Total five rows have been loaded to the Excel sheet.
If it has subfields, you have to follow a few steps further.
Steps when XML data have subfields
When the data is not fully shown in PowerQuery, you have to expand it first and then load the data into an Excel table. At the end, when the data is ready, load the data into an Excel table.
- Before this, click right corner icon of the column whose data is not showing.
- A small window joined with it will open where select Expand radio button and click OK.
- In the Power Query Editor, click the Close & Load button inside the Home This will successfully import the data to Excel table.
- See the imported data in the Excel sheet at the end of this example.
We have no such column with subfields. You need to perform these steps only when your data have subfields.
These steps can be used when the xml file is available on your system. If you do not have the XML file created or downloaded on your system, you have to use another method in Excel. This method works only for files stored on local storage.
Convert the XML file to Excel format
Rather than importing the XML file data directly into Excel, you can use another way. They can convert the XML file to Excel format using file conversion software or an online conversion website.
Several websites on the internet from which you can convert the xml file to Excel format. Xmlgrid.net is an online xml to excel converter site. You can use this site to convert the XML data to Excel format.
Conversion Tool is an XML to Excel file Conversion tool that is free to use. The users can convert up to 100 GB of XML data. For non-registered users, it offers 10 conversions per day. Let us see the steps for this tool as well.
Step 1: Click the link to open the XML to Excel conversion tool online. Here, click the Browse button that will allow the users to search the file in local storage.
Step 2: Select the XML to convert in xlsx format and click the Open button on this window.
XLSX is for Excel 2007+ version. For the below versions, go for XLS format.
Step 3: Selected XML file is inserted into the conversion tool. Now, choose .XLSX as we are using Excel 2016 version.
Step 4: Click the Run Conversion to convert the data into Excel.
Step 5: The uploaded XML file has been converted of size 5.76 KB. Click the Download File (the green highlighted button) to download it.
Step 6: After downloading the converted XML file data, you can open it into the Excel application. See the data after conversion.
Similarly, you can convert more XML files to xlsx format that can be later opened in Excel software.
Import the XML file from web to Excel
In the above example, you have seen that we had either imported the XML file into Excel or converted it online into Excel format that was stored on the local computer. In both examples, you had an XML file on your system.
What if you do not have any XML files stored in your system? You have an XML file on the web that you directly want to import and open into Excel software. For example, you have a web URL (https://www.forbes.com/sitemap_index.xml) of that XML file. For this, you have to use a different process for it.
“Remember this, you do not need to download that XML file and then import it to Excel.” You can directly import the file from the web by connecting it with Power Query.
Steps to import the web XML file to Excel
Following are the steps to import the web XML file to Excel software by connecting it Power Query with XML file web URL.
Step 1: Open the Excel file or sheet where to import the XML data from web.
Step 2: Select a cell in the sheet and move to the Data tab and then click the New Query under the Get & Transform group.
Step 3: From the list, select the From Other Sources and then choose From Web.
Step 4: MS Excel may ask for the security potential to connect with any external data source. Click OK to move forward.
Step 5: A From Web dialogue box will open where you enter the web URL of the XML file after choosing the Basic radio button. Then click OK.
Step 6: An anonymous window will open for the access web content. Here, click Connect to get access to this site.
Step 7: A navigator will open with a web XML data sitemap. Click the Sitemap containing XML data to preview the XML data from the web.
Step 8: See the preview for the web XML data that contains links and click the Transform Data button on this navigator.
- Use Transform data if you want to transform data before loading it into the Excel sheet. Thus, you can add or remove the columns and rows, change the column names as well.
- If you do not want any change and directly load it into the Excel sheet, choose Load.
Step 9: A Power Query Editor with web XML data will open where you can add or remove the row/column. All link has been loaded to Power Query Editor.
Step 10: Make the changes whatever you want and click the Close & Load button inside the Home tab.
Step 11: See that the data is now successfully imported to the Excel sheet started from the selected cell in step 1. 25 rows are imported here from the web XML URL.
If the data changes in the XML file whose weblink you provided, you simply need to refresh the query to get the newly updated data.
Import the XML from developer tab
There is another way to import the XML file data to Excel through the developer tab. From the developer tab, you need to follow fewer steps than the above methods to import the XML data to an Excel sheet.
To import the XML data from the developer tab to Excel, follow the given steps:
Step 1: Open the Excel file or sheet where to import the XML data from web.
Step 2: Move to the Developer tab inside, which click the XML dropdown button present in the end, then choose Import.
Developer > XML > Import
Step 3: Browse the XML file in your system and select it, then click Import to load the selected file in Excel.
Step 4: The specified XML format does not refer to a schema. Excel will create the schema for the source data itself. Click OK on this panel.
Step 5: A cell in the sheet has been selected automatically. You can also select a new sheet. Click OK on this window.
Step 6: See that the data has been successfully loaded/imported to the Excel sheet.
Now, you can read the data and close the file.
