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

Afternoon? It is almost midnight....

Stephen Rauch
– Stephen Rauch ♦

2017-05-17 06:33:39 +00:00
Commented May 17, 2017 at 6:33 — Stephen Rauch
– Stephen Rauch ♦, Commented May 17, 2017 at 6:33

haoyu cai · Accepted Answer · 2017-05-17 06:27:19Z

4

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

edited May 17, 2017 at 6:27

answered May 17, 2017 at 6:20

haoyu cai

2792 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2337871 Over a year ago

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 Over a year ago

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 Over a year ago

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

user2337871 Over a year ago

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

Collectives™ on Stack Overflow

Python - Converting XML to CSV

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related