如何 - 在 Power Automate 中处理 CSV

微软竟然没有创建一个开箱即用的操作来处理 csv 文件（或者 .xls 文件😣），这对我来说仍然很不可思议。好在有几种方法可以实现，实际上有四种，有的很酷，有的很简单，有的很疯狂，但都很有趣（嗯，我觉得很有趣😎），所以我想展示一下：

普拉姆塞尔
流动
Office 脚本
数据流

Plumsail（昂贵的方式）

Plumsail 提供付费连接器来处理任何 csv Plumsail 处理 csv，但显然需要付费，而且这很无聊，所以很快就继续下去。

流动（疯狂的方式）

所以，csv 只是一个长字符串，带有行和列的分隔符。行用 \r\n 标识（大多数情况下，虽然有些只是 \n），列用逗号 (,) 标识（逗号分隔的值，它在名称中就有 😎 ）。因此，我们的流程将拆分成行，然后再拆分成列，但我们需要解决一些问题：

获取列标题
处理值中的逗号

最好的展示方式是按照流程走一遍：

首先我们声明一些变量

aHeaders = 这是我们将用列标题填充的数组
rIndex = 记录我们正在处理的当前行
cIndex = 记录我们正在处理的当前列
aRows = 保存转换后的行数据的数组
oRow = 在添加到 aRows 之前，我们用来构建行的对象

在变量之后，我们需要抓取 csv 文件并拆分行。我们使用以下表达式：

split(outputs('Get_file_content')?['body'],decodeUriComponent('%0D%0A'))

%0D%0A 是 \r\n 的编码版本

我们使用 Do until 来检查 rIndex 是否匹配，以了解所有行何时完成（最后总会有一个空行，所以我们减 1 行）。这也可以很容易地用 Apply_to_each 来实现，我只是对 Do_Untils 情有独钟。

我们使用 interationindex 来检查它是否是第一行，如果是，我们用逗号分隔，然后添加到 aHeaders 数组。

对于主行来说，情况稍微复杂一些。我们将再次拆分行，然后循环遍历新的列数组。对于每一列，我们使用 AddProperty 表达式，通过列索引查找列名（来自 aHeaders）和列值。

addProperty(variables('oRow'),
    variables('aHeaders')[iterationIndexes('Do_until_columns')]
  ,
    outputs('SplitColumns')[iterationIndexes('Do_until_columns')]
  )

当我们 addProperty 时，我们实际上创建了原始对象的副本并添加了属性。因此，我们现在需要用 compose 中的值来更新 oRow 变量。

最后我们将 oRow 对象附加到 aRows 数组。

输入输出

但是你有没有发现我故意犯的错误……目前我还没找到处理值中包含逗号的 CSV 文件的方法。以前 Power Automate 是这样返回 CSV 文件的：

""David","1","TRUE""
因此我们可以在“”上进行拆分，但现在它会返回，
"David,1,TRUE"
因此无法将值中的逗号识别为逗号分隔符。

Office 脚本（很酷的方式）

我们喜欢一些专业的代码，好消息是微软已经为我们做好了（https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv），但这只能转换成 Excel 格式，如果你想转换成 JSON 格式在流程中使用，该怎么办呢？不久前，我在这篇博客《每个 Power Automate 开发人员都应该知道的 5 个脚本》中创建了一个脚本，但这需要你在列中进行硬编码，如果你想让它们动态化（即适用于每个 csv 文件），该怎么办呢？

问题是 Office Scripts 基于 TypeScript（因此每个对象结构都必须声明），而他们已经禁止了所有 TypeScript 脚本（所以我们甚至无法使用 TypeScript 自身的解决方法）。幸运的是，有一种方法，那就是将我们自己的 JSON 数组构建为字符串，然后使用 Power Automate 将其转换回 JSON。

function main(workbook: ExcelScript.Workbook, csv: string) {
    let sJson: string = "[";
    let aHeaders: string[] = []
    csv = csv.replace(/\r/g, "");
    let rows = csv.split("\n");
    const csvRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
    rows.forEach((value, index) => {
        let rIndex=index;
        if (value.length > 0) {
            let row = value.match(csvRegex);

            if (row[0].charAt(0) === ',') {
                row.unshift("");
            }
            if (index != 0) { sJson += "{" }

            row.forEach((cell, index) => {
                row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
                if (rIndex == 0) {
                    aHeaders.push(row[index] .toString())
                } else {
                  if (Number(row[index])){
                    sJson += '"' + aHeaders[index] + '":' + row[index] + ','
                  } else if (row[index] == "TRUE" || row[index] == "FALSE"){
                    sJson += '"' + aHeaders[index] + '":' + row[index].toLowerCase() + ','
                  }else{
                    sJson += '"' + aHeaders[index] + '":"' + row[index].trim()  + '",'
                  }
                }
            });

            if (index != 0) {
                sJson = sJson.substring(0, sJson.length - 1);
                sJson += "},"
            }
        }
    });
    sJson = sJson.substring(0, sJson.length - 1);
    sJson += "]";
    return (sJson);
}

该划分基于 Microsoft 的划分，但我们做了一些更改：

首先，我们声明一个名为 sJson 的字符串变量，并将其设置为 '[' ，打开我们的数组。如果是第一行（即标题），我们将它添加到一个名为 aHeaders 的单独数组中。
在下一行，我们打开我们的对象if (index != 0) { sJson += "{" }，然后循环遍历每一列，从 aHeaders 数组中添加相应的值sJson += '"' + aHeaders[index] + '":"' + row[index].trim() + '",'：

为了确保我们将任何数字/布尔值从字符串转换为数字/布尔值，我们添加了一些逻辑，因此对于每一行我们最终得到：

if (rIndex == 0) {
    aHeaders.push(row[index] .toString())
} else {
  if (Number(row[index])){
    sJson += '"' + aHeaders[index] + '":' + row[index] + ','
  } else if (row[index] == "TRUE" || row[index] == "FALSE"){
    sJson += '"' + aHeaders[index] + '":' + row[index].toLowerCase() + ','
  }else{
    sJson += '"' + aHeaders[index] + '":"' + row[index].trim()  + '",'
  }
}

最后我们进行关闭操作，删除最后一个逗号sJson = sJson.substring(0, sJson.length - 1);，然后关闭对象sJson += "},"。处理完所有行后，我们重复此操作关闭数组，但将 '}' 替换为 ']'sJson += "]";

简要说明一下，Office 脚本将数组作为字符串返回，因此我们使用 json() 表达式将其转换回 json

数据流（简单方法）

数据流很酷，但仍然未被充分利用（长期以来它们都无法感知解决方案，这很不方便）。我之前在这里写过一篇完整的博客，但简而言之，它就是 Power Query（与 Excel 和 Power BI 中的功能完全相同）。因此，我们可以使用一个漂亮的用户界面将文本文件（也就是 CSV 文件）转换为合适的数据。

要创建一个，请前往 make.powerapps.com 并从左侧菜单中选择数据流，然后创建。