So you have an xml file with data you need extracted and converted into an excel sheet ?
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:
#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)
