如何用原生JS或SQL实现CSV导入所需的数据透视格式转换

场景说明 #

CSV导入流程存在格式适配要求：源文件为宽表结构，时间周期以独立列存在，需转换为时间维度单独成列的长表结构，才能匹配导入程序的格式规范。

• 源宽表结构：

• 目标长表结构：

当前运行环境仅支持原生JavaScript，无法引入jQuery或其他第三方库；数据写入暂存区后支持SQL二次加工，两种技术路径均可落地。现有CSVToArray函数可将CSV文本解析为二维数组，代码如下：

function CSVToArray(strData, strDelimiter) {
  // 未指定分隔符时默认用逗号
  strDelimiter = strDelimiter || ",";

  // 构造正则解析CSV字段
  var objPattern = new RegExp(
    "(\\" +
      strDelimiter +
      "|\\r?\\n|\\r|^)" +
      // 匹配带引号字段
      '(?:"([^"]*(?:""[^"]*)*)"|' +
      // 匹配普通字段
      '([^"\\' +
      strDelimiter +
      "\\r\\n]*))",
    "gi"
  );

  // 初始化结果数组，默认带一个空行
  var arrData = [[]];
  var arrMatches = null;

  // 循环匹配直到无结果
  while ((arrMatches = objPattern.exec(strData))) {
    var strMatchedDelimiter = arrMatches[1];

    // 匹配到行分隔符时新增空行
    if (strMatchedDelimiter.length && strMatchedDelimiter !== strDelimiter) {
      arrData.push([]);
    }

    var strMatchedValue;
    if (arrMatches[2]) {
      // 处理带引号的值，转义内部双引号
      strMatchedValue = arrMatches[2]
        .replace(new RegExp('""', "g"), '"')
        .replace('"', "");
    } else {
      // 处理无引号普通值
      strMatchedValue = arrMatches[3];
    }
    // 值写入当前行
    arrData[arrData.length - 1].push(strMatchedValue);
  }
  return arrData;
}

方案1：原生JavaScript转换（解析后直接处理） #

在CSV解析为二维数组后直接完成宽转长，不依赖数据库SQL能力，可自动适配任意数量的时间周期列，代码如下：

/**
 * 宽表二维数组转长表结构
 * @param {Array} csvArr - CSVToArray输出的宽表二维数组
 * @param {number} fixedColNum - 固定维度列数量，当前场景为2（Account、Department）
 * @returns {Array} 长表格式二维数组
 */
function wideToLong(csvArr, fixedColNum) {
  fixedColNum = fixedColNum || 2;
  const [header, ...rows] = csvArr;
  // 拆分固定列和周期列
  const fixedColHeader = header.slice(0, fixedColNum);
  const periodList = header.slice(fixedColNum);
  // 构造长表表头
  const result = [[...fixedColHeader, 'Period', 'Amount']];

  // 遍历每行数据展开
  rows.forEach(row => {
    const fixedColVals = row.slice(0, fixedColNum);
    // 每个周期生成一条独立记录
    periodList.forEach((period, index) => {
      result.push([
        ...fixedColVals,
        period,
        row[fixedColNum + index]
      ])
    })
  })
  return result;
}

// 调用示例
const csvText = `Account,Department,Jan2022,Feb2022,Mar2022\n12345,Sales,$456,$876,$345\n98765,HR,$765,$345,$344`;
const wideArr = CSVToArray(csvText);
const longArr = wideToLong(wideArr, 2);
// longArr可直接转为CSV字符串或传入后续导入逻辑

如果后续固定列数量调整，只需要修改传入的fixedColNum参数即可。

方案2：SQL转换（暂存区二次加工） #

如果选择将宽表数据原样导入暂存表（假设表名为staging_csv_wide，字段与宽表一一对应），可通过SQL完成列转行。

通用写法（所有支持SQL的数据库均兼容） #

通过UNION ALL拼接每个周期的查询结果，逻辑简单无语法兼容问题：

SELECT Account, Department, 'Jan2022' AS Period, Jan2022 AS Amount FROM staging_csv_wide
UNION ALL
SELECT Account, Department, 'Feb2022' AS Period, Feb2022 AS Amount FROM staging_csv_wide
UNION ALL
SELECT Account, Department, 'Mar2022' AS Period, Mar2022 AS Amount FROM staging_csv_wide;

简化写法（支持UNPIVOT语法的数据库可用） #

如果使用SQL Server、Oracle、BigQuery等支持UNPIVOT语法的数据库，可写得更简洁：

SELECT Account, Department, Period, Amount
FROM staging_csv_wide
UNPIVOT(
  Amount FOR Period IN (Jan2022, Feb2022, Mar2022)
) AS t;

选型参考 #

• 每次导入的时间周期列不固定时，优先选JS方案：可自动从表头识别所有周期列，无需手动维护列名清单，适配成本低
• 时间周期列固定、团队更熟悉SQL维护时，选SQL方案即可，逻辑直观调试方便

内容的提问来源于stack exchange，提问作者Travis Fulgham