如何用PowerShell按Lun_Number分组CSV数据并添加计数列？

需求与数据 #

原始CSV数据 #

"Path_Name","Lun_Number","status"
"vmhba0:C2:T0:L1","1","active"
"vmhba0:C1:T0:L1","1","active"
"vmhba1:C0:T7:L230","230","active"
"vmhba1:C0:T7:L231","231","active"
"vmhba1:C0:T7:L232","230","active"
"vmhba1:C0:T7:L235","231","active"
"vmhba1:C0:T7:L236","230","active"

期望输出 #

需要新增Count列，统计每个Lun_Number的出现次数，仅在每组第一行显示数值，其余行留空：

"Path_Name","Lun_Number","status","Count"
"vmhba0:C2:T0:L1","1","active",2
"vmhba0:C1:T0:L1","1","active",
"vmhba1:C0:T7:L230","230","active",3
"vmhba1:C0:T7:L231","230","active",
"vmhba1:C0:T7:L232","230","active",
"vmhba1:C0:T7:L235","231","active",2
"vmhba1:C0:T7:L236","231","active",

尝试过的代码（未成功） #

$status_csv = Import-Csv -Path E:\pathstate.csv
$path_csv = Import-Csv -Path E:\PathInfo.csv

foreach($row in $path_csv)
{   
    $path_1 = $row.Path_Name
    $path_2 = $status_csv | where{$_.Name -match "^$path_1$" }
    [PsCustomObject]@{
    Path_Name = $path_1
    Lun_Number = $row.Lun_Number
    status = $path_2.PathState
    } | Export-Csv -Path E:\FinalReport.csv -NoTypeInformation -Append | Group-Object Lun_Number
    
}

解决方案 #

原代码的问题在于Group-Object放在Export-Csv之后，而Export-Csv不会将对象传递到管道，导致分组逻辑无效。正确流程是先合并数据、处理分组计数，最后导出：

# 导入原始CSV文件
$status_csv = Import-Csv -Path E:\pathstate.csv
$path_csv = Import-Csv -Path E:\PathInfo.csv

# 合并两个CSV的数据，先存到变量中（避免边循环边导出无法后续处理）
$mergedData = foreach ($row in $path_csv) {
    $pathMatch = $status_csv | Where-Object { $_.Name -eq $row.Path_Name }
    [PsCustomObject]@{
        Path_Name   = $row.Path_Name
        Lun_Number  = $row.Lun_Number
        status      = $pathMatch.PathState
    }
}

# 按Lun_Number分组，为每组添加Count列（仅第一行显示计数）
$finalData = $mergedData | Group-Object -Property Lun_Number | ForEach-Object {
    $groupCount = $_.Count
    $index = 0
    foreach ($item in $_.Group) {
        [PsCustomObject]@{
            Path_Name   = $item.Path_Name
            Lun_Number  = $item.Lun_Number
            status      = $item.status
            Count       = if ($index -eq 0) { $groupCount } else { $null }
        }
        $index++
    }
}

# 导出最终结果到CSV
$finalData | Export-Csv -Path E:\FinalReport.csv -NoTypeInformation

关键说明 #

1. 合并数据到变量：先将两个CSV的合并结果存入$mergedData，而非直接导出，确保能对完整数据集进行分组处理。
2. 精确匹配路径：用-eq代替原代码的-match，避免路径中的特殊字符（如:、.）触发正则匹配错误，保证路径精确匹配。
3. 分组计数逻辑：通过Group-Object按Lun_Number分组后，遍历每组条目，仅在第一个条目填充计数，其余条目留空，完全符合期望输出格式。

内容的提问来源于stack exchange，提问作者Empty Coder