将 CSV 文件转换为 Excel 工作簿

项目
02/07/2024

许多服务将数据导出为逗号分隔值 (CSV) 文件。此解决方案以 .xlsx 文件格式自动将这些 CSV 文件转换为 Excel 工作簿的过程。它使用 Power Automate 流在 OneDrive 文件夹中查找扩展名为 .csv 的文件，并使用 Office 脚本将数据从 .csv 文件复制到新的 Excel 工作簿中。

解决方案

将 .csv 文件和空白“模板”.xlsx 文件存储在 OneDrive 文件夹中。
创建 Office 脚本以将 CSV 数据分析为某个范围。
创建 Power Automate 流以读取 .csv 文件并将其内容传递给脚本。

示例文件

下载 convert-csv-example.zip 以获取 Template.xlsx 文件和两个示例 .csv 文件。将文件提取到 OneDrive 中的文件夹中。此示例假定文件夹名为“output”。

将以下脚本添加到示例工作簿。在 Excel 中，使用 “自动化>新脚本” 粘贴代码并保存脚本。将其另存为 转换 CSV ，然后亲自尝试示例！

示例代码：将逗号分隔值插入工作簿

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters.
  csv = csv.replace(/\r/g, "");

  // Split each line into a row.
  // NOTE: This will split values that contain new line characters.
  let rows = csv.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);
    
      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }
  
      // Remove the preceding comma and surrounding quotation marks.
      row.forEach((cell, index) => {
        cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
        row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") === cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
      });
    
      // Create a 2D array with one row.
      let data: string[][] = [];
      data.push(row);
  
      // Put the data in the worksheet.
      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });

  // Add any formatting or table creation that you want.
}

Power Automate 流：创建新的 .xlsx 文件

登录到 Power Automate 并创建新的 计划云流。
将流设置为每隔“1”“天重复 ”，然后选择“ 创建”。
获取模板 Excel 文件。这是所有已转换 .csv 文件的基础。在流生成器中 + ，选择按钮和 “添加操作”。选择OneDrive for Business连接器的“获取文件内容”操作。提供“Template.xlsx”文件的文件路径。
- 文件：/output/Template.xlsx
重命名 “获取文件内容” 步骤。在操作任务窗格中选择当前标题“获取文件内容”。将名称更改为“获取 Excel 模板”。
添加一个操作，用于获取“output”文件夹中的所有文件。选择OneDrive for Business连接器的“在文件夹中列出文件”操作。提供包含 .csv 文件的文件夹路径。
- 文件夹：/output
添加条件，以便流仅对 .csv 文件运行。添加 “条件” 控件操作。对 Condition 使用以下值。
- 选择一个值：名称 (文件夹) 中的“列出文件” 中的 动态内容。请注意，此动态内容具有多个结果，因此 For each 控件括在 Condition 中。
- 以下拉列表中的 (结尾)
- 选择值：.csv
流的其余部分位于 “如果是 ”部分下，因为我们只想对 .csv 文件进行操作。通过添加使用OneDrive for Business连接器的“获取文件内容”操作的操作来获取单个 .csv 文件。使用文件夹中列出文件中动态内容的 ID。
- 文件： id (“ 列出文件夹中的文件 ”步骤) 中的动态内容
将新的 “获取文件内容 ”步骤重命名为“获取 .csv 文件”。这有助于将此文件与 Excel 模板区分开来。
使用 Excel 模板作为基内容制作新的 .xlsx 文件。添加使用 OneDrive for Business 连接器的“创建文件”操作的操作。使用以下值。
- 文件夹路径：/output
- 文件名：名称不带扩展名.xlsx (从“列出文件夹中的文件”中选择“名称”，并在) 后手动键入“.xlsx”
- 文件内容：从获取 Excel 模板) 的文件内容 (动态内容
运行脚本以将数据复制到新工作簿中。添加 Excel Online (Business) 连接器的 “运行”脚本 操作。对操作使用以下值。
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：从创建文件) (动态内容的 ID
- 脚本：转换 CSV
- csv：从获取 .csv 文件) (动态内容
保存流。流设计器应如下图所示。
使用流编辑器页上的“ 测试 ”按钮，或通过“我的流”选项卡运行流。请务必在出现提示时允许访问。
应在“输出”文件夹中找到新的 .xlsx 文件，以及原始 .csv 文件。新工作簿包含与 CSV 文件相同的数据。

疑难解答

脚本测试

若要在不使用 Power Automate 的情况下测试脚本，请在使用前为其 csv 分配一个值。添加以下代码作为函数的第一行， main 然后选择“ 运行”。

  csv = `1, 2, 3
         4, 5, 6
         7, 8, 9`;

分号分隔的文件和其他备用分隔符

某些区域使用分号 (';') 分隔单元格值，而不是逗号。在这种情况下，需要更改脚本中的以下行。

将逗号替换为正则表达式语句中的分号。这从 let row = value.match开始。
```
let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
```
将逗号替换为空白第一个单元格检查中的分号。这从 if (row[0].charAt(0)开始。
```
if (row[0].charAt(0) === ';') {
```
将逗号替换为行中的分号，从显示的文本中删除分隔字符。这从 row[index] = cell.indexOf开始。
```
   row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
```

注意

如果文件使用制表符或任何其他字符来分隔值，请将上述替换中的替换为 ;\t 或正在使用的任何字符。

大型 CSV 文件

如果文件包含数十万个单元格，则可以达到 Excel 数据传输限制。需要强制脚本定期与 Excel 同步。执行此操作的最简单方法是在处理一批行后调用 console.log 。添加以下代码行以执行此操作。

在之前 rows.forEach((value, index) => {，添加以下行。
```
  let rowCount = 0;
```
在之后 range.setValues(data);，添加以下代码。请注意，根据列数，可能需要减少 5000 到较小的数字。
```
  rowCount++;
  if (rowCount % 5000 === 0) {
    console.log("Syncing 5000 rows.");
  }
```

警告

如果 CSV 文件非常大，则可能在 Power Automate 中出现超时问题。在将 CSV 数据转换为 Excel 工作簿之前，需要将 CSV 数据划分为多个文件。

重音符和其他 unicode 字符

具有 unicode 特定字符的文件（例如重音元音，如 é）需要使用正确的编码进行保存。对于 .csv 文件，Power Automate 的 OneDrive 连接器文件创建默认为 ANSI。如果要在 Power Automate 中创建 .csv 文件，则需要在逗号分隔值之前 (BOM) 添加字节顺序标记。对于 UTF-8，请将写入 .csv 文件操作的文件内容替换为表达式 concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (其中 <CSV Input> 是原始 CSV 数据) 。

请注意，此示例不会在流中创建 .csv 文件，因此需要在流的自定义部分进行此更改。如果不控制这些文件的创建方式，也可以使用 BOM 读取和重写 .csv 文件。

周围引号

此示例删除任何引号 (“”) 周围的值。这些通常添加到逗号分隔值，以防止将数据中的逗号视为分隔标记。在 Excel 中打开并保存为 .xlsx 文件的 .csv 文件永远不会向读者显示这些引号。如果希望保留引号并将它们显示在最终电子表格中，请将脚本的第 27-30 行替换为以下代码。

// Remove the preceding comma.
row.forEach((cell, index) => {
  row[index] = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
});

通过