Convert JSON response to CSV

Product Forums Katalon Studio
,
1

Hi there,

I messing around with a “simple” task ; writing JSON response to CSV. I have to say that i’m really pretty new in these kind of things.

I actually use json slurper to retrieve my data and put it into a variable (that works fine) but i don’t want to use it in memory ; i need it to be on a flat file (so i can use it as DDT)

My json datas retrieve multiple records and i have no total count in the header. I only need some propriety of my json for each record.

Thanks for the help !

2

Please show the given INPUT (JSON String you have), and the desired OUTPUT (a CSV text that you want).

3

Thanks for the reply ! My json in attachement.

The output would be only these datas for each record (see csv)

NumeroLiasseEnForme
DateEffet
MontantCheque
NumeroCheque
ProduitChoisi.Nom
Assures.Nom
Assures.Adresse.Commune.CodePostal
NumeroContratCavo

response.7z (4.3 KB)

Thanks

4

Please find the following. You can copy and paste this into a TestCase script and run it.

Please note that the file reponse.json is the file contained in the 7z archive you provided, and I saved the json file into the project root directory.

import groovy.json.JsonSlurper
import groovy.json.JsonOutput

def slurper = new JsonSlurper()
def jsonFile = new File("./response.json")
def contentText = jsonFile.text
def content = slurper.parseText(contentText)

//println JsonOutput.prettyPrint(contentText)

StringBuilder sb = new StringBuilder()
sb.append("NumeroLiasseEnForme")
sb.append(",")
sb.append("DateEffet")
sb.append("\n")
for (record in content) {
	sb.append(record["NumeroLiasseEnForme"])
	sb.append(",")
	sb.append(record["DateEffet"])
	sb.append("\n")
}
//println sb.toString()

def csvFile = new File("./result.csv")
csvFile.text = sb.toString()

When I ran it, I got the following output:

NumeroLiasseEnForme,DateEffet
SF20061-000505,13/05/2020
SF20061-000506,15/05/2020
SF20061-000524,13/07/2020
SF20061-000533,01/07/2020
SF20061-000534,13/07/2020
SF20061-000543,31/07/2020
5

@kazurayam you are my hero ! It works really fine !

Can i ask a little bit more ? Is it possible to do the same with an xlsx file ?

6

Do the same — what?
Do you want read XLSX and convert it into CSV?
Do you want read JSON and convert it into XLSX?

Either case, you can do it if you study programming with Apache POI. You can use the POI in Katalon studio as the following doc tells:

7

Read JSON and convert it into XLSX (instead of CSV) , as i already know how to write data in it with Katalon

Thanks for the link :slight_smile: