cancel
Showing results for 
Search instead for 
Did you mean: 

Groovy script for Json to CSV conversion

0 Kudos

Hi Experts I need to convert the below json to CSV using groovy script. My input Json is below

[{ "id":1, "campaign": { "cost":0, "name":"Test1" }, "visitorId":3 }, { "id":2, "campaign": { "cost":0, "name":"Test2" }, "visitorId":5 }]

and i need the out put in the below csv format

id,cost,name,visitorID

1,0,Test1,3

2,0,Test2,5

I am using below groovy script but it gives a different output

def jsonSlurper = new groovy.json.JsonSlurper(); def object = jsonSlurper.parseText(message.getBody(java.lang.String) as String) StringBuilder sb = new StringBuilder() object.each{ sb << it.collect{ it.value } .join(",") .concat("\n")}

message.setBody(sb.toString())

return message

Any help would be highly appreciated

Accepted Solutions (1)

Accepted Solutions (1)

asutoshmaharana2326
Active Participant
0 Kudos

Dear Sabyasachi,

Please try with below script.

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

def Message processData(Message message) {
        def jsonSlurper = new groovy.json.JsonSlurper(); 
        def object = jsonSlurper.parseText(message.getBody(java.lang.String) as String) 
        StringBuilder sb = new StringBuilder() 
        object.each{ sb << it.collect{ checkMap(it) } .join(",") .concat("\n")}
        message.setBody(sb.toString())
        return message;
    }
def checkMap(def node){
    (node.value instanceof Map) ? node.value.collect{ checkMap(it) } .join(",")  : node.value
}

Thanks,

Asutosh

Answers (2)

Answers (2)

PiotrRadzki
Active Participant

Or you can use standard conversion functionality in CPI iFlow from JSON to XML and then from XML to CSV.

BR, Piotr

0 Kudos

Thanks Piotr for your reply, I tried the above approach .

Json to XML converts into deep node xml with 2 level hierarchy but the XML to CSV does not work for deep level hierarchy conversion

Ryan-Crosby
Active Contributor

Just replace the XML to CSV with an appropriate XSLT to deal with the deep nodes. Ultimately, if you want a flat file the source should be flat and not nested.

yogananda
Product and Topic Expert
Product and Topic Expert
0 Kudos

cpi2019

You can use the following Groovy script to convert the JSON to CSV:

def jsonSlurper = new groovy.json.JsonSlurper() 
def object = jsonSlurper.parseText(message.getBody(java.lang.String) as String)
StringBuilder sb = new StringBuilder()
sb.append("id,cost,name,visitorID") object.each { sb << it.collect { it.value }.join(",").concat("
") } message.setBody(sb.toString())
0 Kudos

Thanks Muthaiah for your reply, I had used the above code

but it displays the output as below different to my desired output

id,cost,name,visitorID

1,{ "cost":0, "name":"Test1" },3

2,{ "cost":0, "name":"Test2" },5