Question Re: XML to excel conversion in File Content Conversion SAP PI in Technology Q&A

XML to excel conversion in File Content Conversion SAP PI

former_member290264 — Mon, 17 Sep 2018 07:29:57 GMT

Hello Expert,

I need to do XML to excel conversion.

Please tell me what extra I should have to do in FCC??

below is my FCC

I am getting below output :

All output in 1 cell, I need it in different cells.

Please suggest me the changes.

Thanks,

Ravi

Re: XML to excel conversion in File Content Conversion SAP PI

Former Member — Mon, 17 Sep 2018 12:25:22 GMT

I'd recommend using the FormatConversionBean for this:
https://blogs.sap.com/2014/11/03/exceltransformbean-part-2-convert-simple-xml-to-various-excel-formats-easily/

FCC only creates Fixed Field lengths or CSVs

Re: XML to excel conversion in File Content Conversion SAP PI

apu_das2 — Mon, 17 Sep 2018 16:53:18 GMT

Hi Ravi,

Below is a sample XML and XSLT mapping.

Refer this and fulfill your requirement.

Test XML -

<ns0:MT_TEST_IN xmlns:ns0="urn://MM_Test_Sender">
<RECORD>
<NAME>awd</NAME>
<AGE>21</AGE>
<ADDRESS>XYZ</ADDRESS>
</RECORD>
<RECORD>
<NAME>werwer</NAME>
<AGE>65</AGE>
<ADDRESS>PQR</ADDRESS>
</RECORD>
ns0:MT_TEST_IN>

XSLT -

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0" 
xmlns:html="http://www.w3.org/TR/REC-html40"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    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">
 
    <xsl:template match="/">
  <Workbook>
            <Styles>
                <Style ss:ID="Default" ss:Name="Normal">
                    <Alignment ss:Vertical="Bottom" />
                    <Borders />
                    <Font />
                    <Interior />
                    <NumberFormat />
                    <Protection />
                </Style>
                <Style ss:ID="s21">
                    <Font ss:Size="22" ss:Bold="1" />
                </Style>
                <Style ss:ID="s22">
                    <Font ss:Size="14" ss:Bold="1" />
                </Style>
                <Style ss:ID="s23">
                    <Font ss:Size="12" ss:Bold="1" />
                </Style>
                <Style ss:ID="s24">
                    <Font ss:Size="10" ss:Bold="1" />
                </Style>
            </Styles>
 
            <Worksheet ss:Name="Page1">
                <Table>
                    <xsl:call-template name="XMLToXSL" />
                </Table>
            </Worksheet>
        </Workbook>
 
    </xsl:template>
 
    <xsl:template name="XMLToXSL">
 
        <Row>
                        <Cell>
                            <Data ss:Type="String">Name</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">Age</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">Address</Data>
                        </Cell>
                         
                    </Row>
        <xsl:for-each
            select="//RECORD">
 
            <Row>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="NAME" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="AGE" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="ADDRESS" />
                    </Data>
                </Cell>
            </Row> 
        </xsl:for-each>
    </xsl:template>
<xsl:template match="MT_TEST_IN">
</xsl:template>
</xsl:stylesheet>

Re: XML to excel conversion in File Content Conversion SAP PI

former_member290264 — Tue, 18 Sep 2018 07:46:30 GMT

Thanks, Apu Das for sharing this but this is for my practice.

I need output as each data in the different cell using FCC. attached the screen shot.

excel-output.jpg

Re: XML to excel conversion in File Content Conversion SAP PI

former_member290264 — Tue, 09 Oct 2018 15:02:48 GMT

correct FCC.

Recordset.fieldFixedLengths               2,2,2,2 
Recordset.endSeparator                    '0x0D''0x0A' 
Recordset.fieldNames                      Output_1,Output_2,Output_3,Output_4 
Recordset.keyFieldInStructure             ignore 
ignoreRecordsetName                       true 
Recordset.fieldSeparator                  '0x09'