converting MXML to XLSX file

[AmruthaPydeti]

I am trying to convert a microsoft xml file into excel spreadsheet. 

the xml files starts like this.

<?xml version="1.0" encoding="UTF-8"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmfile" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"><OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"><DownloadComponents/></OfficeDocumentSettings>

When i tried to use xml parse tool it errors out saying "Invalid character reference at Line 1".  If i check Ignore XML errors and continue it gives error as "No Root element". I then checked Return Root element and it gives error as "No valid XML to Parse".

Anyone had experience with converting MXML to xlsx?

[gyang3]

Hi @AmruthaPydeti can you send us the xml file or a sample of that xml? It may be due to how you're reading in that XML file. 

[AmruthaPydeti]

sorry, I cant paste the file here. But I can say it is not about how i am reading the file. As soon as I drag and drop the file on to the designer it shows the error even before i run it.

[danilang]

Hi @AmruthaPydeti 

This is old MS Office 2003 xml that was used before the new Office Open XML.  If this is a one time conversion, your best option is to open it in excel and resave as xlsx.  I've never seen one of these myself and Alteryx has no native way to open it to just extract the data.    

For the error messages that you're seeing, they probably stem from the fact that the XML file itself is badly formatted or contains illegal characters.  Drag the file into Chrome and see if it complains as well. 

The Sample from the wiki page opens properly in Alteryx as xml.  From there you have to drill down to find the cell and row data that you're looking for.  This won't be a simple process.   Look at Weekly Challenge 116 A Symphony of Parsing Tools for tips on how to find the data that you need.

Good Luck

Dan