How to convert an XML file to an Excel file

0 votes

I have a directory that contains multiple XML files, lets's say it contains the following 2:

<Record>
        <RecordID>Madird01</RecordID>
        <Location>Madird</Location>
        <Date>07-09-2020</Date>
        <Time>07u43m55s</Time>
        <Version>2.0.1</Version>
        <Version_2>v1.9</Version_2>
    <Max_30e>
        <I_25Hz_1s>56.40</I_25Hz_1s>
        <I_25Hz_2s>7.44</I_25Hz_2s>
    </Max_30e>
    <Max_30e>
        <I_75Hz_1s>1.56</I_75Hz_1s>
        <I_75Hz_2s>0.36</I_75Hz_2s>
    </Max_30e>
</Record>

And:

<Record>
        <RecordID>London01</RecordID>
        <Location>London</Location>
        <Date>07-09-2020</Date>
        <Time>08u53m45s</Time>
        <Version>2.0.1</Version>
        <Version_2>v1.9</Version_2>
    <Max_30e>
        <I_25Hz_1s>56.40</I_25Hz_1s>
        <I_25Hz_2s>7.44</I_25Hz_2s>
    </Max_30e>
    <Max_30e>
        <I_75Hz_1s>1.56</I_75Hz_1s>
        <I_75Hz_2s>0.36</I_75Hz_2s>
    </Max_30e>
</Record>

Now I want to convert this to an excel file that shows every XML file in horizontal order like this: enter image description here

I tried to convert the XML to CSV string first and then to Excel but I got stuck, there should be easier ways.

This is my current code:

import xml.etree.ElementTree as ET
import os

xml_root = r'c:\data\Desktop\Blue\XML-files'

for file in os.listdir(xml_root):
    xml_file_path = os.path.join(xml_root, file)
    
    tree = ET.parse(xml_file_path)
    root = tree.getroot()
    tree = ET.ElementTree(root)

    for child in root:
        mainlevel = child.tag
        xmltocsv = ''
        for elem in root.iter():
            if elem.tag == root.tag:
                continue
            if elem.tag == mainlevel:
                xmltocsv = xmltocsv + '\n'
            xmltocsv = xmltocsv + str(elem.tag).rstrip() + str(elem.attrib).strip() + ';' + str(elem.text).rstrip() + ';
Jan 3 in Others by Kithuzzz
• 28,700 points
89 views

1 answer to this question.

0 votes

Create a CSV file that is in Excel-friendly format.

import xml.etree.ElementTree as ET
from os import listdir


xml_lst = [f for f in listdir() if f.startswith('xml')]
fields = ['RecordID','I_25Hz_1s','I_75Hz_2s'] # TODO - add rest of the fields
with open('out.csv','w') as f:
  f.write(','.join(fields) + '\n')
  for xml in xml_lst:
    root = ET.parse(xml)
    values = [root.find(f'.//{f}').text for f in fields]
    f.write(','.join(values) + '\n')
answered Jan 3 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

How can I convert excel file to pdf using TCPDF?

PHPExcel can only read charts from Excel2007 ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 53,160 points
334 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 53,160 points
1,466 views
0 votes
1 answer

How to convert pdf file to excel file using python

Just specify your whole output path instead ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 53,160 points
1,075 views
0 votes
1 answer

How to open an Excel file in C#?

You must have Microsoft Visual Studio Tools ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 53,160 points
216 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,323 views
0 votes
1 answer
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
465 views
0 votes
1 answer

How to update an existing excel .ods file?

It cannot be altered unless the original ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 53,160 points
254 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP