Python ElementTree xml output to csv

Question

I have the following XML file ('registerreads_EE.xml'):

<?xml version="1.0" encoding="us-ascii" standalone="yes"?>
<ReadingDocument xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ReadingStatusRefTable>
<ReadingStatusRef Ref="1">
  <UnencodedStatus SourceValidation="SIMPLE">
    <StatusCodes>
      <Signal>XX</Signal>
    </StatusCodes>
  </UnencodedStatus>
</ReadingStatusRef>
  </ReadingStatusRefTable>
  <Header>
<IEE_System Id="XXXXXXXXXXXXXXX" />
<Creation_Datetime Datetime="2015-10-22T09:05:32Z" />
<Timezone Id="UTC" />
<Path FilePath="X:\XXXXXXXXXXXX.xml" />
<Export_Template Id="XXXXX" />
<CorrelationID Id="" />
  </Header>
  <ImportExportParameters ResubmitFile="false" CreateGroup="true">
    <DataFormat TimestampType="XXXXXX" Type="XXXX" />
  </ImportExportParameters>
  <Channels>
<Channel StartDate="2015-10-21T00:00:00-05:00" EndDate="2015-10-22T00:00:00-05:00">
  <ChannelID ServicePointChannelID="73825603:301" />
  <Readings>
    <Reading Value="3577.0" ReadingTime="2015-10-21T00:00:00-05:00" StatusRef="1" />
    <Reading Value="3601.3" ReadingTime="2015-10-22T00:00:00-05:00" StatusRef="1" />
  </Readings>
  <ExportRequest RequestID="152" EntityType="ServicePoint" EntityID="73825603" RequestSource="Scheduled" />
</Channel>
    <Channel StartDate="2015-10-21T00:00:00-05:00" EndDate="2015-10-22T00:00:00-05:00">
  <ChannelID ServicePointChannelID="73825604:301" />
  <Readings>
    <Reading Value="3462.5" ReadingTime="2015-10-21T00:00:00-05:00" StatusRef="1" />
    <Reading Value="3501.5" ReadingTime="2015-10-22T00:00:00-05:00" StatusRef="1" />
  </Readings>
  <ExportRequest RequestID="152" EntityType="ServicePoint" EntityID="73825604" RequestSource="Scheduled" />
</Channel>
  </Channels>
</ReadingDocument>

I want to parse the XML of the channel data into a csv file.

He is what I have written in Python 2.7.10:

import xml.etree.ElementTree as ET

tree = ET.parse('registerreads_EE.xml')

root = tree.getroot()[3]

for channel in tree.iter('Channel'):
    for exportrequest in channel.iter('ExportRequest'):
        entityid = exportrequest.attrib.get('EntityID')
        for meterread in channel.iter('Reading'):
            read = meterread.attrib.get('Value')
            date = meterread.attrib.get('ReadingTime')
            print read[:-2],",",date[:10],",",entityid

tree.write(open('registerreads_EE.csv','w'))

Here is the screen output when the above is run:

3577 , 2015-10-21 , 73825603
3601 , 2015-10-22 , 73825603
3462 , 2015-10-21 , 73825604
3501 , 2015-10-22 , 73825604

The 'registerreads.csv' output file is like the original XML file, minus the first line.

I would like the printed output above outputted to a csv file with headers of read, date, entityid.

I am having difficulty with this. This is my first python program. Any help is appreciated.

Parfait · Accepted Answer · 2015-10-24 02:07:57Z

1

Use the csv module not lxml module to write rows to csv file. But still use lxml to parse and extract content from xml file:

import xml.etree.ElementTree as ET
import csv

tree = ET.parse('registerreads_EE.xml')

root = tree.getroot()[3]

with open('registerreads_EE.csv', 'w', newline='') as r:
    writer = csv.writer(r)
    writer.writerow(['read', 'date', 'entityid'])  # WRITING HEADERS

    for channel in tree.iter('Channel'):
        for exportrequest in channel.iter('ExportRequest'):
            entityid = exportrequest.attrib.get('EntityID')
            for meterread in channel.iter('Reading'):
                read = meterread.attrib.get('Value')
                date = meterread.attrib.get('ReadingTime')    

                # WRITE EACH ROW ITERATIVELY 
                writer.writerow([read[:-2],date[:10],entityid])

answered Oct 24, 2015 at 2:07

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

Collectives™ on Stack Overflow

Python ElementTree xml output to csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related