This is an extension of the Python 4 Beginners: XML to Excel within a minute. A simple function will help generate a json file.
This is the XML File
The Code
#XML TO EXCEL FILE
import xml.etree.ElementTree as ET
from openpyxl import Workbook
import os
import json
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")
result = readFile("./dataset.xml")
if result:
to_Json(result)
Resulting File
Recommended Read:
Python File Handling: Create, Open, Append, Read, Write
Python 4 Beginners: XML to Excel within a minute
python: Creating a simple bar plot with python, matplotlib module and csv data
Automate Simple Tasks with Python: Excel Table to HTML Table using the python Dominate Module