Excel Open XML File

Last Updated :

21 Aug, 2024

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Excel Open XML File?

With Excel data, we can convert the data into XML format. XML stands for eXtensible Markup Language.We need to know how we can import data from different sources. The source file varies from one situation to another.

We are familiar with common file extensions: “Text file and Excel file.” But several other sources are also available. This article will show us how to open Excel data into XML format and import XML data into Excel with neat formatting.

  • XML stands for eXtensible Markup Language. It is a language that can be read only by computers.
  • The XML files follow a certain script, so we need to follow a mandatory line of codes.
  • When we open our XML file, it will open with TEXT File only, but remember, the format will be in XML Schema.
  • We need to create at least two schema examples to apply the same logic to the whole data and export it as an XML file.

What Is XML File?

EXtensible Markup Language of storing data in the XML format. Like all the other external database rules, XML, too, has its own standard set of actions.

The following are the rules of the XML file:

  • The first line of the data should always include the below line of code.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  • XML format has the head and tail of the table name.
<TableName>

          Column1<Value>Column1

          Column2<Value>Column2

<TableName>

<TableName>

          Column1<Value>Column1

          Column2<Value>Column2

<TableName>

Like this, we can create an XML table to convert the excel data into an XML table format. Now we will see how to open excel data into XML format.

Example Of Converting Excel Data To XML File Format

For example, look at the below data in the Excel worksheet.

First_NameLast_NameCityCountry
JamesButtNew OrleansOrleans
JosephineDarakjyBrightonLivingston
ArtVenereBridgeportGloucester
LennaPaprockiAnchorageAnchorage
DonetteFollerHamiltonButler
SimonaMorascaAshlandAshland
MitsueTollnerChicagoCook
LoetaDilliardSan JoseSanta Clara
SageWieserSioux FallsMinnehaha
KrisMarrierBaltimoreBaltimore City
MinnaAmigonKulpsvilleMontgomery
AbelMacleadMiddle IslandSuffolk

Now, we need to convert this into an XML excel format as shown in the below image.

Excel open xml Example 1-1

Here, we need to create two sample lines formatting for our data. As we have discussed, our rules for creating a mandatory line of code are below.

<?xml version="1.0" encoding="UTF-8"?>

<dataset>

Next, we need to enter the table name as per our wish. So, let us enter the table name as <EmpRecord>

Then, after the table name, we need to enter the column header name, <FirstName> next; Remember, we need to include the name and end this with the column header again.

Likewise, we need to prepare a schema of XML data. For the above data, we have created the schema. Then, we can copy and paste the schema.

<?xml version="1.0" encoding="UTF-8"?> 

<dataset>

         <EmpRecord>

<FirstName>James</FirstName>

<LastName>Butt</LastName>

<City>New Orleans</City>

<Country>Orleans</Country>

         </EmpRecord>

         <EmpRecord>

<FirstName>Josephine</FirstName>

<LastName>Darakjy</LastName>

<City>Brighton</City>

<Country>Livingston</Country>

        </EmpRecord>

</dataset>

We have created two samples. So, by using this, we can apply the same schema to our Excel file and extract data in the same format (XML) as provided above.

We must copy the above code to a text file and save the file as “XML.”

Excel open xml Example 1-2

We must follow the below steps to open the Excel data into XML format.

  • Step 1: First, in the data workbook, we must go to the “Developer” tab and then, click on “Source.”
Excel open xml Example 1-3
  • Step 2: Next, this will open up below the “XML Source” window to the right of the worksheet, and click on “XML Maps.”
Excel open xml Example 1-4
  • Step 3: This will open up the “XML Maps” window, then click on the “Add” button to choose the XML schema file.
Excel open xml Example 1-5
  • Step 4: Now, choose the already saved XML file with the schema.
Excel open xml Example 1-6
  • Step 5: Next, click on “Open.” It will ask for our confirmation.
Excel open xml Example 1-7
  • Step 6: Now, we need to click on “OK” two more times to get the schema headers to the left.
Excel open xml Example 1-8
  • Step 7: Now, drag and drop the first column header “FirstName” from the XML source to the actual table in the worksheet.
Excel open xml Example 1-9

As a result, this will apply the “FirstName” column schema to the data table column.

Excel open xml Example 1-10

Now, we must repeat the same for the remaining columns. So, drag and drop respective schema columns to individual columns of the data table.

Excel open xml Example 1-11

Now, all the XML schema columns are applied. Then, under the “Developer” tab excel, click on the "Export" option.

Developer Example 1-12

Now, it will ask us to save the file in the desired folder. So, we must give a name and save it.

Export Example 1-13

Next, after choosing the folder, we must click on “Export.” The data will be extracted as the “XML Data” file.

Now, open the file, and we can see all the rows in the XML format.

Notepad Example 1-14

Likewise, we can extract or open the Excel data to the XML file.

Import XML Data Into Excel

Importing an XML file to Excel requires following certain steps. First, follow the below steps to import data from an XML file.

  1. First, under the “Data” tab, we must click on “Get Data” → “From File” and choose “From XML.”


    Excel open xml Example 1-15

  2. Consequently, this will ask us to choose the file from the stored location. Therefore, we need to select the file.


    Excel open xml Example 2

  3. Next, click on “Import.” It will import the data into an Excel file.


    Excel open xml Example 1-16
    Likewise, we can work with “XML files” in Excel.

Important Things To Note

  • XML language is a computer language but it is not read by humans.
  • XML stands for eXtensible Markup Language.
  • We can import XML files in excel using the Import option under the Data tab.

Frequently Asked Questions (FAQs)

1. What is XML file?

XML, eXtensible Markup Language is a language read by computer to define text. XML is just like HTML. We can import XML files in excel.

2. How to import XML file in excel?

We can easily open XML file in excel using the below steps:

• Step 1: First, go to Data → Get Data → From File → From XML

• Step 2: Next, the Import Data window opens.

• Step 3: Then, select the file and click Import.

Finally, the XML file will be imported.

3. What are some important points to remember while using XML in excel?

While using XML in excel, it is important to know that
XML has mandatory code lines.
Whenever we import XML file, it will open with TEXT File only, but the format is in XML Schema.

Recommended Articles

This article has been a guide to Excel Open XML File. Here, we discuss exporting Excel data into XML format and importing XML data into Excel. You may learn more about Excel from the following articles: –