Extensible Markup Language (XML) is a widely used data storage format on the web. Generally, users work with XML structure data in Excel in certain circumstances. In simple terms, XML is the form of storing the data in a pre-defined structure that the apps and systems can easily read. However, we need to convert it into a format that can be read easily.
Also, XML is the widely accepted file format for storing and transmitting data on the web. For example, consider the following sample data of XML data structure of two records.
Likewise, the next row of records starts with the word
We can import XML in Excel in two ways, the first is by using an XML extension file stored in our system, and the second is directly from the web.
Let us have a look at the following examples to understand both the ways.
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
First, we need to save the XML structured file in our system. Before that, let us design the XML file schema.
To begin with, add the following code to the notepad file.
It is the standard set of lines. We need to add it the beginning of the XML data.
Once we add the set of lines, copy the following XML structured data to the same notepad file.
Now, click on the Save option and save the file in the XML format, as shown in the following image.
Next, click on the Save button. We can see that the file is saved in XML format.
After saving the file, we need to import this in Excel under the Developer tab.
Though the Developer tab in Excel is unavailable, we can use the following steps to enable it.
Step 1: To begin with, go to the File tab.
Step 2: Next, click on Options.
Step 3: The Excel Options window opens up. Now, click on Customize Ribbon.
Step 4: Then, check the Developer tab box under Main Tab.
Step 5: Next, click OK.
Now, we will be able to see the Developer tab.
Once the Developer tab is enabled, click on the Source option under this tab.
Now, the XML Source window opens on the right side of our Excel screen. Click on the XML Maps… option in this window.
Next, it will open another window, as shown in the following image. Click on the Add button.
Now, we need to choose the XML saved file from our computer. Navigate to the folder and choose the XML file.
Click on Open, and Excel will prompt the following message. Click OK.
Now, we are back to the previous window. Next, click OK in this window.
Now, we can see all the column names in the selected XML table in the XML window on the right side.
Next, select all the columns in the above window. Then, drag and drop the selected columns to the worksheet.
Once we drag and drop the columns, Excel will create an empty table. Now, under the Developer tab, click on the Import option.
Next, it will ask us to choose the XML file from our saved folders. Then, choose the same file that we have saved in the subsequent steps.
Next, click on Import, and XML data will be stored as shown in the following image:
In this way, we can import XML in Excel. Next, let us look into the second method.
If the XML data is from the web, then we can import it from the web using a Power Query.
For instance, the following URL contains the data in XML format.
We will import this by using a power query.
First, go to the Data tab.
Next, click on the drop-down list of Get Data, hover on From Other Sources, and choose From Web.
Now, we should enter the URL in the From Web window.
Then, click OK. Now, Excel will create a connection from the mentioned URL.
Power Query preview data will be displayed as shown in the below image:
Click on Load, and XML data will be downloaded to the Excel file.
We can easily import the data using a Power Query. Under the Data tab, we have a group called Get & Transform Data.
To begin with, click on Get Data select From File and choose From XML.
Now, we should choose the file from the desired folder.
Next, click on Import. It will display the data preview as shown in the following image.
Click on Load.
Now, Excel creates a connection between the XML and Excel files.
Meanwhile, importing the data through a power query is best because, whenever we make a change, we can get the updated data easily to Excel with just a click of a button.
For instance, add the following record to the existing XML file.
Now, save and close the XML file.
Next, right-click on the already created connection in Excel, and click on the Refresh button.
As soon as we press Refresh, the data will be updated automatically.
Likewise, we can use power query and make it easier to work with XML data.
Once the source XML file is connected to Excel, we can map the required columns in Excel. For instance, we have loaded the following XML file already to Excel.
There are 4 columns in the XML data file. However, we do not necessarily need to load all 4 columns to Excel.
Out of these 4, we can choose the columns we want to see in the Excel worksheet.
Now, right-click on the column we want to map and click on Map Element.
Now, we should choose the targeted cell in the worksheet.
Next, click OK to create a mapping column in the selected cell.
Similarly, we can choose only the necessary columns and create a mapping in Excel.
For instance, we have mapped the following 3 columns, which are bold in the XML source window.
We can also map columns. Select the column and double-click on the column to map it.
If we have the XML file in our system, we can import it with a few steps.
For instance, we have the following sample data in the XML file.
Now, let us learn how to use XML in excel.
The steps required to use XML in excel are as follows:
Step 1: First, go to the Developer tab and click on Source.
Step 2: Next, click on XML Maps.
Step 3: Then, click on the Add… button.
Step 4: Choose the file from the saved folder.
Step 5: Next, click on OK. We can see that the XML file is mapped to the Excel file.
Step 6: Then, select all the columns and right-click on the same.
Now, click on Map element… option from the available options.
Step 7: Next, excel will create a table format, as shown in the following image.
Step 8: Then, under the Developer tab, click on Import.
Step 9: Next, import the same file that we imported earlier.
Step 10: Once the import is done, we will see the data in table format.
Likewise, we can use XML in excel.
To open XML in excel by default, we need to make some changes.
Step 1: First, type Windows + R and run regedit.exe.
Step 2: Next, navigate to the folders in the following path.
hkey_current_user/Software/Microsoft/Windows/CurrentVersion/Explorer/FileExts/.xml/OpenWithList/
Step 3: Then, choose EXCEL.EXE
Step 4: Next, restart the computer.
Likewise, we can open XML in Excel.
Why is the XML file not opening in Excel?XML file opens with notepad, and we can import the data into Excel. However, if there is a problem opening the XML file with Excel, then we need to change the default settings in our system.
This article must be helpful to understanding XML in Excel, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to XML in Excel. Here we discuss how to import and convert XML in excel (top 3 methods) along with examples and downloadable template. You can learn more from the following articles –
