Question 330750 in Javascript Question posted in
A very good W3school tutorial can be found here.

JavaScript to convert JSON to CSV without values in " "

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"
}]
Tags:

2

Answers


  1. 0

    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 use Object.values() o build the data body in the same manner you use Object.keys() to build the header row.

    Here’s a working example:

    const buildRow = arr => arr.join('|') + 'rn';

function executeScript(event) {
  var json = accountData; // event.getParameter("jsondata");
  
  var csv = buildRow(Object.keys(json[0])); // header row
  csv += json.map(r => buildRow(Object.values(r).filter(v => v || ''))).join(''); // body
  return csv;
}

const accountData = [{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"}];
console.log(executeScript());

    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.

    Login or Signup to reply.
  2. 0

    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

    const accountData = [{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"}];

function executeScript(dat) { return function(ev){
  console.log( 
    [Object.keys(dat[0])]
    .concat(dat.map(r=>Object.values(r)))
    .map(r=>r.join("|"))
    .join("n") );
}}

document.querySelector("button").addEventListener("click",executeScript(accountData));
    <button>make CSV</button>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search