XML to Excel/CSV using Python

Question

I have a file that contains xml data, but file use some custom extension. Also there are some blank spaces (empty cells). This is the file:

<?xml version="1.0" encoding="utf-8"?>
<XMLData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PinList>
    <Pin>
      <Comment />
      <LedAdr>-1</LedAdr>
      <PinName />
      <System>1</System>
      <Type>DataType2</Type>
    </Pin>
    <Pin>
      <Comment />
      <LedAdr>-1</LedAdr>
      <PinName>Data1</PinName>
      <System>2</System>
      <Type>DataType1</Type>
    </Pin>
    <Pin>
      <Comment />
      <LedAdr>-1</LedAdr>
      <PinName>Data2</PinName>
      <System>3</System>
      <Type>DataType3</Type>
    </Pin>
    <Pin>
      <Comment />
      <LedAdr>-1</LedAdr>
      <PinName>Data3</PinName>
      <System>4</System>
      <Type>DataType2</Type>
    </Pin>
  </PinList>
  <CountFormat>c1a1</CountFormat>
  <Created>2019-09-11T13:46:49.7055676+02:00</Created>
  <Created_by>asd</Created_by>
  <Info>PinSystemNameTypeDecimal</Info>
  <AdapterPintableEnabled>false</AdapterPintableEnabled>
  <LastChanged>2020-05-04T20:13:23.4025733+02:00</LastChanged>
  <LastChanged_by>asd</LastChanged_by>
  <PinRange>5120</PinRange>
  <Version />
</XMLData>

I want to achieve the following using Python:

    A   |   B   |      C      |      D      |   E   |
   No    System  PinName       Type          Comment
   1     1.1     Data1         DataType1
   2     1.2     Data2         DataType2
   3     1.3     Data3         DataType3

With a final touch that System starts with 1.1 but when it reach 1.64 next to be 2.1 instead of 1.65.

My Code so far:

import xml.etree.ElementTree as ET
import csv

tree = ET.parse("SP.pintable")
root = tree.getroot()

f = open('sp.csv', 'w')

csvwriter = csv.writer(f)

count = 0
head = ["No", 'System','PinName','Type','Comment']

csvwriter.writerow(head)
for PinList in root.findall('PinList'):
    row = []
    No = 1
    row.append(No)
    System = PinList.find('Pin').find('System').text
    row.append(System)
    PinName = PinList.find('Pin').find('PinName').text
    row.append(PinName)
    Type = PinList.find('Pin').find('Type').text
    row.append(Type)
    Comment = PinList.find('Pin').find('Comment').text
    row.append(Comment)

    print(row)

    csvwriter.writerow(row)
f.close()

I have a problem that it works only for 1st row of xml file. Also I have added value for No = 1 just to write it in csv, I have no idea how to achieve this counter that value of "System" is going from 1-10000 but to be written as 1.1 till 1.64 after that 2.1 instead of 1.65

Thanks a lot.

If ur end goal is to transform xml to csv, why not use ```xmltodict``` package convert to dict https://stackoverflow.com/questions/36253405/python-get-value-with-xmltodict, and dump to file from there. — sushanth, May 25 '20 at 06:24

sushanth · Answer 1 · 2020-05-25T08:05:25.743

0

for above code to work, get the parent tag PinList & findall the Child Pin under it.

import csv

head = ["No", 'System','PinName','Type','Comment']
fields = ["Comment", "LedAdr", "PinName", "System", "Type"]

with open("test.csv", "w") as f:
    csv_writer = csv.writer(f)

    csv_writer.writerow(head)
    for pin in root.find('PinList').findall('Pin'):
        row = []
        for field in fields:
            row.append(pin.find(field).text)

        csv_writer.writerow(row)

        # above loop can be re-written using list comprehension.
        # csv_writer.writerow([pin.find(field).text for field in fields])

Note : Please follow python naming convention.
What is the naming convention in Python for variable and function names?

edited May 25 '20 at 08:05

answered May 25 '20 at 06:37

sushanth

8,275
3
17
28

When I try it like this, I get: `line 19, in ` `System = pin.find('Pin').find('System').text` `AttributeError: 'NoneType' object has no attribute 'find'` – Directx995 May 25 '20 at 07:40
no u should not do that, because pin does not have a chile by name ```Pin``` to access child element's directly call with the key like this ```pin.find('System')``` – sushanth May 25 '20 at 07:58

XML to Excel/CSV using Python

1 Answers1