SAP Cloud Platform Integration- Handling CSV with Header and Footer

Introduction:

Recently came across many questions in our community about handling CSV with Header and footer in SAP Cloud Platform Integration converter step.

Its known to most of the Integration Developers that its not possible to perform the conversion of CSV with headers and Trailer into equivalent XML having header,body and trailer using converter only,so I have tried my best to give some insight on achieving same using Script,Converter,Filter and Content Modifier steps.

Following are the few scenarios I would like to showcase.

Scenario 1:

Convert CSV with Header[ Single Field ] and Trailer to equivalent XML.

Input Data:

HE_XXX_2017052310:20
aaa,123456,abc,fnametest,lnametest2
bbb,123460,xyz,fnametest1,lnametest2 
TR_YYY

Expected Output Data:

<Root>
	<Header>HE_XXX_2017052310:20</Header>
	<Record>
		<Group>aaa</Group>
		<Filenumber>123456</Filenumber>
		<Person>abc</Person>
		<Firstname>fnametest</Firstname>
		<Lastname>lnametest2</Lastname>
	</Record>
	<Record>
		<Group>bbb</Group>
		<Filenumber>123460</Filenumber>
		<Person>xyz</Person>
		<Firstname>fnametest1</Firstname>
		<Lastname>lnametest2</Lastname>
	</Record>
	<Trailer>TR_YYY</Trailer>
</Root>

Whenever we get above requirement first thought would be usage of CSV to XML Converter.Unfortunately it can not be handled using standard converter only.

Integration Process Design:

Below pic more insight on the Solution which can be implemented.

Step 2: Script to filter body without header and trailer.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;

def Message processData(Message message) {
	
	def body= message.getBody(String.class);
	def header_data = body.substring(0, body.indexOf('\n'));
 
	if (header_data != '')
	{
	
	 header_data = header_data.replace('\r','');
	 header_data = header_data.replace('\n','');
	 
 	 message.setProperty("P_FHeader", header_data);
 	 body = body.substring(body.indexOf('\n')+1);
    }


	def footer_data = body.substring(body.lastIndexOf('\n'), body.length());

	if (footer_data != '')
	{
     footer_data = footer_data.replace('\r','');
     footer_data = footer_data.replace('\n','');
   
     message.setProperty("P_FFooter", footer_data);
     body = body.substring(0, body.lastIndexOf('\n'));
	}
	message.setBody(body);
	return message;
}

Step 3: Converter to convert the CSV to XML

Step 4: Content Filter to remove root node created in Converter.

Scenario 2:

Convert CSV with Header[ Multiple Fields ] and Trailer to equivalent XML.

Input Data:

HE_XXX_2017052310:20,K,L,P
aaa,123456,abc,fnametest,lnametest2
bbb,123460,xyz,fnametest1,lnametest2 
TR_YYY

Expected Output:

<Root>
	<Header>
		<H0>HE_XXX_2017052310:20</H0>
		<H1>K</H1>
		<H2>L</H2>
		<H3>P</H3>
	</Header>
	<Record>
		<Group>aaa</Group>
		<Filenumber>123456</Filenumber>
		<Person>abc</Person>
		<Firstname>fnametest</Firstname>
		<Lastname>lnametest2</Lastname>
	</Record>
	<Record>
		<Group>bbb</Group>
		<Filenumber>123460</Filenumber>
		<Person>xyz</Person>
		<Firstname>fnametest1</Firstname>
		<Lastname>lnametest2 </Lastname>
	</Record>
	<Trailer>TR_YYY</Trailer>
</Root> 

Integration Process Design:

All the steps mentioned in the above scenario remains same except Step 2 [ Script ].

Step 2:

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;

def Message processData(Message message) {
	
    def body= message.getBody(String.class);
   
  //Retrieve Header Data
    def header_data = body.substring(0, body.indexOf('\n'));
    if (header_data != '')
    {
    
      header_data = header_data.replace('\r','');
      header_data = header_data.replace('\n','');
      String[] list =header_data.split(",");
      def Header_List= ""
      if (list.length > 0)
      {
         for (int j = 0; j < list.length; j++)
         {
           if(j!= (list.length-1))
             Header_List=Header_List+"<H"+j+">"+list[j]+"</H"+j+">"+"\n"
           else
             Header_List=Header_List+"<H"+j+">"+list[j]+"</H"+j+">"
         }
       }
   
      message.setProperty("P_FHeader", Header_List);
    //Truncate Header data from body
      body = body.substring(body.indexOf('\n')+1);
     }

    //Retrieve Trailer Data
    def footer_data = body.substring(body.lastIndexOf('\n'), body.length());
    if (footer_data != '')
    {
     footer_data = footer_data.replace('\r','');
     footer_data = footer_data.replace('\n','');
  
     message.setProperty("P_FFooter", footer_data);
    //Truncate Trailer data from body
     body = body.substring(0, body.lastIndexOf('\n'));
    }
    message.setBody(body);
    return message;
}

Conclusion:

Above scenarios can be simplified very much by using Script only,but my intention was to show how other steps can also be used to achieve the required result.Hope it helps beginners to start with File based scenarios with bit of transformation basics.

Thanks & Regards,

Sriprasad Shivaram Bhat