Convert CSV to JSON using Talend Open Studio for Data Integration

Question

I have the below CSV file format:

title	studentName	studentID
Science	'James','Jones'	'JA002','JO101'

I want to convert this to the below JSON format:

[{
"title": "Science",
"students":[
{
"studentname":"John",
"studentid":"JO12"
},
{
"studentname":"James",
"studentid":"JA23"
}
]
}]

How can I split the studentname and studentid in the CSV and convert it to the above JSON format using Talend Open Studio for Data Integration?

Answer 1

If there is no transformation needed, you should just be able to do the following:

tFileInputDelimited --> tFileOutputJSON

If there is some transformation needed, you might want to do the following:

tFileInputDelimited --> tWriteJSONField

Answer 2

You have 2 problems to solve here : First, splitting data so that Nth elements of your lists studentNames and studentIds match : You can do this with tNormalize and tMap :

1. Just split on the comma in your studentNames field. If you have 2 studentNames, it will create 2 rows in output (vs 1 in input) 2)Then it is a bit more tricky in tMap, you can use split method to create an array string, and access the Nth element in "studentId" list for each name

Then you'll have to build the JSON in output, which is quite difficult with tWriteJsonFields component I think (it is doable but I don't like very much this component). You have 2 nested arrays, that could be quite a hassle to build with tWriteJson.

I did it with a tJavaFlex , placed right after tMap, and JSONArray/JSONObject objects. Don't forget to import classes in advanced settings of tjavaflex

import net.minidev.json.JSONObject;
import net.minidev.json.JSONArray;