XML In Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

XML In Excel – Extensible Markup Language

XML are the files used in the databases which are shared over the web. Excel has made it easier for us to import the data in XML to Excel in the form of tables or databases. XML is external data that can be imported to Excel from the “Data” tab under the “Get Data” tab from the data from other sources.

XML – extensible markup language is the most common format to work and analyze data in this modern world. Excel allows us to convert spreadsheet data into XML files, and getting started is strange for common Excel users.

XML in Excel
Table of Contents
  • XML in excel is known as eXtensible Markup Language. It is useful for users who share the data via web.
  • We can easily import data in XML to Excel.
  • To import XML to Excel, simply click on Get Data under Data tab in Excel.
  • Remember, Head and tails should be the same under notepad coding. Also, we need to save the file as .XML in Excel.
  • In addition, XML is available as add-ins too. Search on the internet for more information regarding this.

How To Import XML Data Into Excel?

Let us see how to import XML data into Excel with apt examples.

Examples

Example #1 – Website (XML Data) Data Download

When searching the internet, we may find some useful information on the web. However, we need that data dumped into the worksheet of Excel. This article will show you how to download the data from the web server to a worksheet in Excel.

Assume you are working with foreign markets regularly and need the data of all the currency exchange rates for INR. Usually, we rely on XE.com for all the historical currency rates.

Follow the steps below to download the currency rates for 17th Jan 2019.

  1. Open the XE.com website.

  2. Search for "Historical Currency Rates."

  3. Select the currency and date which you want the data for. For example, we have selected INR and 17-04-2019.

  4. Go to the "Data" tab in Excel, and under "Get Data," select the "From Web" option.


    xml excel example 1.4

  5. Once you click on this, you will see this window.


    xml excel example 1.5

  6. Now, copy the URL you have created in XE.com and paste it into the "Address" section and click on "Go."


    example 1.6

  7. After clicking on "Go," you will see the webpage.


    xml excel example 1.7

  8. Once you see the result click on "Import."


    xml excel example 1.8

  9. After clicking the "IMPORT" button, Excel will ask you where to store the data. Select A1 as the cell reference.


    xml excel example 1.9

  10. After selecting the cell reference, excel will store the XML data as it is on the website and will take a few seconds to complete it.


    example 1.10

    This way, we can download the data from web servers and have our data in the spreadsheet.

Example #2 – Add XML (Markup Schema) To Excel

To add “XML Schema,” we need perfect column headings. We can create an XML Schema by using column headings.

  • Step 1: Open Notepad and copy the below code and paste.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
<Name>Amar Jyothi</Name>
<Salary>14051</Salary>
<Department>Sales</Department>
</record>
<record>
<Name>Radha</Name>
<Salary>17061</Salary> <Department>Marketing</Department>
</record>
</data-set>
  • Step 2: Save this as data-set.xml
example 2.2

It is important to ask two pieces of information from the data. First, we have added two names’ data to the list. It is an indication of the Excel to repeat throughout the worksheet.

  • Step 3: Ensure the “Developer” tab is enabled in your Excel ribbon and select “Source” from the “Developer” tab.
xml.excel example 2.3
  • Step 4: Under "XML Source" right-click on the "XML Maps."
xml.excel example 2.4
  • Step 5: Select the file you want to add.
example 2.5
  • Step 6: Select the desired file and click on the "OK" button.
xml.excel example 2.6

Once you click on the "OK" button, you will see the following output.

xml.excel example 2.7

Important Things To Note

XML is one of the structured languages for a database.

  • XML is an extensible markup language.
  • We can import and export under XML to the spreadsheet and spreadsheet to the XML.

Frequently Asked Questions (FAQs)

1) What is XML in Excel?

XML is structured data and one of the external data sources. We create a link or import data from outside where data resides outside the spreadsheet. We have many external databases like Payroll, POS (Point of Sale), Inventory Software, SQL Servers, etc.

2) What are some common rules of XML in Excel?

Like every other database software, XML has its own rules and regulations. Below are some of the common rules for XML.

• While writing the code data, both the head (start) and tail (end) should have similar tag names. For example:

<TableName> Data </TableName>

• Both head and tail are case sensitive.

3) What are the steps needed to do XML in Excel?

The steps are:
Step 1: Open Notepad. Copy the code and paste it.
Step 2: Save it as data-set.xml
Step 3: Ensure the “Developer” tab is enabled in your Excel Ribbon and select “Source” from the “Developer” tab.
Step 4: Under "XML Source" right-click on the "XML Maps."
Step 5: Select the file you want to add.
Step 6: Select the desired file and click on the "OK" button.