Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
community Alteryx IO Mission Control
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
connect with mentors
designer new user guide
Labels
Top Solution Authors
View All