如何将特定结构CSV转换为指定格式的JavaScript Object数组？

问题背景 #

我有一份CSV文件，需要从中提取各类数据并转换为指定格式的Object数组，但使用默认的CSV转JSON工具得到的结果不符合预期。

原始CSV内容 #

"+++++++++ 1.2 LifeTime Cost and Emissions +++++++++","<TABLE>"
" ","year1","year2","year3","year4","year5","year6","year7","year8","year9","year10","year11","year12","year13","year14","year15","year16","year17","year18","year19","year20","year21","year22","year23","year24","year25","<HEADER>"
"Total Annual Energy Costs (incl. annualized capital costs and electricity sales) ($)",-560.9845,353.4204,451.6855,514.2567,523.2091,572.8177,622.6726,632.3996,642.4129,652.7211,663.3330,674.2575,1458.1040,617.1780,661.0587,692.5061,705.1385,732.5260,760.2972,774.0806,788.2706,802.8795,817.9194,833.4033,849.3444
"Total Annual CO2 emissions (kg)",387734.0330,387734.0330,387736.8925,387736.8925,387736.8925,387738.4191,387738.4191,387738.4191,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886
"Levelized Cost Of Energy ($/kWh)",-0.1738,0.1095,0.1404,0.1598,0.1626,0.1786,0.1942,0.1972,0.2003,0.2035,0.2069,0.2103,0.4547,0.1925,0.2061,0.2159,0.2199,0.2284,0.2371,0.2414,0.2458,0.2504,0.2551,0.2599,0.2649

尝试的代码 #

const csvFilePath = 'Result_Lifetime002.csv';
const json = await csvToJson().fromFile(csvFilePath);
const jsonString = JSON.stringify(json, null, 2);

问题 #

上述代码返回的是包含数组的大字符串，不符合预期。

预期格式 #

const result = [
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Total Annual Energy Costs (incl. annualized capital costs and electricity sales) ($)",
        "year1": -560.9845,
        "year2": 353.4204,
        "year3": 451.6855,
        "year4": 514.2567,
        "year5": 523.2091,
        "year6": 572.8177,
        "year7": 622.6726,
        "year8": 632.3996,
        "year9": 642.4129,
        "year10": 652.7211,
        "year11": 663.3330,
        "year12": 674.2575,
        "year13": 1458.1040,
        "year14": 617.1780,
        "year15": 661.0587,
        "year16": 692.5061,
        "year17": 705.1385,
        "year18": 732.5260,
        "year19": 760.2972,
        "year20": 774.0806,
        "year21": 788.2706,
        "year22": 802.8795,
        "year23": 817.9194,
        "year24": 833.4033,
        "year25": 849.3444
    },
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Total Annual CO2 emissions (kg)",
        "year1": 387734.0330,
        "year2": 387734.0330,
        "year3": 387736.8925,
        "year4": 387736.8925,
        "year5": 387736.8925,
        "year6": 387738.4191,
        "year7": 387738.4191,
        "year8": 387738.4191,
        "year9": 387738.8886,
        "year10": 387738.8886,
        "year11": 387738.8886,
        "year12": 387738.8886,
        "year13": 387738.8886,
        "year14": 387738.8886,
        "year15": 387738.8886,
        "year16": 387738.8886,
        "year17": 387738.8886,
        "year18": 387738.8886,
        "year19": 387738.8886,
        "year20": 387738.8886,
        "year21": 387738.8886,
        "year22": 387738.8886,
        "year23": 387738.8886,
        "year24": 387738.8886,
        "year25": 387738.8886
    },
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Levelized Cost Of Energy ($/kWh)",
        "year1": -0.1738,
        "year2": 0.1095,
        "year3": 0.1404,
        "year4": 0.1598,
        "year5": 0.1626,
        "year6": 0.1786,
        "year7": 0.1942,
        "year8": 0.1972,
        "year9": 0.2003,
        "year10": 0.2035,
        "year11": 0.2069,
        "year12": 0.2103,
        "year13": 0.4547,
        "year14": 0.1925,
        "year15": 0.2061,
        "year16": 0.2159,
        "year17": 0.2199,
        "year18": 0.2284,
        "year19": 0.2371,
        "year20": 0.2414,
        "year21": 0.2458,
        "year22": 0.2504,
        "year23": 0.2551,
        "year24": 0.2599,
        "year25": 0.2649
    }
]

解决方案 #

由于这份CSV的结构特殊，默认的CSV转JSON工具无法直接生成目标格式，需要手动解析并构建对象：

const fs = require('fs').promises;

async function convertCsvToTargetFormat() {
  // 读取CSV文件内容
  const csvContent = await fs.readFile('Result_Lifetime002.csv', 'utf8');
  // 拆分成行，过滤空行
  const rows = csvContent.split('\n').filter(row => row.trim() !== '');

  // 提取主标题（第一行第一列，去除引号）
  const mainTitle = rows[0].split(',')[0].replace(/"/g, '');
  // 提取年份表头（第二行，从第1列到第25列，去除引号）
  const yearHeaders = rows[1].split(',').slice(1, 26).map(header => header.replace(/"/g, ''));

  // 处理数据行（第3到第5行）
  const result = rows.slice(2).map(dataRow => {
    const columns = dataRow.split(',').map(col => col.replace(/"/g, ''));
    // 构建目标对象
    const item = {
      [mainTitle]: columns[0]
    };
    // 为每个年份赋值，转换为数值类型
    yearHeaders.forEach((year, index) => {
      item[year] = parseFloat(columns[index + 1]);
    });
    return item;
  });

  // 输出结果
  console.log(JSON.stringify(result, null, 2));
  return result;
}

// 执行转换
convertCsvToTargetFormat();

代码说明 #

1. 读取CSV文件并拆分成行，过滤掉空行；
2. 从第一行提取主标题，第二行提取年份表头；
3. 遍历每一行数据，构建包含主标题和对应年份数据的对象，将字符串类型的数值转换为浮点数；
4. 最终返回符合预期格式的Object数组。

内容的提问来源于stack exchange，提问作者Diego Filastro