How to convert this XML file to CSV using python?

Question

I have hereby the attached the xml file I am trying to convert to csv

<?xml version="1.0"?>
<response>
  <data>
    <shops>
      <shop id="204019">
        <name>Bannockburn</name>
        <status>Open</status>
        <company id="25">Franchise</company>
        <shopAttributes>
          <shopAttribute attrName="shop_OPEN_DATE">2008-07-16</shopAttribute>
          <shopAttribute attrName="CLOSE_DATE"/>
          <shopAttribute attrName="shop_DISTRIBUTION_CTR_GENERAL" startDate="2019-03-19">90</shopAttribute>
          <shopAttribute attrName="shop_DISTRIBUTION_CTR_GENERAL" startDate="1900-01-01" endDate="2019-03-18"/>
        </shopAttributes>
        <addresses>
          <address type="PUBLIC">
            <addressLine1>1211 Half Day Road</addressLine1>
            <addressLine2></addressLine2>
            <city>Bannockburn</city>
            <stateProvince>IL</stateProvince>
            <postalCode>60015</postalCode>
            <country>USA</country>
            <latitude>42.199461</latitude>
            <longitude>-87.860582</longitude>
          </address>
        </addresses>
      </shop>
      <shop id="204020">
        <name>Plainfield - North Plainfield</name>
        <status>Open</status>
        <company id="25">Franchise</company>
        <shopAttributes>
          <shopAttribute attrName="shop_OPEN_DATE">2007-05-18</shopAttribute>
          <shopAttribute attrName="CLOSE_DATE"/>
          <shopAttribute attrName="shop_DISTRIBUTION_CTR_GENERAL" startDate="2019-03-19">90</shopAttribute>
          <shopAttribute attrName="shop_DISTRIBUTION_CTR_GENERAL" startDate="1900-01-01" endDate="2019-03-18"/>
        </shopAttributes>
        <addresses>
          <address type="PUBLIC">
            <addressLine1>12632 IL Route 59</addressLine1>
            <addressLine2>Suite #102</addressLine2>
            <city>Plainfield</city>
            <stateProvince>IL</stateProvince>
            <postalCode>60585</postalCode>
            <country>USA</country>
            <latitude>41.653125</latitude>
            <longitude>-88.204527</longitude>
          </address>
        </addresses>
      </shop>
</shops>
</data>
</response>

this is the xml file I want to convert to csv, can someone help me how to do it in python? Below is the code that I tried to use but I haven't really understood how to do it, saw some examples but it isn't very clear

from xml.etree import ElementTree

tree = ElementTree.parse('Store.xml')
root = tree.getroot()

for att in root:
    first = att.find('shops').text
    print('{}'.format(first))

but i was getting None here.

Google: i.e. have a look here: blog.appliedinformaticsinc.com/… — Siebe Jongebloed
– Siebe Jongebloed, Commented May 10, 2021 at 5:57
@SiebeJongebloed I have seen this, and when I tried to use this. I got an empty csv so I thought there must be something i am missing. — LearnerJS
– LearnerJS, Commented May 10, 2021 at 6:43
What information are you trying to get? Shops has no text, so it would only print blank. — Superspork
– Superspork, Commented May 10, 2021 at 7:29

Tim Jim · Accepted Answer · 2021-05-10 07:41:57Z

2

Not a complete solution but in answer to why you were getting None, it's because your shops are actually one level deeper, under the data tag.

This bit of code might give you an idea of how to access the underlying attributes, which you can collect into a list or other container to build your CSV.

from xml.etree import ElementTree

tree = ElementTree.parse('Store.xml')
root = tree.getroot()
data = root.find('data')

for shops in data:
    for shop in shops:
        name = shop.find('name').text
        sid = shop.attrib
        status = shop.find('status').text
        attrs = shop.find('shopAttributes')
        open_date = attrs.find(".//shopAttribute/[@attrName='shop_OPEN_DATE']").text
        print(f"Name: {name}, ID: {sid}, Status: {status}, open: {open_date}")

The open_date is an example of how to use XPath to access attributes. The code returns:

Name: Bannockburn, ID: {'id': '204019'}, Status: Open, open: 2008-07-16
Name: Plainfield - North Plainfield, ID: {'id': '204020'}, Status: Open, open: 2007-05-18

answered May 10, 2021 at 7:41

Tim Jim

6707 silver badges20 bronze badges

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

2 Comments

LearnerJS Over a year ago

The explanation as well as the links attached were really helpful. Thanks a lot!

Tim Jim Over a year ago

No probs! I remember struggling with element tree way back when.. it was a good review! There might be better ways to do it if you want more complex behaviour though. =)

Superspork · Accepted Answer · 2021-05-10 07:34:14Z

0

Shops has no text, so it’ll print nothing. You need to get down to the level you want

for att in root.findall('./data/shops/shop’):
    first = att.find('name')
    print('{}'.format(first.text))

Gives

Bannockburn
Plainfield - North Plainfield

Here is a good resource for ElementTree: https://docs.python.org/3/library/xml.etree.elementtree.html

answered May 10, 2021 at 7:34

Superspork

4133 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to convert this XML file to CSV using python?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related