how to convert xml file to csv in python

Question

I am a beginner for python. even if had a lot of example of xml to csv, but I still don't get this key point, when I do something. the xml file as below:

<?xml version="1.0" encoding="ISO-8859-1"?>
<spec:BACKUPCFG xmlns:spec="http://" >
    <spec:fileHeader fileFormatVersion="1.2.0" nenrmversion="BTS3900DATAV100R012C10SPC100" neversion="BTS3900 V100R012C10SPC100" syntype="synall" synlabel="00000519320218576688" producttype="117"/>
    <spec:compatibleNrmVersionList>
        <spec:compatibleNrmVersion>B100R012C10SPC100</spec:compatibleNrmVersion>
    </spec:compatibleNrmVersionList>
<spec:syndata FunctionType="NODE" Id="NODENAME=Node" productversion="BTS3900 V100R012C10SPC100" nermversion="HERTBBUV500R007C10SPC100" objId="-1">
    <spec:compatibleNrmVersionList>
        <spec:compatibleNrmVersion>HERTBBUV500R007</spec:compatibleNrmVersion>
    </spec:compatibleNrmVersionList>
    <class>
        <Cell>
            <attributes>
                <LocalCellId>60</LocalCellId>
                <CellName>NJ0003P_9NB01_S01</CellName>
                <CsgInd>0</CsgInd><!--False-->
                <UlCyclicPrefix>0</UlCyclicPrefix><!--Normal-->
                <DlCyclicPrefix>0</DlCyclicPrefix><!--Normal-->
                <FreqBand>8</FreqBand>
                <UlEarfcnCfgInd>0</UlEarfcnCfgInd><!--Not configure-->
                <DlEarfcn>3750</DlEarfcn>
                <UlBandWidth>2</UlBandWidth><!--5M-->
                <DlBandWidth>2</DlBandWidth><!--5M-->
                <CellId>60</CellId>
                <PhyCellId>128</PhyCellId>
            </attributes>
        </Cell>
        <Cell>
            <attributes>
                <LocalCellId>61</LocalCellId>
                <CellName>NJ0003P_9NB01_S02</CellName>
                <CsgInd>0</CsgInd><!--False-->
                <UlCyclicPrefix>0</UlCyclicPrefix><!--Normal-->
                <DlCyclicPrefix>0</DlCyclicPrefix><!--Normal-->
                <FreqBand>8</FreqBand>
                <UlEarfcnCfgInd>0</UlEarfcnCfgInd><!--Not configure-->
                <DlEarfcn>3750</DlEarfcn>
                <UlBandWidth>2</UlBandWidth><!--5M-->
                <DlBandWidth>2</DlBandWidth><!--5M-->
                <CellId>61</CellId>
                <PhyCellId>192</PhyCellId>
            </attributes>
        </Cell>
        <Cell>
            <attributes>
                <LocalCellId>62</LocalCellId>
                <CellName>CBR0003P_9NB01_S03</CellName>
                <CsgInd>0</CsgInd><!--False-->
                <UlCyclicPrefix>0</UlCyclicPrefix><!--Normal-->
                <DlCyclicPrefix>0</DlCyclicPrefix><!--Normal-->
                <FreqBand>8</FreqBand>
                <UlEarfcnCfgInd>0</UlEarfcnCfgInd><!--Not configure-->
                <DlEarfcn>3750</DlEarfcn>
                <UlBandWidth>2</UlBandWidth><!--5M-->
                <DlBandWidth>2</DlBandWidth><!--5M-->
                <CellId>62</CellId>
                <PhyCellId>194</PhyCellId>
            </attributes>
        </Cell>
        <Cell>
            <attributes>
                <LocalCellId>63</LocalCellId>
                <CellName>NJ0003P_9NB01_S04</CellName>
                <CsgInd>0</CsgInd><!--False-->
                <UlCyclicPrefix>0</UlCyclicPrefix><!--Normal-->
                <DlCyclicPrefix>0</DlCyclicPrefix><!--Normal-->
                <FreqBand>8</FreqBand>
                <UlEarfcnCfgInd>0</UlEarfcnCfgInd><!--Not configure-->
                <DlEarfcn>3750</DlEarfcn>
                <UlBandWidth>2</UlBandWidth><!--5M-->
                <DlBandWidth>2</DlBandWidth><!--5M-->
                <CellId>63</CellId>
                <PhyCellId>424</PhyCellId>
            </attributes>
        </Cell>
    </class>
    <class>
        <CellDrxSpecialPara>
            <attributes>
                <LocalCellId>60</LocalCellId>
                <CellDrxSpecialParaValid>0</CellDrxSpecialParaValid><!--FALSE-->
                <LongDrxCycleSpecial>0</LongDrxCycleSpecial><!--10 subframes-->
                <OnDurationTimerSpecial>4</OnDurationTimerSpecial><!--5 PDCCH subframes-->
            </attributes>
        </CellDrxSpecialPara>
        <CellDrxSpecialPara>
            <attributes>
                <LocalCellId>61</LocalCellId>
                <CellDrxSpecialParaValid>0</CellDrxSpecialParaValid><!--FALSE-->
                <LongDrxCycleSpecial>0</LongDrxCycleSpecial><!--10 subframes-->
                <OnDurationTimerSpecial>4</OnDurationTimerSpecial><!--5 PDCCH subframes-->
            </attributes>
        </CellDrxSpecialPara>
        <CellDrxSpecialPara>
            <attributes>
                <LocalCellId>64</LocalCellId>
                <CellDrxSpecialParaValid>0</CellDrxSpecialParaValid><!--FALSE-->
                <LongDrxCycleSpecial>0</LongDrxCycleSpecial><!--10 subframes-->
                <OnDurationTimerSpecial>4</OnDurationTimerSpecial><!--5 PDCCH subframes-->
            </attributes>
        </CellDrxSpecialPara>
        <CellDrxSpecialPara>
            <attributes>
                <LocalCellId>65</LocalCellId>
                <CellDrxSpecialParaValid>0</CellDrxSpecialParaValid><!--FALSE-->
                <LongDrxCycleSpecial>0</LongDrxCycleSpecial><!--10 subframes-->
                <OnDurationTimerSpecial>4</OnDurationTimerSpecial><!--5 PDCCH subframes-->
            </attributes>
        </CellDrxSpecialPara>
    </class>
</spec:syndata>
    <spec:fileFooter label="00000519320218576688" ExportResult="Success" dateTime="2017-05-15T08:21:09">
        <spec:moclistincluded></spec:moclistincluded>
    </spec:fileFooter>
</spec:BACKUPCFG>
<!--CRC=0x062E67D4BBFE31B20E6886224D77AA640AEE7313098F53CE7B2C831CB4905DA6-->

import xml.etree.ElementTree as ET
import csv

tree = ET.parse('xml-csv/CFGDATA.XML')

root = tree.getroot()

with open('config.csv', 'w', newline = '') as r:
    writer = csv.writer(r)
    writer.writerow(['LocalCellId','CellName','FreqBand','DlEarfcn','CellId','PhyCellId'])

    for mr in tree.iter('Cell'):
        #for mr in cell.iter('Cell'):
        LocalCellId = mr.attrib.get('LocalCellId')
        CellName = mr.attrib.get('CellName')
        FreqBand = mr.attrib.get('FreqBand')
        DlEarfcn = mr.attrib.get('DlEarfcn')
        CellId = mr.attrib.get('CellId')
        PhyCellId = mr.attrib.get('PhyCellId')
        writer.writerow([LocalCellId,CellName,FreqBand,DlEarfcn,CellId,PhyCellId])

I want to get csv data from xml just like below table, but I can't.

enter image description here

Do you get an error, or your output is different as you would expect? Please describe what are you getting now. — nio
– nio, Commented Aug 14, 2018 at 14:19
What happens? Do you get any output? Is it correct? If not, how is it wrong? — user9455968
– user9455968, Commented Aug 14, 2018 at 14:19
sorry, run this code got an error as below:--------------------------------------------------------------------------- PermissionError Traceback (most recent call last) <ipython-input-38-b531667522f3> in <module>() 1 root = tree.getroot() 2 ----> 3 with open('config.csv', 'w', newline = '') as r: 4 writer = csv.writer(r) 5 writer.writerow(['LocalCellId','CellName','FreqBand','DlEarfcn','CellId','PhyCellId']) PermissionError: [Errno 13] Permission denied: 'config.csv' — Well Joe
– Well Joe, Commented Aug 15, 2018 at 4:46

lojza · Accepted Answer · 2018-08-15 08:44:46Z

1

Using lxml:

from lxml import etree

xmlparser = etree.XMLParser()

with open('xml-csv/CFGDATA.XML') as xmlfile:
    tree = etree.parse(xmlfile, xmlparser)
cells = tree.xpath("//Cell")  

for cell in cells:
    LocalCellId = cell.find('attributes/LocalCellId').text
    CellName = cell.find('attributes/CellName').text
    FreqBand = cell.find('attributes/FreqBand').text
    DlEarfcn = cell.find('attributes/DlEarfcn').text
    CellId = cell.find('attributes/CellId').text
    PhyCellId = cell.find('attributes/PhyCellId').text
    print(LocalCellId,CellName,FreqBand,DlEarfcn,CellId,PhyCellId,sep=',')

Ouput:

60,NJ0003P_9NB01_S01,8,3750,60,128
61,NJ0003P_9NB01_S02,8,3750,61,192
62,CBR0003P_9NB01_S03,8,3750,62,194
63,NJ0003P_9NB01_S04,8,3750,63,424

edited Aug 15, 2018 at 8:44

user9455968

answered Aug 15, 2018 at 8:37

lojza

1,9412 gold badges13 silver badges23 bronze badges

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

1 Comment

lojza Over a year ago

:-) If the answer is working please accept it (tick under voting score). Thank you

Collectives™ on Stack Overflow

how to convert xml file to csv in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related