Import XML file directly into Excel spreadsheet

Import XML file directly into Excel spreadsheet - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Import XML file directly into Excel spreadsheet (/thread-39274.html)

Import XML file directly into Excel spreadsheet - demdej - Jan-24-2023

I'm new to Python and despite how many hours/days I've spent on this, I'm loving my journey. Currently, I'm trying to import an XML file directly into Excel via Python. I've tried converting my file from XML to CSV and XLSX to no avail. I've tried following this site https://ajalacomfort.com/python-4-beginners-xml-to-excel-within-a-minute/ but my XML file is UGLY! None of the records have properly named attributes to easily pick out the information I need. Here's what it look like"

Quote:<Worksheet ss:Name="Sheet1">
<Table>
<Column ss:AutoFitWidth="0" ss:Width="50"/>
<Column ss:AutoFitWidth="0" ss:Width="84" ss:Span="1"/>
<Column ss:AutoFitWidth="0" ss:Width="110"/>
<Column ss:AutoFitWidth="0" ss:Width="110"/>
<Column ss:AutoFitWidth="0" ss:Width="90"/>
<Column ss:AutoFitWidth="0" ss:Width="90"/>
<Column ss:AutoFitWidth="0" ss:Width="87"/>
<Column ss:AutoFitWidth="0" ss:Width="57.75"/>
<Column ss:AutoFitWidth="0" ss:Width="57.75"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:AutoFitWidth="0" ss:Width="80"/>
<Row>
<Cell><Data ss:Type="Number">6427</Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[7EQ-E2V-9VG6]]></Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="DateTime">2023-01-01T19:01:06.000</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="DateTime">2023-01-11T13:26:44.000</Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Mack Snow]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[[email protected]]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Price Check]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Low]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Resolved]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Ticket from Karen Kaldwell]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[John Doe]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[01:00:00]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[2023-00123]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[337-411-0000]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[The Round Office]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[REPORT NARRATIVE DATA. Please return my call ASAP]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Jane]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[Doe]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[10/20/94]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[1234 Testing Dr]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
<Cell><Data ss:Type="String"><![CDATA[]]></Data></Cell>
</Row>

And the rows go on and on specifying hundreds of rows. Nothing is properly labelled.

I also attempted to change the file extension with this code:

Quote:A = r"C:\Users\ddejean\Desktop\TEST\finalxml.xml"
pre, ext = os.path.splitext(A)
B = os.rename(A, pre + ".xlsx")

Visually works, but the file corrupts because the extension doesn't match the format of the file. Please help!