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

Javascript to convert CSV to JSON but not able to take empty value

I am using below JavaScript code to convert csv to json, where i have two delimiters Ï and Ñ

function executeScript(event) {
     var csv = event.getParameter("filecontent");
    
 const rows = csv.split(/r?n/); // Split the data into lines
 const headers = ["ID", "SID", "TYPE", "DT", "SEQNUM", "LSEQNUM", "ESEQNUM", "OSID", "SSID", "TIME", "QUANTITY", "TTIME", "STATUS", "ALLOCATED", "LVL", "PRC", "LLVL", "CPRC", "REASON", "FLAG", "NREASON", "DAY", "EFLAG", "LOCK", "GPRC", "CLVL", "NID", "PHASE", "ALLOCATION", "DELETE_FLAG", "EDBY", "EDDT", "DBY", "DDT", "REMARKS"];

const record = rows.filter(row => {
  return row.includes('PRSS');
});

const lines = record.toString().split(",");
const arrayOfObjects = lines.map(row => {
const values = row.split(/["ÏÑ"]+/);
const obj = {};
  headers.forEach((header, index) => {
    obj[header] = values[index];
  });
  return obj;
});

const jsonString = JSON.stringify(arrayOfObjects, null, 2);
}

Inputs:
PRSSÏ160ÏIÏ26/05/20Ï160Ï2515752ÏÏ456Ï100892Ï26/05/20ÏÏÏÏÏÏÏÏÏÏÏÏÏÏYÏÏÏÏÏÏNÏcs405Ï26/05/20Ïcs405Ï26/05/20ÏLOCKING PROCESS

Here some of the fields are empty but using this script i am getting below response it will not taking blank value and some fields value are skipped.

[
  {
    "ID": "PRSS",
    "SID": "160",
    "TYPE": "I",
    "DT": "26/05/20",
    "SEQNUM": "160",
    "LSEQNUM": "2515538",
    "ESEQNUM": "456",
    "OSID": "100892",
    "SSID": "26/05/201507:14:57",
    "TIME": "Y",
    "QUANTITY": "N",
    "TTIME": "cs405",
    "STATUS": "26/05/201507:14:57",
    "ALLOCATED": "cs405",
    "LVL": "26/05/201507:14:57",
    "PRC": "LOCKING PROCESS"
  }
]

If anyone suggest here for below expected output

[
  {
    "ID": "PRSS",
    "SID": "160",
    "TYPE": "I",
    "DT": "26/05/20",
    "SEQNUM": "160",
    "LSEQNUM": "2515538",
    "ESEQNUM": "456",
    "OSID": "100892",
    "SSID": "26/05/20",
    "TIME": "",
    "QUANTITY": "",
    "TTIME": "",
    "STATUS": "",
    "ALLOCATED": "",
    "LVL": "",
    "PRC": ""
    "LLVL": "",
    "CPRC": "",
    "REASON": "",
    "FLAG": "",
    "NREASON": "",
    "DAY": "",
    "EFLAG": "",
    "LOCK": "Y",
    "GPRC": "",
    "CLVL": "",
    "NID": "",
    "PHASE": "",
    "ALLOCATION": "",
    "DELETE_FLAG": "N",
    "EDBY": "cs405",
    "EDDT": "26/05/20",
    "DBY": "cs405",
    "DDT": "26/05/20",
    "REMARKS": "LOCKING PROCESS"
  }
]
Tags:

2

Answers


  1. 0

    Your split regex uses + which is 1 or more (like ...ÏÏÏÏÏÏÏÏÏÏÏÏÏÏ... in your input). If you want singular delimiter, remove the + (the " are also unnecessary as you only have 2 delimiters not 3)

    function executeScript(csv) {

  const rows = csv.split(/r?n/); // Split the data into lines
  const headers = ["ID", "SID", "TYPE", "DT", "SEQNUM", "LSEQNUM", "ESEQNUM", "OSID", "SSID", "TIME", "QUANTITY", "TTIME", "STATUS", "ALLOCATED", "LVL", "PRC", "LLVL", "CPRC", "REASON", "FLAG", "NREASON", "DAY", "EFLAG", "LOCK", "GPRC", "CLVL", "NID", "PHASE", "ALLOCATION", "DELETE_FLAG", "EDBY", "EDDT", "DBY", "DDT", "REMARKS"];

  const record = rows.filter(row => {
    return row.includes('PRSS');
  });

  const lines = record.toString().split(",");
  const arrayOfObjects = lines.map(row => {
    const values = row.split(/[ÏÑ]/);
    const obj = {};
    headers.forEach((header, index) => {
      obj[header] = values[index];
    });
    return obj;
  });

  const jsonString = JSON.stringify(arrayOfObjects, null, 2);
  return jsonString
}
console.log(executeScript("PRSSÏ160ÏIÏ26/05/20Ï160Ï2515752ÏÏ456Ï100892Ï26/05/20ÏÏÏÏÏÏÏÏÏÏÏÏÏÏYÏÏÏÏÏÏNÏcs405Ï26/05/20Ïcs405Ï26/05/20ÏLOCKING PROCESS"))
    Login or Signup to reply.
  2. 0

    Your regular expression is simply wrong.

    /["ÏÑ"]+/

    Note the + quantifier – it means ‘one or more of the preceding token’. So if it encounters a sequence like ÏÏÏ it will be treated as one token. If you use this regular expression for splitting you won’t get three individual elements for ÏÏÏ.

    Use the {1} quantifier instead. e.g. /["ÏÑ"]{1}/

    function executeScript(csv) {

  const rows = csv.split(/r?n/); // Split the data into lines

  const headers = ["ID", "SID", "TYPE", "DT", "SEQNUM", "LSEQNUM", "ESEQNUM", "OSID", "SSID", "TIME", "QUANTITY", "TTIME", "STATUS", "ALLOCATED", "LVL", "PRC", "LLVL", "CPRC", "REASON", "FLAG", "NREASON", "DAY", "EFLAG", "LOCK", "GPRC", "CLVL", "NID", "PHASE", "ALLOCATION", "DELETE_FLAG", "EDBY", "EDDT", "DBY", "DDT", "REMARKS"];
  const record = rows.filter(row => {
    return row.includes('PRSS');
  });

  const lines = record.toString().split(",");

  const arrayOfObjects = lines.map(row => {
    const values = row.split(/["ÏÑ"]{1}/);
    const obj = {};
    headers.forEach((header, index) => {
      obj[header] = values[index];
    });
    return obj;
  });

  const jsonString = JSON.stringify(arrayOfObjects, null, 2);
  return jsonString
}
console.log(executeScript("PRSSÏ160ÏIÏ26/05/20Ï160Ï2515752ÏÏ456Ï100892Ï26/05/20**ÏÏÏÏÏÏÏÏÏÏÏÏÏÏ**YÏÏÏÏÏÏNÏcs405Ï26/05/20Ïcs405Ï26/05/20ÏLOCKING PROCESS"))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search