CSV to XML Conversion for Nested Structure using Groovy Script in SAP CPI

[former_member184293]

Introduction:

This blog helps you solve most common requirement to convert nested CSV structure to an XML in SAP CPI.

Many times, we are required to convert CSV structures to deeply nested XML structures. Standard Conversion methods in SAP CPI allow you to convert the incoming CSV file to flat XML structures only i.e., only one level of nesting is possible.

IF we are receiving a complex/nested csv files from third party system, then standard CSV converters in SAP CPI would not help us. In that case we need to use of either Groovy Script or third-party conversion agents or inbuilt graphical mapping by using intermediate structures and user defend functions.

This blog explains about the Groovy script used to convert a CSV structure to Nested XML file.  In the below scenario, I have taken a sample XML file and converted that to an output XML file using Groovy script in SAP CPI.

 

Current Scenario:

In this scenario I get a Purchase Order and multiple Line Items associated to it. Received multiple purchase Orders from the client as shown in Input File. We need to convert this .csv file into an XML File as shown in the output File.

Below groovy script can be used to achieve this requirement.

Sample Input CSV File :

 

Output XML:

 

In the CSV_XML Converter, i have added the Groovy script mentioned below.

 

Groovy Script:

Code Explanation:

• getRows Method splits each line and store it in a array(lines). Lines array is split based on the commas and stored in List(rows).


• getListNodes creates an array of objects with each PO and associated Items together.


• generateXML Method converts the object to the XML format. I have hardcoded the fields for code simplicity. You can enhance the code based on your requirement.

 

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

import groovy.xml.*;



def getRows(body) {

    String[] lines = body.split("\\r?\\n")

    lines[0].replace('\n', '')

  //  List<String[]> rows = lines.collect {it.tokenize(',')}

  List<String[]> rows = lines.collect {it.split(',')}

   println rows

   return rows

}



def getListNodes(input) {

    def listOfNodes = [];

    for (int i = 0; i < input.size(); i++) {

       //Identifier for the Purchase Order is “PO” 

       if (input[i][0] == 'PO') {

            def obj = [

                    PurchaseOrders: input[i],

                    items : []

            ]



            for (int j = i + 1; j < input.size(); j++) {

               //All the “ITEM” will come under the “PO” structure. 

               if (input[j][0] != 'PO') {

                    obj.items.add(input[j])

                } else {

                    i = j - 1;

                    break;

                }

            }

            listOfNodes.add(obj)

        }

    }



    return listOfNodes

}



def generateXML(xml, listNodes) {

    // println listNodes

    listNodes.each{it -> 

        def currHeader = it.get('PurchaseOrders')

        def currNode = it.get('items')



        xml.PurchaseOrders {

            BELNR(currHeader[1])

            RECIPNT_NO(currHeader[2])

            CURCY(currHeader[3])

             Item{

            currNode.each { it1 ->

               

                    Items {    

                    POSEX(it1[1])

                    MENGE(it1[2])

                    MENEE(it1[3])

                    PEINH(it1[4])

                    NETWR(it1[5])

                        }

                    }        

            }

        }

    }

}



def Message processData(Message message) {

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

    def writer = new StringWriter()

    def xml = new MarkupBuilder(writer)



    def rows = getRows(body)

    def listNodes = getListNodes(rows)

    generateXML(xml, listNodes)



    message.setBody(writer.toString());

    return message

}

 

Conclusion:

So, to conclude, this blog post helps you in converting a complex .csv into an XML in SAP CPI in cases where standard converter is not sufficient.

If you have any queries, please feel free to ask your question in comments. I would request everyone to provide your feedback and like if this blog post finds helpful for you.

 

Thanks & Regards,

Kavya Priya

[YuvrajKarwar]

Hello Kavya,

Nice blog. Thanks for sharing.

Just to add we can use  serialize  method from class groovy.xml.XmlUtil to create output xml rather than writing separate function to generate xml structure, of course with slight modification in getListNodes function.

[former_member17443]

Hello Kavya,

Thank you for the insigts

I have tried the same script but getting only the last item in the XML file.

 

Attached the snapshots

 

ConvertedXMLFile

 

Input file

 

 

is there anything I missed here?

 

[former_member184293]

Hi Avinash,

 

I have tried again. It is working as expected. Can you please refer to the below link

https://groovyide.com/cpi

 

Please let me know if you are still facing any issues.

[former_member184293]

Thanks for the Feedback. Surely I will give it a try.

[former_member17443]

Hello Kavya,

Thanks for the response

Still facing the same issue. Coud you plase share the snapshot of the input in the text format which I have posted earlier.

 

[vineet_ruhela]

This is a great Blog Kavya, You have put your efforts and create this solution with single groovy script. This will surely going to help many SAP Consultant.

I tested myself and it worked flawlessly 🙂

 

 

[szimara_1]

Hello Avinash

depending on your needs you should change this line

List<String[]> rows = lines.collect {it.tokenize(',')}

to

List<String[]> rows = lines.collect {it.split(';')}

The split() method returns a string [] instance and the tokenize() method returns a list instance tokenize() ,which returns a list, will ignore empty string (when a delimiter appears twice in succession) where as split() keeps such string.

cheers,

Stefan

[Jajam_Akhil]

Hi Kavya,

I was able to achieve the expected output for my requirement using this Groovy script however in my file there are no values for few of the fields. Then the values gets swap. Can you let me know how to enhance your script.

Thanks in advance!!!!

Example:

[former_member184293]

Hi Sai,

Sorry for the late reply. you can use the below code for getRows. it should work

def getRows(body) {

    String[] lines = body.split("\\r?\\n")

    lines[0].replace('\n', '')

  //  List<String[]> rows = lines.collect {it.tokenize(',')}

  List<String[]> rows = lines.collect {it.split(',')}

   println rows

   return rows

}

[former_member184293]

Thanks Stefan.

I have updated the Blog to handle empty values also.

[abbasabbas]

Hello Kavya,

Could you also try to provide XML nested structure to CSV using Groovy in SAP CPI ?