使用Windows PowerShell合并CSV文件并生成含差异列的输出文件

需求说明 #

现有两个存储参数数值的CSV文件，内容如下：

File1 #

Entity Account Amount
100    1001    $100
100    1004    $300
101    1002    $200

File2 #

Entity Account Amount
100    1001    $100
100    1004    $200
101    1002    $200
101    1005    $500

需要生成一个新CSV文件，包含File1金额列、File2金额列及两者差值的差异列，目标输出格式如下：

目标输出文件 #

Entity Account Amount as per file1  Amount as per file2 Variance Amount
100    1001    $100                  $100                 0 
100    1004    $300                  $200                 $100
101    1002    $200                  $200                 0  
101    1005    $0  (blank record)    $500                 ($500)

本人是PowerShell脚本新手，已知Compare-Object可查找两个CSV的唯一/差异记录，曾尝试以下命令获取唯一记录：

PS C:\Users\Daman> compare-object (Get-Content C:\File1.csv) (Get-Content C:\File2.csv)

但希望实现合并文件并生成上述格式的差异文件，求指导。

实现步骤 #

1. 读取CSV文件 #

用Import-Csv把CSV转换成PowerShell对象，方便后续处理：

$file1 = Import-Csv -Path "C:\File1.csv" -Delimiter " "  # 示例是空格分隔，实际如果是逗号就改成","
$file2 = Import-Csv -Path "C:\File2.csv" -Delimiter " "

2. 收集所有唯一的Entity+Account组合 #

确保覆盖两个文件里的所有记录组合，避免遗漏：

$allPairs = ($file1 | Select-Object Entity, Account) + ($file2 | Select-Object Entity, Account) | 
            Sort-Object Entity, Account -Unique

3. 遍历组合生成差异记录 #

对每个组合分别匹配两个文件的记录，提取金额并计算差值：

$results = foreach ($pair in $allPairs) {
    # 匹配两个文件的对应记录
    $record1 = $file1 | Where-Object { $_.Entity -eq $pair.Entity -and $_.Account -eq $pair.Account }
    $record2 = $file2 | Where-Object { $_.Entity -eq $pair.Entity -and $_.Account -eq $pair.Account }

    # 处理缺失记录的金额显示
    $amount1 = $record1 ? $record1.Amount : '$0  (blank record)'
    $amount2 = $record2 ? $record2.Amount : '$0  (blank record)'

    # 转换金额为数字计算差值
    $num1 = $record1 ? [double]$record1.Amount.Replace('$','') : 0
    $num2 = $record2 ? [double]$record2.Amount.Replace('$','') : 0
    $variance = $num1 - $num2

    # 格式化差值显示：正数加$，负数用($)包裹，0显示为0
    $varianceStr = switch ($variance) {
        0 { '0' }
        { $_ -gt 0 } { "`$$_" }
        default { "(`$$([math]::Abs($_)))" }
    }

    # 构造结果对象
    [PSCustomObject]@{
        'Entity' = $pair.Entity
        'Account' = $pair.Account
        'Amount as per file1' = $amount1
        'Amount as per file2' = $amount2
        'Variance Amount' = $varianceStr
    }
}

4. 导出为目标CSV #

把结果输出成指定格式的CSV文件：

$results | Export-Csv -Path "C:\Output.csv" -NoTypeInformation -Delimiter " "

完整脚本 #

把上述步骤整合后的完整代码：

# 读取CSV文件
$file1 = Import-Csv -Path "C:\File1.csv" -Delimiter " "
$file2 = Import-Csv -Path "C:\File2.csv" -Delimiter " "

# 获取所有唯一的Entity+Account组合
$allPairs = ($file1 | Select-Object Entity, Account) + ($file2 | Select-Object Entity, Account) | 
            Sort-Object Entity, Account -Unique

# 生成差异记录
$results = foreach ($pair in $allPairs) {
    $record1 = $file1 | Where-Object { $_.Entity -eq $pair.Entity -and $_.Account -eq $pair.Account }
    $record2 = $file2 | Where-Object { $_.Entity -eq $pair.Entity -and $_.Account -eq $pair.Account }

    $amount1 = $record1 ? $record1.Amount : '$0  (blank record)'
    $amount2 = $record2 ? $record2.Amount : '$0  (blank record)'

    $num1 = $record1 ? [double]$record1.Amount.Replace('$','') : 0
    $num2 = $record2 ? [double]$record2.Amount.Replace('$','') : 0
    $variance = $num1 - $num2

    $varianceStr = switch ($variance) {
        0 { '0' }
        { $_ -gt 0 } { "`$$_" }
        default { "(`$$([math]::Abs($_)))" }
    }

    [PSCustomObject]@{
        'Entity' = $pair.Entity
        'Account' = $pair.Account
        'Amount as per file1' = $amount1
        'Amount as per file2' = $amount2
        'Variance Amount' = $varianceStr
    }
}

# 导出结果
$results | Export-Csv -Path "C:\Output.csv" -NoTypeInformation -Delimiter " "

内容的提问来源于stack exchange，提问作者Damanjot Singh