Python reading large xml file and save to csv file

Question

I have a large xml file like below structure

<?xml version="1.0"?>
  <products xmlns="http://data-vocabulary.org/product/">
   <channel>
   <title>Online Store</title>
   <link>https://www.clienturl.com/</link>   
   <product>
   <identifier>DI035AT12JNR</identifier>
   <quantity>1</quantity>
   <fn>Button Fastening Mid Rise Boyfriend Jeans</fn>
   <description>Button Fastening Mid Rise Boyfriend Jeans</description>
  <category>women-clothing &gt; women-clothing-jeans &gt; women-clothing-jeans-straight_jeans</category>
  <currency>SAR</currency>
  <photo>http://clienturl/product/78/6014/v1/1-zoom.jpg</photo>
  <brand>Diesel</brand>
  <url>https://eclient-product-url.html</url>
  <price>1450</price>
  <google_product_category>Apparel &amp; Accessories &gt; Clothing &gt; Pants</google_product_category>
</product>
<product>
  <identifier>DI035AT12JNR</identifier>
  <quantity>1</quantity>
  <fn>Button Fastening Mid Rise Boyfriend Jeans</fn>
  <description>Button Fastening Mid Rise Boyfriend Jeans</description>
  <category>women-clothing &gt; women-clothing-jeans &gt; women-clothing-jeans-straight_jeans</category>
  <currency>SAR</currency>
  <photo>http://clienturl/product/78/6014/v1/1-zoom.jpg</photo>
  <brand>Diesel</brand>
  <url>https://eclient-product-url.html</url>
  <price>1450</price>
  <google_product_category>Apparel &amp; Accessories &gt; Clothing &gt; Pants</google_product_category>
  </product>
  </channel>
  </products>

and here is the python code below

   import codecs
   import xml.etree.ElementTree as etree
   xmlfile = 'en-sa.xml'

   def iterate_xml(xmlfile):
   doc = etree.iterparse(xmlfile, events=('start', 'end'))
   _, root = next(doc)
   start_tag = None
   for event, element in doc:
        if event == 'start' and start_tag is None:
            start_tag = element.tag
        if event == 'end' and element.tag == start_tag:
            yield element
            start_tag = None
            root.clear()

   count=0
   for element in iterate_xml(xmlfile):
       for ele in element:
           print ele
       count=count+1
       if count == 5:
           break

which print output like below

<Element '{http://data-vocabulary.org/product/}title' at 0x7efd046f7a10>
<Element '{http://data-vocabulary.org/product/}link' at 0x7efd046f7ad0>
<Element '{http://data-vocabulary.org/product/}product' at 0x7efd046f7d10>
<Element '{http://data-vocabulary.org/product/}product' at 0x7efd04703050>

I want make this xml into csv file like having below cloumns headers

identifier:quantity:fn:description:category:currency:photo:brand:url:price:google_product_category

but didn't get any ideas how to proceed, can someone help me here \ Thanks in advance

Open a csv file, write to it, adding the numbers of columns you need. Then you append the data you collect from xml to the columns. — Xion
– Xion, Commented Feb 6, 2019 at 19:18
It will be helpful if you provide some code on that, actually I want print the values and can append to a file later — Chethu
– Chethu, Commented Feb 6, 2019 at 19:23
Change yield element to yield {element.tag:element.text} — stovfl
– stovfl, Commented Feb 6, 2019 at 19:51

Alex Albracht · Accepted Answer · 2019-02-06 20:06:08Z

Would suggest using lxml.etree to extract all of the text for this instance it returns a list of strings containing all of the text and tails.

import lxml.etree
text = """<?xml version="1.0"?>
  <products xmlns="http://data-vocabulary.org/product/">
   <channel>
   <title>Online Store</title>
   <link>https://www.clienturl.com/</link>   
   <product>
   <identifier>DI035AT12JNR</identifier>
   <quantity>1</quantity>
   <fn>Button Fastening Mid Rise Boyfriend Jeans</fn>
   <description>Button Fastening Mid Rise Boyfriend Jeans</description>
  <category>women-clothing &gt; women-clothing-jeans &gt; women-clothing-jeans-straight_jeans</category>
  <currency>SAR</currency>
  <photo>http://clienturl/product/78/6014/v1/1-zoom.jpg</photo>
  <brand>Diesel</brand>
  <url>https://eclient-product-url.html</url>
  <price>1450</price>
  <google_product_category>Apparel &amp; Accessories &gt; Clothing &gt; Pants</google_product_category>
</product>
<product>
  <identifier>DI035AT12JNR</identifier>
  <quantity>1</quantity>
  <fn>Button Fastening Mid Rise Boyfriend Jeans</fn>
  <description>Button Fastening Mid Rise Boyfriend Jeans</description>
  <category>women-clothing &gt; women-clothing-jeans &gt; women-clothing-jeans-straight_jeans</category>
  <currency>SAR</currency>
  <photo>http://clienturl/product/78/6014/v1/1-zoom.jpg</photo>
  <brand>Diesel</brand>
  <url>https://eclient-product-url.html</url>
  <price>1450</price>
  <google_product_category>Apparel &amp; Accessories &gt; Clothing &gt; Pants</google_product_category>
  </product>
  </channel>
  </products>""".encode('utf-8')# the library wants bytes so we encode
#  Not needed if reading from a file
doc = lxml.etree.fromstring(text)
print(doc.xpath('//text()'))

Will output all of the text from the XML in a list of strings

['\n   ', '\n   ', 'Online Store', '\n   ', 'https://www.clienturl.com/', '   \n   ', '\n   ', 'DI035AT12JNR', '\n   ', '1', '\n   ', 'Button Fastening Mid Rise Boyfriend Jeans', '\n   ', 'Button Fastening Mid Rise Boyfriend Jeans', '\n  ', 'women-clothing > women-clothing-jeans > women-clothing-jeans-straight_jeans', '\n  ', 'SAR', '\n  ', 'http://clienturl/product/78/6014/v1/1-zoom.jpg', '\n  ', 'Diesel', '\n  ', 'https://eclient-product-url.html', '\n  ', '1450', '\n  ', 'Apparel & Accessories > Clothing > Pants', '\n', '\n', '\n  ', 'DI035AT12JNR', '\n  ', '1', '\n  ', 'Button Fastening Mid Rise Boyfriend Jeans', '\n  ', 'Button Fastening Mid Rise Boyfriend Jeans', '\n  ', 'women-clothing > women-clothing-jeans > women-clothing-jeans-straight_jeans', '\n  ', 'SAR', '\n  ', 'http://clienturl/product/78/6014/v1/1-zoom.jpg', '\n  ', 'Diesel', '\n  ', 'https://eclient-product-url.html', '\n  ', '1450', '\n  ', 'Apparel & Accessories > Clothing > Pants', '\n  ', '\n  ', '\n  ']

Can't guarantee this to work when iterating through the entirety of the XML code because you only gave one example. But if the number of categories in the XML is standard you could iterate by product and select the desired indices to add to another list. Once you have a lists containing (identifier:quantity:fn:description:category:currency:photo:brand:url:price:google_product_category) it should be easy enough to create a pandas dataframe using pandas.DataFrame.append and export to a csv df.to_csv(r'Path where you want to store the exported CSV file\File Name.csv')

Collectives™ on Stack Overflow

Python reading large xml file and save to csv file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related