3

Does anyone have any sample Groovy code to convert a JSON document to CSV file? I have tried to search on Google but to no avail.

Example input (from comment):

[ company_id: '1',
  web_address: 'vodafone.com/',
  phone: '+44 11111',
  fax: '',
  email: '',
  addresses: [ 
      [ type: "office", 
        street_address: "Vodafone House, The Connection",
        zip_code: "RG14 2FN",
        geo: [ lat: 51.4145, lng: 1.318385 ] ]
  ],
  number_of_employees: 91272,
  naics: [
      primary: [ 
          "517210": "Wireless Telecommunications Carriers (except Satellite)" ],
      secondary: [ 
          "517110": "Wired Telecommunications Carriers",
          "517919": "Internet Service Providers",
          "518210": "Web Hosting"
      ]
  ]

More info from an edit:

def export(){
   def exportCsv = [ [ id:'1', color:'red', planet:'mars', description:'Mars, the "red" planet'], 
                     [ id:'2', color:'green', planet:'neptune', description:'Neptune, the "green" planet'],
                     [ id:'3', color:'blue', planet:'earth', description:'Earth, the "blue" planet'],
                   ]
    def out = new File('/home/mandeep/groovy/workspace/FirstGroovyProject/src/test.csv') 
    exportCsv.each {
        def row = [it.id, it.color, it.planet,it.description]
        out.append row.join(',')
        out.append '\n'
    }
    return out
}
tim_yates
  • 167,322
  • 27
  • 342
  • 338
Mandeep Gill
  • 161
  • 1
  • 2
  • 6
  • This would be bespoke to your json as there is no specification that objects in a json array must contain the same properties. Do you have some example json? What have you tried? What difficulty do you have? – tim_yates Feb 05 '14 at 11:40
  • i have String JSON data . i want to convert this json data to csv file using groovy code. like here i have created csv file with static data but i want to create csv file from string json object and want to send this csv file as a attachment using send email code in groovy – Mandeep Gill Feb 05 '14 at 12:12
  • Your question makes it apparent that you have some Json and want to make a CSV, but what JSON do you have? Any solution is going to be unique to your json, as Json can take may forms, and doesn't have to match a csv format easily. Can you post some example Json into your question as an edit? – tim_yates Feb 05 '14 at 12:15
  • Map with the following structure (represented as Json): [ company_id: '1' web_address: 'http://vodafone.com/', phone: '+44 11111', fax: '', email: '', addresses: [ [ type: "office", street_address: "Vodafone House, The Connection", zip_code: "RG14 2FN", geo: [ lat: 51.4145, lng: 1.318385 ] ] ], number_of_employees: 91272, naics: [ primary: [ "517210": "Wireless Telecommunications Carriers (except Satellite)", ], secondary: [ "517110": "Wired Telecommunications Carriers", "517919": "Internet Service Providers", "518210": "Web Hosting" ] ], – Mandeep Gill Feb 05 '14 at 12:31
  • Right, so I've added that formatted as an edit to your question. That's not a full list (it just ends), also what sort of format CSV were you expecting? – tim_yates Feb 05 '14 at 12:39
  • 1
    I think you should go complementing your question instead of using comments ... you can edit your question ... – tetri Feb 05 '14 at 12:41
  • def export(){ def exportCsv = [ [id:'1',color:'red',planet:'mars',description:'Mars, the "red" planet'], [id:'2',color:'green',planet:'neptune',description:'Neptune, the "green" planet'], [id:'3',color:'blue',planet:'earth',description:'Earth, the "blue" planet'], ] def out = new File('/home/mandeep/groovy/workspace/FirstGroovyProject/src/test.csv') exportCsv.each { def row = [it.id, it.color, it.planet,it.description] out.append row.join(',') out.append '\n' } return out } – Mandeep Gill Feb 05 '14 at 12:43
  • here m using out as a csv file object to send file as attachment – Mandeep Gill Feb 05 '14 at 12:44
  • All of this information should go in the question, and not in comments where it's impossible to read :-( – tim_yates Feb 05 '14 at 13:07
  • http://stackoverflow.com/questions/7172158/converting-json-to-xls-csv-in-java lets see here ..i want to implement this code in groovy – Mandeep Gill Feb 06 '14 at 05:05

1 Answers1

9

Ok, how's this:

import groovy.json.*

// Added extra fields and types for testing    
def js = '''{"infile": [{"field1": 11,"field2": 12,                 "field3": 13},
                        {"field1": 21,             "field4": "dave","field3": 23},
                        {"field1": 31,"field2": 32,                 "field3": 33}]}'''


def data = new JsonSlurper().parseText( js ) 
def columns = data.infile*.keySet().flatten().unique()

// Wrap strings in double quotes, and remove nulls
def encode = { e -> e == null ? '' : e instanceof String ? /"$e"/ : "$e" }

// Print all the column names
println columns.collect { c -> encode( c ) }.join( ',' )

// Then create all the rows
println data.infile.collect { row ->
    // A row at a time
    columns.collect { colName -> encode( row[ colName ] ) }.join( ',' )
}.join( '\n' )

That prints:

"field3","field2","field1","field4"
13,12,11,
23,,21,"dave"
33,32,31,

Which looks correct to me

tim_yates
  • 167,322
  • 27
  • 342
  • 338
  • The script fails if a value contains a double quote, which would have to be escaped using two double quotes, for example `da"ve` should yield `"da""ve"`. – roskakori Mar 30 '18 at 09:39
  • True... You should use a CSV library instead of the above – tim_yates Mar 30 '18 at 10:41