CODEWITHSUNDEEP
pythonxmlexport-to-csv
Chethu
Chethu

Reputation: 555

Python reading large xml file and save to csv file

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

Upvotes: 1

Views: 821

Answers (1)

Alex Albracht
Alex Albracht

Reputation: 339

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')

Upvotes: 1

Related Questions