Microsoft Office

How to Export Data into Excel from XML File

Let's Create a XML file of 'StudentData.xml' by just copy paste below text in it or You can download the 'StudentData.xml' file which i have attached here. 
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2.   
  3.   <Student>  
  4.  <MyData>  
  5.     <RollNo>10</RollNo>  
  6.     <StudName>Sandeep Singh</StudName>  
  7.     <StudClass>A+</StudClass>  
  8.   </MyData>  
  9.   <MyData>  
  10.     <RollNo>11</RollNo>  
  11.     <StudName>Ankur Mistry</StudName>  
  12.     <StudClass>A</StudClass>  
  13.   </MyData>  
  14.  <MyData>  
  15.     <RollNo>12</RollNo>  
  16.     <StudName>Rohan Shah</StudName>  
  17.     <StudClass>A+</StudClass>  
  18.   </MyData>  
  19.  <MyData>  
  20.     <RollNo>13</RollNo>  
  21.     <StudName>Rahul Lad</StudName>  
  22.      <StudClass>A</StudClass>  
  23.   </MyData>  
  24. <MyData>  
  25.     <RollNo>14</RollNo>  
  26.     <StudName>Rushabh Parekh</StudName>  
  27.      <StudClass>A</StudClass>  
  28.   </MyData>  
  29. <MyData>  
  30.     <RollNo>15</RollNo>  
  31.     <StudName>Sonal Shah</StudName>  
  32.      <StudClass>A</StudClass>  
  33.   </MyData>  
  34.      
  35. </Student>  
Step 1: Open your Microsoft Excel 2013 and open a blank workbook.
 
Click on Data Tab, Select From Other Sources -> From XML Data Import, Shown as per below Screen.
 
  
 
Step 2: Select the StudentData.xml from your PC.
 
 
 
Step 3: Next, it will ask Where do you want to put data? here you can map your columns and where you want to place it, for now just click 'Ok'
 
 

It will display the XML Data in your excel file. just save the data.

If you note here all your tag like '<RollNo>' or '<StudentName>'  became Headers in Excel Spreadsheet.
 
  
 
That's it, Hope you like 3 simple steps to convert data from XML to Excel.
Ebook Download
View all
Learn
View all
Membership not found