PowerShell如何将分组数组转为透视表并导出为Excel可打开的CSV

完整实现代码 #

# 原始示例数据
$data = @(
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service1';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service4';Propertyx=2;Price='4'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service4';Propertyx=2;Price='1'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service2';Propertyx=2;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service1';Propertyx=1;Price='17'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='13'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service1';Propertyx=1;Price='2'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service1';Propertyx=1;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='11'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service2';Propertyx=1;Price='12'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service3';Propertyx=1;Price='8'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service5';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service3';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service1';Propertyx=1;Price='3'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service2';Propertyx=1;Price='11'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='2';Id2=11112314;Service='Service3';Propertyx=1;Price='5'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service2';Propertyx=1;Price='4'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service4';Propertyx=1;Price='12'}
   [pscustomobject]@{Id1='1';Id2=51213412;Service='Service5';Propertyx=1;Price='8'}
   [pscustomobject]@{Id1='4';Id2=42112521;Service='Service1';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='3';Id2=12512521;Service='Service5';Propertyx=1;Price='7'}
   [pscustomobject]@{Id1='5';Id2=53252352;Service='Service1';Propertyx=1;Price='7'})

# 预处理：将Price字段从字符串转为数值，避免求和错误
$data | ForEach-Object { $_.Price = [int]$_.Price }

# 提取所有唯一的Service名称，作为透视表列名
$allServices = $data.Service | Select-Object -Unique | Sort-Object

# 按Id1、Id2分组，构建透视表行
$pivotTable = $data | Group-Object Id1, Id2 | ForEach-Object {
    $idInfo = $_.Name -split ', '
    # 初始化行对象，固定前两列为Id1、Id2
    $row = [PSCustomObject]@{
        Id1 = $idInfo[0]
        Id2 = $idInfo[1]
    }
    # 遍历所有服务，计算对应求和值，无匹配则为0
    foreach ($service in $allServices) {
        $serviceSum = ($_.Group | Where-Object { $_.Service -eq $service } | Measure-Object Price -Sum).Sum
        $row | Add-Member -MemberType NoteProperty -Name $service -Value $serviceSum
    }
    return $row
}

# 输出透视表到控制台查看
$pivotTable | Format-Table -AutoSize

# 导出为Excel可直接打开的CSV文件，使用UTF8无BOM编码避免乱码
$pivotTable | Export-Csv -Path "Service价格透视表.csv" -Encoding UTF8NoBOM -NoTypeInformation

注意事项 #

• 最终导出的Service价格透视表.csv可直接双击用Excel打开，行维度为Id1+Id2组合，列维度为所有出现过的Service名称，单元格值为对应组合的Price求和结果
• 如果需要将无消费记录的服务对应值改为空值而非0，可将$serviceSum的赋值逻辑替换为：

  $sumResult = ($_.Group | Where-Object { $_.Service -eq $service } | Measure-Object Price -Sum).Sum
  $serviceSum = $sumResult ? $sumResult : ''
  

• 针对超大型CSV文件，可先通过Import-Csv加载原始数据后复用上述逻辑，内存占用过高可替换为逐行流式处理方案。

内容的提问来源于stack exchange，提问作者Miha Žitko