How to convert this XML file to CSV using python?

user_13024743 0
Viewed 73

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.

csv elementtree xpath xml python
2 Answers

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
user_79858550

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

user_78865290
Related