Python Forum
Import XML file directly into Excel spreadsheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Import XML file directly into Excel spreadsheet
#1
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-beginn...-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!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 249 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 754 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Returning Column and Row Data From Spreadsheet knight2000 0 413 Oct-22-2023, 07:07 AM
Last Post: knight2000
  Editing spreadsheet/csv BSDevo 6 969 Sep-01-2023, 05:47 PM
Last Post: BSDevo
Video doing data treatment on a file import-parsing a variable EmBeck87 15 2,667 Apr-17-2023, 06:54 PM
Last Post: EmBeck87
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Save and Close Excel File avd88 0 2,840 Feb-20-2023, 07:19 PM
Last Post: avd88
  Trying to access excel file on our sharepoint server but getting errors cubangt 0 773 Feb-16-2023, 08:11 PM
Last Post: cubangt

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020