community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

XML ( embedded Excel sheet ) conversion to Tabular report

SyedQD
6 - Meteoroid
‎03-21-2023 01:16 PM

Hi Team, 

 

Attached file is an XML with Excel workbook work sheet , table , row , cells and data tags , 

The issue is that when i parse it i am getting following result every thing in one column , all headers and all the data are showing up in one column  ,what i am looking for is to generate a tabular report 

 

Is there a way in Alteryx to read this XML and write in a tabular format , i have tried , pivoting but it its not working as well , Any help would be highly appreciated.

 

attached is the XML file and my workflow as well which is showing the following output 

 

SyedQD_0-1679429217178.png

 

 

Follows it he XML file  

 

 

 

 

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<LastAuthor>USERNAME</LastAuthor>
<Created>15 March 2023, 16:14:47</Created>
<Version>12.00</Version>
<UUID>UUID</UUID>
<ClassName>CLASSNAME</ClassName>
</DocumentProperties>
<!-- Generated by ExcelXMLReport::getExcelReportHeader - 15 March 2023 - 16:14:47 -->
<Styles>
<Style ss:ID="s11" >
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:FontName="Arial" ss:Size="10"/>
</Style>
<Style ss:ID="s10" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Arial" ss:Size="10"/>
</Style>
<Style ss:ID="s9" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Arial" ss:Size="10"/>
</Style>
<Style ss:ID="s12" >
<Alignment ss:Vertical="Center" ss:Horizontal="Center"/>
<Font ss:FontName="Arial" ss:Size="10"/>
</Style>
<Style ss:ID="s13" >
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:FontName="Arial" ss:Size="14.25" ss:Bold="1"/>
</Style>
<Style ss:ID="s14" >
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:FontName="Arial" ss:Size="14.25" ss:Bold="1" ss:Underline="Single"/>
</Style>
<Style ss:ID="s2" >
<Alignment ss:Vertical="Center" ss:Horizontal="Left"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
</Style>
<Style ss:ID="s5" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
<NumberFormat ss:Format="00.00"/></Style>
<Style ss:ID="s3" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
<NumberFormat ss:Format="000.00"/></Style>
<Style ss:ID="s6" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
<NumberFormat ss:Format="0000.00"/></Style>
<Style ss:ID="s4" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
<NumberFormat ss:Format="00000.00"/></Style>
<Style ss:ID="s1" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Courier New" ss:Size="8.25"/>
</Style>
<Style ss:ID="s8" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Small Fonts" ss:Size="6.75"/>
<NumberFormat ss:Format="00"/></Style>
<Style ss:ID="s7" >
<Alignment ss:Vertical="Center" ss:Horizontal="Right"/>
<Font ss:FontName="Small Fonts" ss:Size="6.75"/>
<NumberFormat ss:Format="000"/></Style>
</Styles>
<Worksheet ss:Name="ExcelWorksheet">
<Table>
<Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="33"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="46"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="48"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="82"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="26"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="38"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="69"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="16"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="77"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="94"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="94"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="94"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="90"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="73"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="63"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="19"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="11"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="15"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="31"/><Column ss:StyleID="" ss:AutoFitWidth="0" ss:Width="46"/>

<Row ss:AutoFitHeight="0" ss:Height="17">
<Cell ss:Index="1" ss:StyleID="s2" ><Data ss:Type="String">Process Date:</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">15 March 2023, 16:14:07</Data></Cell>
<Cell ss:Index="9" ss:StyleID="s14" ><Data ss:Type="String">Holding company</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="11">
<Cell ss:Index="16" ss:StyleID="s12" ><Data ss:Type="String">Page</Data></Cell>
<Cell ss:Index="19" ss:StyleID="s12" ><Data ss:Type="String">1</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="10">
<Cell ss:Index="1" ss:StyleID="s2" ><Data ss:Type="String">Period End Date:</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">31/03/2023</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="29">
<Cell ss:Index="9" ss:StyleID="s13" ><Data ss:Type="String">Customer Balances</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="17">
<Cell ss:Index="14" ss:StyleID="s9" ><Data ss:Type="String">Hold&#10;(Not in Total)</Data></Cell>
<Cell ss:Index="15" ss:StyleID="s9" ><Data ss:Type="String">Future&#10;(Not in Total)</Data></Cell>
<Cell ss:Index="18" ss:StyleID="s12" ></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="22">
<Cell ss:Index="1" ss:StyleID="s11" ><Data ss:Type="String">Code</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s11" ><Data ss:Type="String">Name</Data></Cell>
<Cell ss:Index="5" ss:StyleID="s11" ></Cell>
<Cell ss:Index="7" ss:StyleID="s10" ><Data ss:Type="String">Total Due</Data></Cell>
<Cell ss:Index="9" ss:StyleID="s10" ><Data ss:Type="String">Current</Data></Cell>
<Cell ss:Index="10" ss:StyleID="s10" ><Data ss:Type="String">Overdue1</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s10" ><Data ss:Type="String">Overdue2</Data></Cell>
<Cell ss:Index="12" ss:StyleID="s10" ><Data ss:Type="String">Overdue3</Data></Cell>
<Cell ss:Index="13" ss:StyleID="s10" ><Data ss:Type="String">Overdue4</Data></Cell>
<Cell ss:Index="18" ss:StyleID="s10" ><Data ss:Type="String">Branch</Data></Cell>
<Cell ss:Index="20" ss:StyleID="s10" ><Data ss:Type="String">Slsprsn</Data></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="1" ss:StyleID="s2" ><Data ss:Type="String">000692</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s2" ><Data ss:Type="String">CompanyA</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s6" ><Data ss:Type="Number">3680.00</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">3680.00</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s1" ></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
<Cell ss:Index="17" ss:StyleID="s8" ><Data ss:Type="Number">01</Data></Cell>
<Cell ss:Index="20" ss:StyleID="s7" ><Data ss:Type="Number">013</Data></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">28/02/2023</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">81266</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">3680.00</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s1" ></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="1" ss:StyleID="s2" ><Data ss:Type="String">000621</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s2" ><Data ss:Type="String">CompanyB</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s4" ><Data ss:Type="Number">20498.19</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="12" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
<Cell ss:Index="17" ss:StyleID="s8" ><Data ss:Type="Number">01</Data></Cell>
<Cell ss:Index="20" ss:StyleID="s7" ><Data ss:Type="Number">007</Data></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="20">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">28/12/2022</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">REF-AIR-JAN-23</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s1" ></Cell>
<Cell ss:Index="11" ss:StyleID="s1" ></Cell>
<Cell ss:Index="12" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">28/01/2023</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">REF-AIR-FEB-23</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s1" ></Cell>
<Cell ss:Index="11" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">28/02/2023</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">REF-AIR-MAR-23</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">6832.73</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s1" ></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="1" ss:StyleID="s2" ><Data ss:Type="String">502</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s2" ><Data ss:Type="String">CompanyC</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s6" ><Data ss:Type="Number">2106.28</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">1053.14</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s6" ><Data ss:Type="Number">1053.14</Data></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
<Cell ss:Index="17" ss:StyleID="s8" ><Data ss:Type="Number">01</Data></Cell>
<Cell ss:Index="20" ss:StyleID="s7" ><Data ss:Type="Number">007</Data></Cell>
</Row>

<Row ss:AutoFitHeight="0" ss:Height="20">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">31/01/2023</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">FP08004</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s1" ></Cell>
<Cell ss:Index="11" ss:StyleID="s6" ><Data ss:Type="Number">1053.14</Data></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="16">
<Cell ss:Index="2" ss:StyleID="s2" ><Data ss:Type="String">28/02/2023</Data></Cell>
<Cell ss:Index="4" ss:StyleID="s2" ><Data ss:Type="String">AR Invoice</Data></Cell>
<Cell ss:Index="6" ss:StyleID="s2" ><Data ss:Type="String">FP08045</Data></Cell>
<Cell ss:Index="8" ss:StyleID="s1" ></Cell>
<Cell ss:Index="10" ss:StyleID="s6" ><Data ss:Type="Number">1053.14</Data></Cell>
<Cell ss:Index="11" ss:StyleID="s1" ></Cell>
<Cell ss:Index="12" ss:StyleID="s1" ></Cell>
<Cell ss:Index="13" ss:StyleID="s1" ></Cell>
<Cell ss:Index="14" ss:StyleID="s1" ></Cell>
<Cell ss:Index="15" ss:StyleID="s1" ></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout Orientation="Landscape"/>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0" x:Left="0" x:Right="0" x:Top="0"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<Scale>72</Scale>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PageBreaks xmlns="urn:schemas-microsoft-com:office:excel">
<RowBreaks><RowBreak><Row>38</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>78</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>117</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>157</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>197</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>236</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>277</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>317</Row></RowBreak></RowBreaks>
<RowBreaks><RowBreak><Row>350</Row></RowBreak></RowBreaks>
</PageBreaks>
</Worksheet>
</Workbook>

 

 

 

 

Xml_excel_toTabular.yxmd
Labels:
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

5 REPLIES 5
Yoshiro_Fujimori
15 - Aurora
‎03-21-2023 02:34 PM

@SyedQD ,

Your attachment is .yxmd format and so the xml data is not packaged.

Could you attach it as .yxzp file, so that I can see what's happening?

https://help.alteryx.com/20223/designer/workflow-management#export-workflow

 

Reply
SyedQD
6 - Meteoroid
‎03-21-2023 02:42 PM

thanks @Yoshiro_Fujimori

 

Please find the attached packaged Xml and workflow.

XML_to_tabularreport.yxzp
Reply
0 Likes
Yoshiro_Fujimori
15 - Aurora
‎03-21-2023 04:04 PM

Thanks for the data.

It seems the index is not uniquely connected with the field names.

So I assume the order of the rows for each "code" is fixed (at least the first 12 rows).

Please check the attached workflow works for your case.

Good luck.

Yoshiro_Fujimori_0-1679439810345.png

 

Xml_excel_toTabular.yxmd
Reply
SyedQD
6 - Meteoroid
‎03-21-2023 04:32 PM

Great Thanks a lot Yoshiro , thats exactly what i was looking for , i can handle the rest of the information ( top 12 rows ) 

 

Reply
SyedQD
6 - Meteoroid
‎04-20-2023 03:24 PM

Not only i have learned something new but i can say its one of the besthing i have learned so far because working with simple data is easy but the messy data as described above i know what is critically needed there which was an ID or some sort of identifier that the categories the data as one record .

Thank you very much @Yoshiro_Fujimori 

Reply
register for community webinar
Labels