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 

xml_to_excel

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

%d bloggers like this: