Question 109449 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Groovy script for Json to CSV conversion

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())

Any guidance would be highly appreciated

Tags:

2

Answers


  1. 0

    You aren’t going to be able to just generically write a JSON to CSV converter because JSON has hierarchy and CSV is a flat table. So you’ll have to get specific:

    new File("objects.csv").withPrintWriter { writer ->
   writer.println("id,cost,name,visitorID")
   objects.each { row ->
      writer.println( "${row.id},${row.campaign.cost},${row.campaign.name},${row.visitorId}" )
   }
}

    To make it more generic or higher level you’ll have to make situation specific assumptions to make less typing. But it won’t work for all JSONs so unless the example you gave is exactly what you are trying to write out you’ll have to think harder about your situation.

    Word to the wise you’re not using a CSV writing library which without it means your are not handling the harder parts of CSV like escaping, quoting, etc. My suggestion is DO NOT write your own version of CSV creation code. Go get a CSV library to help you.

    Login or Signup to reply.
  2. 0

    Your object is a List of nested Maps. If you want it to be kind of generic and you can rely on this specific structure you could try something like

    def jsonSlurper = new groovy.json.JsonSlurper()
List<Map> object = jsonSlurper.parseText(message.getBody(java.lang.String) as String)
List<List<Map.Entry>> rows = object.collect{ Map map -> map.collectMany{ k, v -> v instanceof Map ? v.entrySet() : [Map.entry(k, v)] } }
List header = rows.first().collect { it.key }
List<List> body = rows.collect { row -> row.collect { it.value } }
String csvOutput = ([header] + body).collect { List row -> row.join(",") }.join("n")

    but you better listen to chubbsondubs’ advice.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search