create a csv file which is Excel friendly format.
import xml.etree.ElementTree as ET
from os import listdir
xml_lst = [f for f in listdir() if f.startswith('xml')]
fields = ['RecordID','I_25Hz_1s','I_75Hz_2s'] # TODO - add rest of the fields
with open('out.csv','w') as f:
f.write(','.join(fields) + '\n')
for xml in xml_lst:
root = ET.parse(xml)
values = [root.find(f'.//{f}').text for f in fields]
f.write(','.join(values) + '\n')
output
RecordID,I_25Hz_1s,I_75Hz_2s
Madird01,56.40,0.36
London01,56.40,0.36
python - How to convert an XML file to nice pandas dataframe? - Stack Overflow
How to parse XML into an excel sheet?
Parsing XML into a Pandas dataframe
What’s the easiest way to convert .xml file to .xlsx?
Parsing xml isn’t something I’ve tried before, but I would probably start by trying this guy’s approach: https://link.medium.com/f6TTwmTSo2
More on reddit.comHow to use the
Convert XML to Pandas DataFrame Online for free?
What is Pandas DataFrame format?
What is XML format?
Videos
create a csv file which is Excel friendly format.
import xml.etree.ElementTree as ET
from os import listdir
xml_lst = [f for f in listdir() if f.startswith('xml')]
fields = ['RecordID','I_25Hz_1s','I_75Hz_2s'] # TODO - add rest of the fields
with open('out.csv','w') as f:
f.write(','.join(fields) + '\n')
for xml in xml_lst:
root = ET.parse(xml)
values = [root.find(f'.//{f}').text for f in fields]
f.write(','.join(values) + '\n')
output
RecordID,I_25Hz_1s,I_75Hz_2s
Madird01,56.40,0.36
London01,56.40,0.36
When you need to iterate over files in folder with similar names one of the ways could be make a pattern and use glob. To make sure that returned path is file you can use isfile().
Regarding XML, I see that basically you need to write values of every terminal tag in column with name of this tag. As you have various files you can create tag-value dictionaries from each file and store them into ChainMap. After all files processed you can use DictWriter to write all data into final csv file.
This method is much more safe and flexible then use static column names. Firstly program will collect all possible tag(column) names from all files, so in case if XML doesn't have such a tag or have some extra tags it won't throw an exception and all data will be saved.
Code:
import xml.etree.ElementTree as ET
from glob import iglob
from os.path import isfile, join
from csv import DictWriter
from collections import ChainMap
xml_root = r"C:\data\Desktop\Blue\XML-files"
pattern = "xmlfile_*"
data = ChainMap()
for filename in iglob(join(xml_root, pattern)):
if isfile(filename):
tree = ET.parse(filename)
root = tree.getroot()
temp = {node.tag: node.text for node in root.iter() if not node}
data = data.new_child(temp)
with open(join(xml_root, "data.csv"), "w", newline="") as f:
writer = DictWriter(f, data)
writer.writeheader()
writer.writerows(data.maps[:-1]) # last is empty dict
Upd. If you want to use xlsx format instead of csv you have to use third-party library (e.g. openpyxl). Example of usage:
from openpyxl import Workbook
...
wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(list(data)) # write header
for row in data.maps[:-1]:
ws.append([row.get(key, "") for key in data])
wb.save(join(xml_root, "data.xlsx"))
You can easily use xml (from the Python standard library) to convert to a pandas.DataFrame. Here's what I would do (when reading from a file replace xml_data with the name of your file or file object):
import pandas as pd
import xml.etree.ElementTree as ET
import io
def iter_docs(author):
author_attr = author.attrib
for doc in author.iter('document'):
doc_dict = author_attr.copy()
doc_dict.update(doc.attrib)
doc_dict['data'] = doc.text
yield doc_dict
xml_data = io.StringIO(u'''YOUR XML STRING HERE''')
etree = ET.parse(xml_data) #create an ElementTree object
doc_df = pd.DataFrame(list(iter_docs(etree.getroot())))
If there are multiple authors in your original document or the root of your XML is not an author, then I would add the following generator:
def iter_author(etree):
for author in etree.iter('author'):
for row in iter_docs(author):
yield row
and change doc_df = pd.DataFrame(list(iter_docs(etree.getroot()))) to doc_df = pd.DataFrame(list(iter_author(etree)))
Have a look at the ElementTree tutorial provided in the xml library documentation.
As of v1.3, you can simply use:
pandas.read_xml(path_or_file)
» pip install xml2xlsx
Bare with me, as I'm a novice with python, but basically, I am trying to take an XML file, and plop it into an existing excel workbook in a specific sheet. I know I have done this successfully before, but cannot find the file where I did, nor can I remember how I did.
When I do it manually, the process is pretty straight forward - download the XML file, open it with excel, copy and paste as text into the sheet. Just hoping someone could help me get started here. Thanks so much for your time.
To be more specific this is the layout of the XML file:
<products>
<product active="1" on_sale="0" discountable="1">
<sku>GG1234</sku>
<name><![CDATA[ Product Name Here ]]></name>
<description><![CDATA[Product Description Here ]]></description>
<keywords></keywords>
<price>8.9</price>
<stock_quantity>220</stock_quantity>
<reorder_quantity>0</reorder_quantity>
<height>4.25</height>
<length>1.25</length>
<diameter>2.5</diameter>
<weight>0.53</weight>
<color></color>
<material>Material Here/material>
<barcode>0000000000</barcode>
<release_date>2010-02-19</release_date>
<images>
<image>/path/path.jpg</image>
<image>/path/path.jpg</image>
<image>/path/path.jpg</image>
<image>/path/path.jpg</image>
</images>
<categories>
<category code="518" video="0" parent="0">Category 1</category>
<category code="525" video="0" parent="528">Category 2</category>
<category code="138" video="0" parent="0">Category 3</category>
<category code="552" video="0" parent="528">Category 4</category>
</categories>
<manufacturer code="AC" video="0">Manufact</manufacturer>
<type code="CL" video="0">Product Type</type>
</product> . . . . .
<products>
What I need is for the follow values to populate the top row as the header of the excel file:
active
on_sale
disctountable
sku
name
description
keywords
price
stock_quantity
reorder_quantity
height
length
diameter
weight
color
material
barcode
release_date
image
category
manufacturer
code2
video3
type
code4
video5
And then their respective values to populate the cells going downward in the columns.
Hope that makes sense