So you have an xml file with data you need extracted and converted into an excel sheet ? the following code should provide an idea of how to extract values from an xml file. This is adapted to the following example xml file, so you will need to tweak yours depending on the structure of your file.
Links you may need :
This is what the XML file looks like:
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[sourcecode lang="python"] #XML TO EXCEL FILE import xml.etree.ElementTree as ET from openpyxl import Workbook import os def readFile(filename): ''' Checks if file exists, parses the file and extracts the needed data returns a 2 dimensional list without "header" ''' if not os.path.exists(filename): return tree = ET.parse(filename) root = tree.getroot() #you may need to adjust the keys based on your file structure dict_keys = ["id","first_name","last_name","email","gender","ip_address" ] #all keys to be extracted from xml mdlist = [] for child in root: temp = [] for key in dict_keys: temp.append(child.find(key).text) mdlist.append(temp) return mdlist def to_Excel(mdlist): ''' Generates excel file with given data mdlist: 2 Dimenusional list containing data ''' wb = Workbook() ws = wb.active for i,row in enumerate(mdlist): for j,value in enumerate(row): ws.cell(row=i+1, column=j+1).value = value newfilename = os.path.abspath("./xml_to_excel.xlsx") wb.save(newfilename) print("complete") return result = readFile("./dataset.xml") if result: to_Excel(result) [/sourcecode]<br /><br /><br /> |
Resulting Excel File looks like this:
Hope it helped!