Excel Open XML File

Data may come differently. The source file varies from one situation to another. Of course, data is the most important part of making decisions, but it is important to know how we can import data from different sources. We are familiar with common file extensions: “Text file and Excel file.” But several other sources are also available. This article will show you how to open Excel data into XML format and import XML data into Excel with neat formatting.

What is an XML File?

Extensible Markup Language of storing data in the XML format. With Excel data, we can convert the data into 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 format has the head and tail of the table name.

          Column1Column1

          Column2Column2

          Column1Column1

          Column2Column2

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.

We need to convert this into an XML excel formatXML Excel FormatExcel has made it very easy for us to import the data in XML to excel in the form of tables or databases. XML is basically an external data and it can be imported to excel from the data tab under the “get external data tab from data from other sources” section.read more like the below one.

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.

Next, we need to enter the table name as per our wish, and the table name is 

After the table name, we need to enter the column header name, next; we need to include the name and end this with the column header again.

Like this, 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.

         

James

Butt

New Orleans

Orleans

         

         

Josephine

Darakjy

Brighton

Livingston

       

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.”

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

Step 1: In the data workbook, we must go to the “Developer” tab and click on “Source.”

Step 2: This will open up below the “XML Source” window to the right of the worksheet, and click on “XML Maps.”

Step 3: This will open up the “XML Maps” window, then click on the “Add” button to choose the XML schema file.

Step 4: Now, choose the already saved XML file with the schema.

Step 5: Click on “Open.” It will ask for our confirmation.

Step 6: We need to click on “OK” two more times to get the schema headers to the left.

Step 7: Now, drag and drop the first column header “FirstName” from the XML source to the actual table in the worksheet.

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

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

So, all the XML schema columns are applied. Then, under the “Developer” tab excelDeveloper Tab ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more, click on the “Export” option.

It will ask us to save the file in the desired folder. We must give it a name and save it.

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

Now open the file, and all the rows are in the XML format.

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

Import XML Data into Excel

Things to Remember

  • The XML files follow a certain script, so we need to follow a mandatory line of codes as provided above.When you open your XML file, it will open with TEXT File only, but the format is 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.

This article has been a guide to Excel Open XML. 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: –

  • Firstly, under the “Data” tab, we must click on “Get Data” » “From File” and choose “From XML.” Consequently, this will ask to choose the file from the stored location. Therefore, we need to select the file. Click on “Import.” It will import the data to an Excel file. Like this, we can work with “XML files” in Excel.

Like this, we can work with “XML files” in Excel.

  • MS Excel UsesConnections in Power BIRepair in ExcelENVIRON in VBA