how can we convert json file to csv file before data loading activity in Adobe campaign classic

[Shrutii]

hi all,

stuck in one challenging situation where 

how can we convert json file to csv file before data loading activity in Adobe campaign classic

Thanks!

[LakshmiPravallika · Accepted solution]

Hi @Shrutii ,

Please use the below code in the JavaScript activity before Data loading activity to convert .json to .csv 

var inputFilePath= "/{Your file path}/Test.json"
var addKeys = true;
var delimiter = ",";
convertToCSV(inputFilePath, addKeys, delimiter);

function convertToCSV(inputFilePath, addKeys, delimiter) {
var jsonFile = new File(inputFilePath);
jsonFile.open("r", File.CODEPAGE_UTF8);
instance.vars.outputFilePath = inputFilePath.replace('.json','.csv');
var csvFile = new File(instance.vars.outputFilePath);
csvFile.open("w", File.CODEPAGE_UTF8);
//conversion logic starts
var line = '';
var fullFile = '';
while(line = jsonFile.readln()) {
fullFile = fullFile + line;
}
var jsonObj = JSON.parse(fullFile);

for (var obj in jsonObj) {
var flatObj = {};
flatObj = flattenObject(jsonObj[obj]);
var keyVal = getKeysAndValues(flatObj);
var allKeys = keyVal["keys"];
var allVals = keyVal["vals"];
if (addKeys == true) {
csvFile.writeln(allKeys.join(delimiter));
addKeys = false;
}
csvFile.writeln(allVals.join(delimiter));
}
//conversion logic ends

jsonFile.close();
csvFile.close();
}

function getKeysAndValues(obj) {
var keys = [];
var vals = [];
for (var item in obj) {
keys.push(item);
vals.push(obj[item]);
}
return {"keys":keys,"vals":vals};
}

function flattenObject(ob) {
var toReturn = {};

for (var i in ob) {
if ((typeof ob[i]) == 'object') {
var flatObject = flattenObject(ob[i]);
for (var x in flatObject) {
toReturn[i + '.' + x] = flatObject[x];
}
} else {
toReturn[i] = ob[i];
}
}
return toReturn;
}

Also, PFA the link to get the Sample reference.

Hope this helps.

Regards,

Pravallika.