How To Import An Xml File Into An Excel Xls File Template Using Python?

I have an Excel template file which has columns as defined within an XML. Manually I can right click over the template then XML > import, and select the XML file, and finally sa

Solution 1:

Have you tried using BeautifulSoup and Pandas? Note that the parser I use in the following script requires you to have lxml installed already. If you don't have it just pip install lxml.

import pandas as pd
from bs4 import BeautifulSoup

file = open("file.xml", 'r')
soup = BeautifulSoup(file, 'lxml')
df = pd.DataFrame({'ids': [x.text for x in soup.find_all('id')]})
df.to_excel('data.xls')

While you will have to figure out how you want to parse your file, that will give you the tools that you need. If you need more information about how to parse the file, try visiting the BeautifulSoup documentation. Using this code you can loop through all the files that you are interested in and parsing them into dataframes, then exporting them using the to_excel method.

Solution 2:

Finally I could figure this out using win32com.client module.

I used the following code successfully to import an xml to an existing Excel xlsx file I use as template, and then save it with a different name:

import win32com.client as win32excel= win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open("D:/tmp/template.xlsx")
wb.XmlImport("D:/tmp/result.xml")
wb.SaveAs("D:\\tmp\\result.xlsx")
wb.Close()

Methods for Excel workbooks can be found here. Also I had to take into account that the saveAs method doesn't support forward slashes.