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
|
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
[sourcecode lang="python"] #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) [/sourcecode] |
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
