JavaScript实现CSV转JSON丢失空值问题求助

问题背景 #

现有JavaScript代码将使用Ï和Ñ作为分隔符的CSV转换为JSON，但无法保留空值，导致字段值错位。需修正代码实现正确的字段映射，保留空值。

原代码 #

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);
}

输入数据 #

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

当前错误输出 #

[
  {
    "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"
  }
]

期望正确输出 #

[
  {
    "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"
  }
]

代码修正方案 #

原代码问题点：

1. 多余的record.toString().split(",")步骤破坏了原始行结构，输入数据不存在逗号分隔符
2. 分隔符正则/["ÏÑ"]+/会合并连续分隔符，导致空值被忽略，且错误包含了无意义的双引号匹配

修正后的代码：

function executeScript(event) {
    var csv = event.getParameter("filecontent");
    
    const rows = csv.split(/\r?\n/);
    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 arrayOfObjects = rows
        .filter(row => row.includes('PRSS'))
        .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;
}

修正说明 #

• 移除了无意义的逗号分割步骤，直接处理过滤后的行数据
• 调整分隔符正则为/[ÏÑ]/，避免合并连续分隔符，保留空值
• 通过values[index] || ""保证所有字段都存在对应值，防止字段错位或缺失

内容的提问来源于stack exchange，提问作者Neha Parmar