Multiple XML file to multiple Excel file

Hi Everyone, This is Rakesh, I’m new to Code academy, but I’m an intermediate level Python Programmer and Data Science enthusiastic, I just got a task in my company, to covert Large xml files into Excel files.

When you have a big file it is getting difficult to find a clue, when you are doing it for the first time.

Can anyone help me on this.

#Python #project #XML

1 Like

Hi @rakeshd3, welcome to the forums.

Did you open Excel and select File-Open, etc?

https://www.techwalla.com/articles/how-to-convert-xml-to-excel

I’ve not used this, so I don’t know how reliable it is but found it via a google search:
https://conversiontools.io/convert/xml-to-excel

I’ve coded in python can any one fix the code…
import xml.etree.ElementTree as ET

import xlsxwriter as xw

import os

from fnmatch import fnmatch

xml_file_cnt=0

ls_mdl =

ls_regno =

ls_cth =

ls_cty =

ls_opr =

ls_rsd =

ls_rdt =

ls_tailno =

ls_fname =

dir_path = input(‘Please Enter your directory path:’)

for filename in os.listdir(dir_path):

if fnmatch(filename,'*.xml'):

    xml_file_cnt +=1

    tree = ET.parse(dir_path + "/" + filename)

    root = tree.getroot()

    for x in root.findall(".//ReliabilityData//AircraftHoursAndLandings//AircraftHoursAndLandingsDetails/AID_Segment") :

        mdl = x.find('AMC').text

        ls_mdl.append(mdl)

        regno = x.find('AIN').text

        ls_regno.append(regno)

        tail_no = x.find('REG').text

        ls_tailno.append(tail_no)

        cth = x.find('CTH').text

        ls_cth.append(cth)

        cty = x.find('CTY').text

        ls_cty.append(cty)

        fname = filename

        ls_fname.append(fname)

    rng_mdl = len(set(ls_tailno))

    for y in root.findall(".//ReliabilityData//AircraftHoursAndLandings/HDR_Segment"):

        opr = y.find('OPR').text

        ls_opr.append(opr)

        rsd = y.find('RSD').text

        ls_rsd.append(rsd)

        rdt = y.find('RDT').text

        ls_rdt.append(rdt)

    ls_oprs = ls_opr*(rng_mdl//xml_file_cnt)

    ls_rsds = ls_rsd*(rng_mdl//xml_file_cnt)

    ls_rsds.sort()

    ls_fnames = ls_fname*(rng_mdl//xml_file_cnt)

    ls_rdts =ls_rdt*(rng_mdl//xml_file_cnt)

    print("("+str(xml_file_cnt)+"): " +filename)

    print("rng :" + str(rng_mdl))

    print("oprs:" +str(len(ls_rdts)))

               

    wb = xw.Workbook('D:\\xmltoexcel\\xml.xlsx')

    ws = wb.add_worksheet()

    ws.write("A1","Operator")

    ws.write("B1","Received Date")

    ws.write("C1","Aircraft Model")

    ws.write("D1","Reg. No.")

    ws.write("E1","Aircraft Serial No.")

    ws.write("F1","Flight Hours")

    ws.write("G1","Flight Cycles")

    ws.write("H1","Filename")

    item=0

    while rng_mdl == True:

        if item < rng_mdl:

         

            ws.write(item+1,0,ls_oprs[item])

            ws.write(item+1,1,ls_rsds[item])

            ws.write(item+1,2,ls_mdl[item])

            ws.write(item+1,3,ls_tailno[item])

            ws.write(item+1,4,ls_regno[item])

            ws.write(item+1,5,ls_cth[item])

            ws.write(item+1,6,ls_cty[item])

            ws.write(item+1,7,ls_fnames[item])

        item +1

    print("Write successful")

    wb.close()