Search code examples
pythonxml-parsingexport-to-csv

Multiple layer XML to CSV

I struggle to find a way to convert the XML file to CSV by python. This file has multiple attributes which I need to have in dataframe. Here is the example of XML file:

<helpdesk-tickets type="array">
---<helpdesk-ticket>
------<account-id type="integer">123</account-id>
---<notes type="array">
------<helpdesk-note>
---------<body>content 1 I need</body>
------</helpdesk-note>
------<helpdesk-note>
---------<body>content 2 I need</body>
------</helpdesk-note>
---</notes>
---</helpdesk-ticket>
---<helpdesk-ticket>
------<account-id type="integer">456</account-id>
---<notes type="array">
------<helpdesk-note>
---------<body>content 3 I need </body>
------</helpdesk-note>
------<helpdesk-note>
---------<body>content 4 I need </body>
------</helpdesk-note>
---</notes>
---</helpdesk-ticket>
</helpdesk-tickets>

This is my code:

import xml.etree.ElementTree as Xet
import pandas as pd
  
cols = ["account-id","notes"]
rows = []
  
xmlparse = Xet.parse('E:\python\Tickets132.xml')
root = xmlparse.getroot()

for i in root:
    display_id = i.find("account-id").text
  
for att in root.findall('./helpdesk-ticket/notes/helpdesk-note'):
    notes2 = att.find("body").text 
    
    
    rows.append({
                "account-id": display_id,
                "notes" : notes2,
                })
  
df91 = pd.DataFrame(rows, columns=cols)
display (df91)
df91.to_csv('output21.csv')

This is what I get:

account-id  notes
0   123 content 1 I need
1   123 content 2 I need
2   123 content 3 I need
3   123 content 4 I need

The expected output:

account-id  notes
    0   123 content 1 I need
    1   123 content 2 I need
    2   456 content 3 I need
    3   456 content 4 I need

Thanks in advance!

Solution

  • The problem is that you firstly iterate over account-id over whole file and then again with helpdesk-note. You need nested loop.

    This should work:

    import xml.etree.ElementTree as Xet
import pandas as pd

cols = ["account-id", "notes"]
rows = []

xmlparse = Xet.parse('E:\python\Tickets132.xml')
root = xmlparse.getroot()

for helpdesk_ticket in root.findall("./helpdesk-ticket"):   # iteration over every helpdesk_ticket
    display_id = helpdesk_ticket.find("account-id").text    # save account-id

    for helpdesk_note in helpdesk_ticket.findall(".//helpdesk-note"):    # find every helpdesk-note in iterated helpdesk_ticket
        notes2 = helpdesk_note.find("./body").text   # find body text
        rows.append({
            "account-id": display_id,
            "notes": notes2,
        })

df91 = pd.DataFrame(rows, columns=cols)
display(df91)
df91.to_csv('output21.csv')