Hey yall! Another snippet to convert the same XML file in Python 4 Beginners: XML to JSON within a minute into a simple CSV file.
XML File:
Code:
#XML TO EXCEL FILE
import xml.etree.ElementTree as ET
from openpyxl import Workbook
import os
import json
import csv
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
def to_Json(mdlist):
'''
Generates json file with given data
mdlist: 2 Dimenusional list containing data
'''
#create a file
newfilename = os.path.abspath("./data.json")
json_file = open(newfilename, "w");
dictionary = {"data":mdlist}
my_json_string = json.dumps(dictionary)
json_file.write(my_json_string)
json_file.close();
print("complete")
def to_CSV(mdlist):
'''
Generates csv file with given data
mdlist: 2 Dimenusional list containing data
'''
newfilename = os.path.abspath("./data.csv")
fh = open(newfilename, "w")
writer = csv.writer(fh, delimiter=' ',quotechar='|') #each value is separated by space; if "," substitute " " with ","
for row in mdlist:
writer.writerow(row)
fh.close()
print("complete")
result = readFile("./dataset.xml")
if result:
to_CSV(result)
Resulting CSV File
