Start Free TrialLog in
Avatar of Scott Abraham
Scott AbrahamFlag for United States of America

asked on 

Help with a Python script converting xml to csv

I have an xml file that I want to extract data from and put it into a CSV file.  I have most the script working fine.  My problem is each row or data point needs a time stamp.  In the xml the times are given as a start and stop time with the number of time intervals/data points given instead of time.

For Instance:
<ContiguousIntervalSet NumberOfReadings="24">
          <TimePeriod StartTime="2017-03-21T00:00:00-05:00" EndTime="2017-03-22T00:00:00-05:00" />

Open in new window

This tells me that their are 24 reads over 24 hours, each one being at the top of the hour.
Here is what the reads look like:
 <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />

Open in new window

This tells me the 24 data points for the time frame, but no date/time.

This tells me what item and point I am looking for:
<ChannelID ServicePointChannelID="73825602:101" />

Open in new window

Ideally in the output file in this instance it would be like this:
Value,EntityId,ReadingTime,ServicePointChannelID
1,73825602,2017-03-21T00:00:00-05:00,101
1,73825602,2017-03-21T01:00:00-05:00,101
1,73825602,2017-03-21T02:00:00-05:00,101
1,73825602,2017-03-21T03:00:00-05:00,101
1,73825602,2017-03-21T04:00:00-05:00,101
1,73825602,2017-03-21T05:00:00-05:00,101
1,73825602,2017-03-21T06:00:00-05:00,101
1,73825602,2017-03-21T07:00:00-05:00,101
1,73825602,2017-03-21T08:00:00-05:00,101
1,73825602,2017-03-21T09:00:00-05:00,101
1,73825602,2017-03-21T10:00:00-05:00,101
1,73825602,2017-03-21T11:00:00-05:00,101
1,73825602,2017-03-21T12:00:00-05:00,101
1,73825602,2017-03-21T13:00:00-05:00,101
1,73825602,2017-03-21T14:00:00-05:00,101
1,73825602,2017-03-21T15:00:00-05:00,101
1,73825602,2017-03-21T16:00:00-05:00,101
1,73825602,2017-03-21T17:00:00-05:00,101
1,73825602,2017-03-21T18:00:00-05:00,101
1,73825602,2017-03-21T19:00:00-05:00,101
1,73825602,2017-03-21T20:00:00-05:00,101
1,73825602,2017-03-21T21:00:00-05:00,101
1,73825602,2017-03-21T22:00:00-05:00,101
1,73825602,2017-03-21T23:00:00-05:00,101

Open in new window

I can get it to output:
Value,EntityId,ServicePointChannelID
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101
1.0,73825602,101

Open in new window

Here is a sample of the XML file with two time periods:
<?xml version="1.0" encoding="us-ascii" standalone="yes"?>
<MeterReadingDocument xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ReadingStatusRefTable>
    <ReadingStatusRef Ref="1">
      <UnencodedStatus SourceValidation="NV">
        <StatusCodes>
          <Code>ESTIMATED</Code>
          <Code>ESTNUMREPLACE</Code>
          <Code>ESTUNSCALED</Code>
          <Code>GAP</Code>
          <Code>NV</Code>
          <Code>VEESET1PV</Code>
          <Code>VEESET2NV</Code>
          <Code>VEESET3NV</Code>
        </StatusCodes>
      </UnencodedStatus>
    </ReadingStatusRef>
	<!--DELETED LARGE AMOUNT OF ReadingStatusRef inumerations -->
  </ReadingStatusRefTable>
  <Header>
    <IEE_System Id="REDACTED" />
    <Creation_Datetime Datetime="2017-03-22T09:09:21Z" />
    <Timezone Id="UTC" />
    <Path FilePath="REDACTED" />
    <Export_Template Id="REDACTED" />
    <CorrelationID Id="" />
  </Header>
  <ImportExportParameters CreateResubmitFile="false" CreateReadingGroupForRegisterReads="true">
    <DataFormat ReadingTimestampType="MeterDefault" DSTTransitionType="REDACTED" />
  </ImportExportParameters>
  <Channels>
    <Channel StartDate="2017-03-21T00:00:00-05:00" EndDate="2017-03-22T00:00:00-05:00" TimeZone="CentralUS" IsRegister="false" MarketType="Electric" IntervalLength="60" NumberOfDials="-1" PulseMultiplier="1" PressureCompensationFactor="1" IsReadingDecoded="true" ReadingsInPulse="false">
      <ChannelID ServicePointChannelID="73825602:101" />
      <ContiguousIntervalSets>
        <ContiguousIntervalSet NumberOfReadings="24">
          <TimePeriod StartTime="2017-03-21T00:00:00-05:00" EndTime="2017-03-22T00:00:00-05:00" />
          <Readings>
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
            <Reading Value="1.0" StatusRef="1" />
          </Readings>
          <ExportRequest RequestID="702" EntityType="ServicePoint" EntityID="73825602" RequestSource="Scheduled" />
        </ContiguousIntervalSet>
      </ContiguousIntervalSets>
    </Channel>
	<Channel StartDate="2017-03-21T00:00:00-05:00" EndDate="2017-03-22T00:00:00-05:00" TimeZone="CentralUS" IsRegister="false" MarketType="Electric" IntervalLength="5" NumberOfDials="-1" PulseMultiplier="0.025" PressureCompensationFactor="1" IsReadingDecoded="true" ReadingsInPulse="false">
      <ChannelID ServicePointChannelID="76058806:404" />
      <ContiguousIntervalSets>
        <ContiguousIntervalSet NumberOfReadings="288">
          <TimePeriod StartTime="2017-03-21T00:00:00-05:00" EndTime="2017-03-22T00:00:00-05:00" />
          <Readings>
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
            <Reading Value="0.0" StatusRef="3" />
          </Readings>
          <ExportRequest RequestID="702" EntityType="ServicePoint" EntityID="76058806" RequestSource="Scheduled" />
        </ContiguousIntervalSet>
      </ContiguousIntervalSets>
    </Channel>
</Channels>
</MeterReadingDocument>

Open in new window

Here is my py code:
import xml.etree.ElementTree as ET
import csv

tree = ET.parse('dataexport.xml')
root = tree.getroot()[3]

with open('dataexport.csv', 'wb') as fout:
    writer = csv.writer(fout)
    row = ['Value', 'EntityId', 'ServicePointChannelID']
    writer.writerow(row)
    for channel in tree.iter('Channel'):
        for exportrequest in channel.iter('ExportRequest'):
            entityid = exportrequest.attrib.get('EntityID')
        for channelid in channel.iter ('ChannelID'):
            servicepointchannelid = channelid.attrib.get ('ServicePointChannelID')
            for meterread in channel.iter('Reading'):
                read = meterread.attrib.get('Value')
                row = [read, entityid, servicepointchannelid[9:]]
                print ', '.join(row)
                writer.writerow(row)

Open in new window

I am vexed.  Any help would be appreciated!
XMLPython
Avatar of undefined
Last Comment
pepr
ASKER CERTIFIED SOLUTION
Avatar of pepr
pepr
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Scott Abraham
Scott Abraham
Flag of United States of America image

ASKER

Thank you very much.  That worked great!
Avatar of pepr
pepr
I have fixed the way the EntityID is get (and the same for the ServicePointChannelID). You can use the .find method of the channel object to get the ExportRequest element (instead for the explicit iteration through the channel object). If you want to process only some EntityID channels, just test for the condition before processing. 

import xml.etree.ElementTree as ET
import csv
import datetime

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

with open('dataexport.csv', 'wb') as fout:
    writer = csv.writer(fout)
    row = ['Value', 'EntityId', 'DateTime', 'ServicePointChannelID']
    writer.writerow(row)
    for channel in tree.iter('Channel'):
        # Find the EntityID for the channel.
        exportrequest = channel.find('.//ExportRequest')    # from here (.) wherever deeper in the subtree (//)
        entityid = exportrequest.attrib.get('EntityID')
        
        # Process the channel data only if the EntityID shows it should be processed.
        if entityid != 'Bex2':
            print 'EntityID:', entityid, 'processing'

            # Get the data for generating time stamps.
            start_str = channel.attrib['StartDate']
            interval = int(channel.attrib['IntervalLength'])
            
            # The standard datetime module does not support parsing the zone UTC offset.
            # Ignore it when converting (use the dateutil extension module
            # https://pypi.python.org/pypi/python-dateutil if needed). Here the zone
            # suffix is extracted as a string and used later.
            dt = datetime.datetime.strptime(start_str[:19], '%Y-%m-%dT%H:%M:%S')     
            zone_str = start_str[19:]
            ##print start_str, interval, dt, zone_str
            
            # Convert the interval in minutes to timedelta object.
            delta = datetime.timedelta(minutes=interval)

            # Get the ServicePointChannelID.             
            channelid = channel.find('ChannelID')   # this is simpler, no need for XPath
            servicepointchannelid = channelid.attrib.get('ServicePointChannelID')
            
            # Process the readings for the the channel.
            for meterread in channel.iter('Reading'):
                read = meterread.attrib.get('Value')
                row = [read, entityid, dt.isoformat() + zone_str, servicepointchannelid[9:]]
                dt += delta         # calculate the next datetime
                ##print ', '.join(row)
                writer.writerow(row)
        else:                    
            print 'EntityID:', entityid, 'ignored'

Open in new window

XML
XML

Extensible Markup Language (XML) refers to the encoding of documents such that they can be read by both machines and humans. XML documents use tags to show the beginning and end of a set of data. XML is used extensively on websites to show volumes of data, and is the default for a number of office productivity suites. This topic includes discussions of XML-related technologies, such as XQuery (the XML Query language), XPath (the XML Path language), XSLT (eXtensible Stylesheet Language Transformations), XLink (the XML Linking language) and XPointer (the XML Pointer language).

22K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent