CSV File to N IDocs in SAP Cloud Integration!

[govardanam]

This blog post describes the case of  CSV File to N IDocs conversion in SAP CI and a strong case, when a specific set of rows identified by unique value in the CSV File are to be converted into  individual IDoc, it requires conversion of grouped rows(by unique value)  into  individual messages before conversion into IDoc XML.

Use Case:

To convert CSV to IDoc XML where CSV contains rows grouped by unique values.

Prerequisites:

CSV file sorted  and grouped by unique values.

APPROACH OVERVIEW

 

Let us take a look at how the flow 2 is implemented in SAP CI.

 Step1:Convert Grouped Row Items into a Line

Groovy Script

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

import java.util.HashMap;

def Message processData(Message message) {



    def iteratorValue=1;

    def body = message.getBody(java.lang.String);

    def rows = body.split("\n");

    def rowlength = rows.length;

    def columnsize = 5;//define the columnsize based on CSV file 

    def separtedCSVMessages = new String[rowlength];

    StringBuilder finalMessage = new StringBuilder();

    def separateMessageIterator =0;

    def rowsColumnsArray = new String[rowlength][columnsize];

    for(int i=0;i<rowlength;i++)

        rowsColumnsArray[i] = rows[i].split(";") as String[];





     while(iteratorValue<rowlength){

        def uniqueIdentifier =   rowsColumnsArray[iteratorValue][0];

        separtedCSVMessages[separateMessageIterator]= (rowsColumnsArray[iteratorValue] as java.lang.String).trim();

           

        while(iteratorValue+1 <rowlength && rowsColumnsArray[iteratorValue+1][0].equals(uniqueIdentifier))  {

            iteratorValue =  iteratorValue +1;

            separtedCSVMessages[separateMessageIterator] = (separtedCSVMessages[separateMessageIterator] as String) +':'+(rowsColumnsArray[iteratorValue] as String);

            if(iteratorValue+1 == rowlength)

                break;

            }



        try {

            def temporaryString = (separtedCSVMessages[separateMessageIterator] as String).replace('[', '').replace(']','').replaceAll('\\R','');

            finalMessage.append(temporaryString+"\r\n");

        } catch(Exception e){

            e.printStackTrace();

        }



        if(iteratorValue < rowlength) {

            iteratorValue = iteratorValue + 1;

            separateMessageIterator = separateMessageIterator + 1;

        }

    }



    message.setBody(finalMessage.toString());

    return message;

}

After this step, rows with same unique value(say column1) are grouped into one string with ":" as delimiter. After all rows with same unique values(identifier)  are appended to StringBuilder, a line break is added.

Step 2:Convert Line to Message

Iterating Splitter

Parelle Processing was unchecked to Support the Transaction Handling for JMS .

Now, after this step, you have M messages, M is the distinct unique identifiers (distinct column 1 values). This message is of text format .

Step 3: Re-Structure the Msg for XML Conversion

Groovy Script

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

import groovy.util.XmlParser



def Message processData(Message message) {

    String body = message.getBody(String);

    def rows = body.split(":") as String[];

    StringBuilder finalMessage = new StringBuilder();

    for(int i=0;i<rows.length;i++){

        finalMessage.append(rows[i]+"\r\n");

    }

    message.setBody(finalMessage.toString());

    return message;

}

 

Step 4: Convert CSV to XML Message

 

Step 5: XML to IDocXML Map

This is a Message Map from XML to IDoc XML.

 

Now, let us take a look at payload and analyze the output at each stage to better understand

1. Initial CSV File Data (; Delimited)
   

   Column1;Column2;Column3;Column4;Column5
   
   Message1;R1C2;R1C3;R1C4;R1C5
   
   Message1;R2C2;R2C3;R2C4;R2C5
   
   Message1;R3C2;R3C3;R3C4;R3C5
   
   Message2;R4C2;R4C3;R4C4;R4C5
   
   Message2;R5C2;R5C3;R5C4;R5C5
   
   Message3;R6C2;R6C3;R6C4;R6C5
   
   Message3;R7C2;R7C3;R7C4;R7C5
   
   Message3;R8C2;R8C3;R8C4;R8C5
   
   Message3;R9C2;R9C3;R9C4;R9C5
   
   Message4;R10C2;R10C3;R10C4;R10C5
   
   Message5;R11C2;R11C3;R11C4;R11C5​

   
   


2. Output After Step 1:
   

   Message1, R1C2, R1C3, R1C4, R1C5:Message1, R2C2, R2C3, R2C4, R2C5:Message1, R3C2, R3C3, R3C4, R3C5
   
   Message2, R4C2, R4C3, R4C4, R4C5:Message2, R5C2, R5C3, R5C4, R5C5
   
   Message3, R6C2, R6C3, R6C4, R6C5:Message3, R7C2, R7C3, R7C4, R7C5:Message3, R8C2, R8C3, R8C4, R8C5:Message3, R9C2, R9C3, R9C4, R9C5
   
   Message4, R10C2, R10C3, R10C4, R10C5
   
   Message5, R11C2, R11C3, R11C4, R11C5     
   
   ​

   
   


3. Output After Step 2:

M messages where M is distinct column 1 values . In our case, we have 5 Distinct values, so we have 5 messages.

Let us take one of the messages here

Message1, R1C2, R1C3, R1C4, R1C5:Message1, R2C2, R2C3, R2C4, R2C5:Message1, R3C2, R3C3, R3C4, R3C5

4.Output After Step3 :

Message1, R1C2, R1C3, R1C4, R1C5

Message1, R2C2, R2C3, R2C4, R2C5

Message1, R3C2, R3C3, R3C4, R3C5

5. Output After  Step 4:

<?xml version='1.0' encoding='UTF-8'?><MT_XML><rs><r><C1>Message1</C1><C2> R1C2</C2><C3> R1C3</C3><C4> R1C4</C4><C5> R1C5</C5></r><r><C1>Message1</C1><C2> R2C2</C2><C3> R2C3</C3><C4> R2C4</C4><C5> R2C5</C5></r><r><C1>Message1</C1><C2> R3C2</C2><C3> R3C3</C3><C4> R3C4</C4><C5> R3C5</C5></r></rs></MT_XML>

Further ,above Xml can be easily converted to IDoc Xml. Final output for the flow 2 when we tested it is as below ;

5 IDoc XML Messages in receiver Jms queue.

 

Further, why Transaction handling is set ?

We have set the Transaction handling for the flow to guarantee that either the whole CSV file is converted and N IDoc XML Messages in JMS queue are created, in case where one of the IDoc Maps have issue, we don't want to create any message in JMS queue, rather we correct the errors in CSV file for the whole file to be processed  in one go. This is one of the approaches, further, if you don't set transaction handling, then individual message might be processed, based on  configuration for iterating splitter and you go correct the records for the error message and process them alone as CSV file again.

Thanks for reading 🙂