WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Open XML

Excel Open XML

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel Open XML File

Data may come in different ways, and the source file varies from one situation to another. Data is the most important part of making decisions, but it is important to know how we can import data from different sources. Common file extensions we are all familiar with are “Text file and Excel file,” but there are several other sources available as well. In this article, we will show you how to open excel data into XML format and also how to import XML data into excel with neat formatting.

What is 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 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 excel worksheet.

Excel open xml Example 1

We need to convert this into an XML excel format like the below one.

Excel open xml Example 1-1

Here what we need to do is create two sample lines format for our data. As we have discussed, our rules to create a mandatory line of code is below.

<?xml version=”1.0″ encoding=”UTF-8″?>

<dataset>

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

After the table name, we need to enter the column header name, and that is <FirstName> 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, I have created the schema; you 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.

Copy the above code to a Text file and save the file as “XML.”

Excel open xml Example 1-2

Follow the below steps to open the excel data into XML format.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Excel open xml Example 1-3

Step 2: 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: Now, 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: Click on “Open,” and it will ask your confirmation.

Excel open xml Example 1-7

Step 6: 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

This will apply the “FirstName” column schema to the data table column.

Excel open xml Example 1-10

Now repeat the same for the remaining columns as well. Drag and drop respective schema columns to respective columns of the data table.

Excel open xml Example 1-11

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

Developer Example 1-12

Now this will ask you to save the file in the desired folder, give a name and save the file.

Export Example 1-13

After choosing the folder, click on “Export,” and your data will be extracted as the “XML Data” file.

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

Notepad Example 1-14

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

Import XML Data into Excel

Importing XML file to excel requires to follow certain steps. Follow the below steps to import data from an XML file.

Step 1: Under the Data tab, click on “Get Data & From File,” choose From XML.

Choose File Example 2

Step 2: This will ask you to choose the file from the stored location. Choose the file.

Select file Example 2-1

Step 3: Click on “Import,” and your data will be imported to an excel file.

Import Example 2-2

Like this, we can work with “XML Files” in excel.

Things to Remember

  • XML file follows a certain script, so you need to follow a mandatory line of codes as provided above.
  • When 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 whole data and export as an XML file.

Recommended Articles

This has been a guide to Excel Open XML. Here we discuss how to export excel data into XML format and also how to import XML data into excel. You may learn more about excel from the following articles –

  • MS Excel Uses
  • Connections in Power BI
  • Repair in Excel
  • ENVIRON in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More