I am using below JS code to convert JSON data to CSV data,
function executeScript(event) {
var json = event.getParameter("jsondata");
var fields = Object.keys(json[0]);
var replacer = function(key, value) { return value === null ? '' : value };
var csv = json.map(function(row){
return fields.map(function(fieldName){
return JSON.stringify(row[fieldName], replacer)
}).join('|');
})
csv.unshift(fields.join('|')); // add header column
csv = csv.join('rn');
event.log(csv);
}
But using this code i am getting below response :
Username|Identifier|First name|Last name
"booker12"|"9012"|"Rachel"|"Booker"
"grey07"|"2070"|"Laura"|"Grey"
"johnson81"|"4848"|"Craig"|"Johnson"
"jenkins46"|"9346"|"Mary"|"Jenkins"
"smith79"|"5079"|"Jamie"|"Smith"
But as an output i need,
Username|Identifier|First name|Last name
booker12|9012|Rachel|Booker
grey07|2070|Laura|Grey
johnson81|4848|Craig|Johnson
jenkins46|9346|Mary|Jenkins
smith79|5079|Jamie|Smith
Can anyone suggest how to get values in CSV without ""?
input data:
[{
"Username": "booker12",
"Identifier": "9012",
"First name": "Rachel",
"Last name": "Booker"
}, {
"Username": "grey07",
"Identifier": "2070",
"First name": "Laura",
"Last name": "Grey"
}, {
"Username": "johnson81",
"Identifier": "4848",
"First name": "Craig",
"Last name": "Johnson"
}, {
"Username": "jenkins46",
"Identifier": "9346",
"First name": "Mary",
"Last name": "Jenkins"
}, {
"Username": "smith79",
"Identifier": "5079",
"First name": "Jamie",
"Last name": "Smith"
}]
2
Answers
The issue is because you call
JSON.stringify()
on a single string value. This is the cause of the double-quoted strings. It’s also redundant and can be removed.In addition, it’s worth noting that the code can be simplified. You can use the OR operator to coalesce null values to empty strings due to the falsy nature of
null
. You can also useObject.values()
o build the data body in the same manner you useObject.keys()
to build the header row.Here’s a working example:
As a side note, I’d suggest you rename the
json
variable, as it holds an array, not JSON, and your logic would be better structured if you passed the array in to the function as an argument instead of relying on a higher-scoped variable.I am not sure what is and what is not available in GCP tools. I am surprised to hear that the
Object
methods.keys()
and.values()
should not be available there. The following is an example of an event triggered function that can also receive a data array through a parameter