XML in Excel (Table of Contents)
Introduction to Extensible Markup Language (.XML)
Designed for storing and transporting data. It is a markup language similar to HTML. XML File appears as below mentioned, where it is a note to John from Craig.
XML File type & structure appears as below mentioned:
<note>
<to> John</to>
<from> Craig</from>
<heading>Reminder</heading>
<body>Don’t forget me this weekend!</body>
</note>
- XML is just a piece of information wrapped in tags, just like above mentioned note.
- Usually, most of XML applications work as expected even if you add new data (or it is removed).
- XML Simplifies tasks like data transport, data sharing, data availability & platform changes.
- XML standard is a flexible & easy way to create information formats.
- XML helps out in electronically sharing structured data via the public Internet or through a corporate network.
- XML is a hierarchical structure.
Definition of XML & XSD
- XML Schema language is also referred to as XML Schema Definition (XSD).
- XSD defines the structure of the XML file which needs to be created and also it indicates, where the columns need to be mapped in the tabular excel data.
- It is also referred to as Extensible Stylesheet Language Transformation (XSLT) or (.xslt file).
- XSD implement data type and validation to file.
- XSD allows you to create elements, Data relationships & Data validation is defined in XML.
- XML data files (.xml), which contains custom tags and structured data.
How to Import XML File in Excel?
You can import the XML file in Excel from the source option in the Developer tab. If the Developer tab is not available in the Ribbon, you need to add the developer tab, with the mentioned steps or procedure.
Go to “File” and select “Options.” and select “Customize the Ribbon” or you can right-click on the Home tab and select “Customize Ribbon” as shown below.
In Excel Options –
When you return to the main Excel page, you will see the Developer tab next to the view tab at the top. In that, if you need to select the source tool to map elements in your XML to your spreadsheet.
Let’s check out the steps available to convert excel file to an XML data file & vice versa.
Step 1 – To Convert your Spreadsheet into XML, you need a Markup Schema
This markup schema defines each field that you’ll use in your XML file i.e. the structure of the XML file. We have to do this outside Excel, in the other filetype.
In the below-mentioned spreadsheet, I have four types of column & information, i.e. Car make, Car model, its year of manufacture, and at last Car cost or value. So, in our Excel schema, will have four different tags and we need to create it.
In the above spreadsheet, it contains four columns, each of which will become an element. The car data in the above cells of excel are not independent. For example, I have four cells in a row all that row data is related to each other, it provides details about a car type and its information, i.e. It’s brand name, model name and year of purchase and its cost or value at present.
Open Notepad and add the following two lines, they are the standard pieces of information that XML files need at the beginning. (Note: It is optional ).
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
The element is declared from line three onwards. The record tags i.e. < >defines a single record described in our spreadsheet. whereas other tags define the relevant pieces of information under that for that record. i.e.
<record>
<Make>Toyota</Make>
<Model>Fortuner</Model>
<Year>2010</Year>
<Value>949999</Value>
</record>
Finally, add the closing </record> tag at the end of the file in note pad.
I just added the first-row data for the explanation purpose. You can add as many rows, based on your choice. Again, you can start with <record>, after the </record> tag. Once you entered the data in the notepad, you can save it as test.xml in the file name, and save it on desktop.
Later, you can create a new excel file with filename XML.xlsx
As you have noticed, we entered one car detail (one records) in the XML file (NOTEPAD). Now, you have created a markup schema and saved on your desktop.
Step 2 – How to Add this Markup Schema to New Excel File
In the Developer tab, click on Source in the XML section.
XML window pane appears, click on XML Maps.
XML Maps window appears, in that, Click Add in the resulting window.
Then you can navigate to your XML file on desktop which you saved as Abc.xml and you can select it.
Due to excel version issues in my system, I am uploading other .xml file with other option. The other option in the developer tab is to select Import in the Home tab.
Once you select a .xml file on desktop, a popup appears, click Ok.
Now to import this excel data right-click on XML file name and select MAP Element option.
Then you will see the following popup, select the location to where you want your data.
Then you will see the table headers in your excel sheet.
Now under the developer tab click on Import.
It will open a window to select the file.
Output:
Another popup appears asking for cell reference, where you want the data to be placed.
The import .xml file type appears as shown earlier. Similarly, Export feature in the developer option will be available if your spreadsheet contains XML file data, otherwise, export option will be greyed out.
Here, I opened an excel file containing XML file data, I can export it and save it as .xml file type anywhere with export option in developer tab.
Things to Remember About XML in Excel
- Every bit of data you enter has to start and end with an identical tag i.e. start with < Record> Data </Record> (End with).
- Tag names is case sensitive i.e. capitalization in the end tag should be same as the beginning tag.
- The XML file should always begin and end with a root tag. There can only be one root tag in a file. In the example above, the root tag is <Record>.
- You can have an empty tag i.e. you can mention or put the slash at the end of the tag instead of the beginning: <Record> </Record>.
Recommended Articles
This is a guide to XML in Excel. Here we discuss How to Import & Export XML Data File along with practical examples and downloadable excel template. You can also go through our other suggested articles –
