Python - Converting XML to CSV

Question

Good Afternoon All.

I will preface this question by saying that this is my first foray into Python. I am using an API to return the following XML sample:

<Times>
    <Time>
        <ID> 120877787 </ID>
        <Job>
            <ID> J000050 </ID>
            <Name> My Job </Name>
        </Job>
        <Task>
            <ID> 59469972 </ID>
            <Name> My Task </Name>
        </Task>
        <Staff>
            <ID> 74268 </ID>
            <Name> My Name </Name>
        </Staff>
        <Date> 2017-05-19T00:00:00 </Date>
        <Minutes> 480 </Minutes>
        <Note/>
        <Billable> true </Billable>
    </Time>
</Times>

I am presently in the process of converting the XML to CSV using Python 3.4.

I have done a fair bit of research (http://blog.appliedinformaticsinc.com/how-to-parse-and-convert-xml-to-csv-using-python/ for example) into resolving the issue, but I can't come up with a suitable result, primarily because I don't understand the syntax well enough to adapt it to my exact circumstance.

Basically I am looking for the following output.

Job Name  Task Name  Staff Name  Date                 Minutes Billable
My Job    My Task    My Name     2017-05-19T00:00:00  480     true

As requested this is how the XML is returned from the API (as a string and viewed by print(ts.content)), which may be where I am going wrong.

<Times><Time><ID> 120877787 </ID><Job><ID> J000050 </ID><Name> My Job </Name></Job><Task><ID> 59469972 </ID><Name> My Task </Name></Task><Staff><ID>74268</ID><Name> My Name </Name></Staff><Date> 2017-05-19T00:00:00 </Date><Minutes> 480 </Minutes><Note/><Billable> true </Billable></Time></Times>

Could someone please offer some insight into the best way to approach this task?

Thank you for your help.

Scott

Solution

You can use findall function.

import xml.etree.ElementTree as ET
import csv

tree = ET.parse("/temp/test.xml")
root = tree.getroot()

f = open('/temp/test.csv', 'w')

csvwriter = csv.writer(f)

count = 0

head = ['Job Name','Task Name','Staff Name','Date','Minutes','Billable']

csvwriter.writerow(head)

for time in root.findall('Time'):
    row = []
    job_name = time.find('Job').find('Name').text
    row.append(job_name)
    task_name = time.find('Task').find('Name').text
    row.append(task_name)
    staff_name = time.find('Staff').find('Name').text
    row.append(staff_name)
    date = time.find('Date').text
    row.append(date)
    minutes = time.find('Minutes').text
    row.append(minutes)
    billable = time.find('Billable').text
    row.append(billable)
    csvwriter.writerow(row)
f.close()

Which gives:

Job Name,Task Name,Staff Name,Date,                Minutes,Billable
 My Job , My Task , My Name , 2017-05-19T00:00:00 , 480 , true 

Comments

[user2337871]

I am using print(ts.content) to view the results of my API query. Do I replace /temp/test.xml from tree = ET.parse("/temp/test.xml") with ts.content?

[user2337871]

I have tried using ET.fromstring() instead, but this results in a csv with the header row only. Do you have any other suggestions?

[haoyu cai]

Whats's your api result? Can you give it?

[user2337871]

I have added the output received from API to the original post.