How to embed a CSV file in XML output

Question

I am are trying to convert a CSV (comma separated file) into XML using XSLT.

CSV Sample:

AcctEntryId,ValueDate,Entity,Folder,DenomCcy,FunctCcy
321,2017-08-29,ABC NY,My Portfolio/PAC,BR,US
322,2017-08-30,ABC NY,My Portfolio/PBC,BR,US
323,2017-08-31,ABC NY,My Portfolio/PCC,BR,US

Desired XML Output:

<?xml version="1.0" encoding="utf-8"?>
<ProcessResponse xmlns="http://com.test.ws/">
    <ProcessResult>&lt;Data DataNodeName="CData" DataType="TEXT"&gt;&lt;CData&gt;&lt;![CDATA[AcctEntryId,ValueDate,Entity,Folder,DenomCcy,FunctCcy
        321,2017-08-29,ABC NY,My Portfolio/PAC,BR,US
        322,2017-08-30,ABC NY,My Portfolio/PBC,BR,US
        323,2017-08-31,ABC NY,My Portfolio/PCC,BR,US
        ]]&gt;&lt;/CData&gt;&lt;/Data&gt;
    </ProcessResult>
</ProcessResponse>

I want to pick the CSV file from AL11 folder in sap. The final output after xslt mapping , I wish it to be XML file in the desired format specified above.

Please, can you guide me how to code an XSLT to generate above XML based on sample CSV data.

XSLT only transforms XML content. Your input described above is just CSV formatted TEXT. What scripting languages would you be using to convert it? — Mister Lucky, Jan 16 '19 at 13:24

zx485 · Answer 1 · 2019-01-17T21:57:31.113

This is an XSLT-2.0 or above solution. I do not know if SAP supports this. If not, see below for an XSLT-1.0 hack.

You can use the XSLT-2.0 function unparsed-text() in combination with the RegEx functionality of xsl:analyze-string:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:variable name="csv" select="replace(unparsed-text('file:///home/kubuntu/Downloads/a.csv'),'utf-8','')"/>

    <xsl:template match="/">
        <xsl:element name="ProcessResponse" namespace="http://com.test.ws/">
            <xsl:element name="ProcessResult" namespace="http://com.test.ws/">
                <xsl:value-of select="'&lt;Data DataNodeName=&quot;CData&quot; DataType=&quot;TEXT&quot;&gt;&lt;CData&gt;&lt;![CDATA['" />
                <xsl:analyze-string select="$csv" regex='(.+)\n'>
                    <xsl:matching-substring>
                        <xsl:value-of select="concat(regex-group(1),'&#xa;')" />
                    </xsl:matching-substring>
                    <xsl:non-matching-substring><xsl:sequence select="."/></xsl:non-matching-substring>
                </xsl:analyze-string>
                <xsl:value-of select="']]&gt;&lt;/CData&gt;&lt;/Data&gt;&#xa;'" />
            </xsl:element>
        </xsl:element>
    </xsl:template>

</xsl:stylesheet>

The output is as desired, although its requirement seems a bit strange to me...

<?xml version="1.0" encoding="UTF-8"?>
<ProcessResponse xmlns="http://com.test.ws/">
   <ProcessResult>&lt;Data DataNodeName="CData" DataType="TEXT"&gt;&lt;CData&gt;&lt;![CDATA[AcctEntryId,ValueDate,Entity,Folder,DenomCcy,FunctCcy
321,2017-08-29,ABC NY,My Portfolio/PAC,BR,US
322,2017-08-30,ABC NY,My Portfolio/PBC,BR,US
323,2017-08-31,ABC NY,My Portfolio/PCC,BR,US
]]&gt;&lt;/CData&gt;&lt;/Data&gt;
</ProcessResult>
</ProcessResponse>

If you're restricted to XSLT-1.0, you can use an Entity Reference as a hack like described in this SO answer.

score 0 · Answer 2 · answered Jan 21 '19 at 02:43

Here is the way to convert your CSV to XML without XSLT. The desired XML is rather primitive as it has only one child node, so I believe XSLT is redundant here.

DATA: lv_data     TYPE string,
      lv_filename TYPE string VALUE 'C:\usr\sap\erp\sys\src\sample.csv',
      lv_csv      TYPE string.

OPEN DATASET lv_filename FOR INPUT IN TEXT MODE ENCODING DEFAULT.

WHILE sy-subrc = 0.
READ DATASET lv_filename INTO lv_data.
CHECK strlen( lv_data ) > 0.
lv_csv = lv_csv && lv_data && cl_abap_char_utilities=>cr_lf.
NDWHILE.
CLOSE DATASET lv_filename.

DATA(lo_ixml) = cl_ixml=>create( ).
DATA(lo_response) = lo_ixml->create_document( ).
DATA(lo_result)  = lo_response->create_simple_element( name    = 'ProcessResponse' parent  = lo_response ).

CONCATENATE `&lt;Data DataNodeName="CData" DataType="TEXT"&gt;&lt;CData&gt;&lt;![CDATA[`
v_csv `]]&gt;&lt;/CData&gt;&lt;/Data&gt;` INTO lv_csv.

o_response->create_simple_element( name    = 'ProcessResult' parent  = lo_result value   = lv_csv ).

DATA(lo_streamfactory) = lo_ixml->create_stream_factory( ).
DATA(lo_ostream)  = lo_streamfactory->create_ostream_uri( system_id = 'C:\usr\sap\erp\sys\src\sample.xml' ).
DATA(lo_renderer) = lo_ixml->create_renderer( ostream  = lo_ostream document = lo_response ).
DATA(ref_ixml_encoding) = lo_ixml->create_encoding( byte_order    = 0 character_set = 'UTF-8' ).

o_ostream->set_encoding( encoding = ref_ixml_encoding ).
o_ostream->set_pretty_print( 'X' ).
o_renderer->render( ).

Just run this snippet provided your CSV lays in C:\usr\sap\erp\sys\src\ dir and the resulted XML will be put in the same dir.

How to embed a CSV file in XML output

2 Answers2