I am looking to combine 20 XML files into a single Excel file, with each XML file as an individual worksheet.
Although the structure of the XML files is consistent, the values vary.
You can see how the XML file appears here. Only the X and Y values are required.
My plan involves importing the necessary data (X and Y values) into Python first to create a table, which will then be imported into an Excel file.
Initially, I started by importing one XML file and creating a table in Python. Below is the code I wrote:
import xml.etree.ElementTree as ET
import pandas as pd
file_path = file_path
root = ET.parse(file_path).getroot()
for Point in root.findall('Point'):
X = float(Point.find('X').text)
Y = float(Point.find('Y').text)
kraft_all = [Y]
data = {'Weg/mm': [X], 'Kraft/N': [Y],'Max. Kraft/N':max(kraft_all)}
df = pd.DataFrame.from_dict(data)
print(df)
The output I obtained looks like this:
I have a couple of questions:
- Why does each value have its own column name? How can I display the column names only in the first row?
- How can I extract the maximum value from the Y values and place it in the first 'cell' of the third column 'Max. Kraft/N'?
Prior to this, I successfully imported multiple CSV files into an Excel file using pd.read_csv and df.to_excel. So once I address these initial questions, I aim to work on handling multiple files independently. However, any advice or suggestions would be highly appreciated :)
Thank you for your time!
Update_2023.10.09
Following guidance from @Edo Akse, I modified my code and added some additional lines to import the dataframe into an Excel file. It worked smoothly for one XML file :). Here's the updated code:
import xml.etree.ElementTree as ET
import pandas as pd
file_path = r'C:\Users\xli\OneDrive - TFI Aachen GmbH\Excel_Beige und Weiß_Tru\tru Beige\tru beige-1.xml'
root = ET.parse(file_path).getroot()
as_list = []
for Point in root.findall("Point"):
X = float(Point.find("X").text)
Y = float(Point.find("Y").text)
line = {"Weg/mm": X, "Kraft/N": Y}
as_list.append(line)
df = pd.DataFrame.from_dict(as_list)
print(df)
excel_path = r'C:\Users\xli\OneDrive - TFI Aachen GmbH\Excel_Beige und Weiß_Tru\beige.xlsx'
writer = pd.ExcelWriter(excel_path,engine='openpyxl')
df.to_excel(excel_writer=writer,sheet_name='tru beige-1')
writer.close()
Now, my objective is to
- import multiple XML files from a folder into distinct dataframes
- import these dataframes into separate sheets within a single Excel file
With reference to @Hermann12, I constructed the following code:
import glob
import pandas as pd
import os
from pathlib import Path
def load_xml(files):
column = ["Weg[mm]","Kraft[N]"]
df1 = pd.concat([pd.read_xml(file, names=column) for file in files])
return df1
excel_path = r'C:\Users\xli\OneDrive - TFI Aachen GmbH\Excel_Beige und Weiß_Tru\beige.xlsx'
writer = pd.ExcelWriter(excel_path,engine='openpyxl')
num=1
for root,dirs,files in os.walk(r"C:\Users\xli\OneDrive - TFI Aachen GmbH\Excel_Beige und Weiß_Tru\tru Beige"):
print(root)
print(dirs)
print(files)
for file in files:
xml_files = Path(r"C:\Users\xli\OneDrive - TFI Aachen GmbH\Excel_Beige und Weiß_Tru\tru Beige").glob('*.xml')
df = load_xml(xml_files)
df.to_excel(excel_writer=writer,sheet_name=file)
writer.close()
An error "ValueError: names does not match length of child elements in xpath" appeared.
If anyone could review my code and guide me on rectifying the errors, that would be greatly appreciated.
Many thanks for your time!