如何使用PowerShell对比两个CSV文件并合并生成指定格式的第三个文件

需求说明 #

现有两个CSV格式的文本文件file1.txt和file2.txt，需要按Name字段关联两个文件内容，输出新文件file3.txt。输出要求保留file1.txt的全部字段，新增PresentTo字段：同Name对应的多个主机名用分号拼接，无匹配项时PresentTo字段留空。

示例文件内容 #

file1.txt #

"Name","RcopyGroup","State","Type","Prov","UsrCPG","VSize_MB","Usr_Rsvd_MB","Usr_Used_Perc","Usr_Used_MB","Snp_Rsvd_MB","Snp_Used_Perc"
".srdata","-","normal","base","full","-","81920","81920","100.0","81920","0","0.0"
"admin","-","normal","base","full","-","12288","12288","100.0","12288","0","0.0"
"LUN1","-","normal","base","tpvv","SSD_CPG","1331200","1159552","85.6","1139240","17408","0.0"
"LUN2","-","normal","base","tpvv","FC_CPG","122880","82944","52.3","64304","17408","0.0"
"LUN3","-","normal","base","tpvv","SSD_CPG","71680","56320","73.7","52861","1024","0.0"
"LUN4","-","normal","base","tpvv","FC_CPG","40960","33792","77.9","31922","1024","0.0"
"LUN5","-","normal","base","tpvv","FC_CPG","10240","11264","99.7","10209","1024","0.0"

file2.txt #

"Name","LunID","PresentTo","vvWWN"
"LUN1","10","Host1","60002AC0000000000000004400015537"
"LUN1","10","Host3","60002AC0000000000000004400015537"
"LUN1","110","Host2","60002AC0000000000000004400015537"
"LUN1","209","Host5","60002AC0000000000000004400015537"
"LUN1","110","Host4","60002AC0000000000000004400015537"
"LUN2","111","Host2","60002AC000000000080019FC000153AF"
"LUN3","110","Host2","60002AC000000000080019FD000153AF"
"LUN4","210","Host2","60002AC0000000000A004BC500015537"
"LUN5","211","Host2","60002AC0000000000A004BC700015537"
"LUN4","210","Host5","60002AC0000000000A004BC500015537"
"LUN5","211","Host5","60002AC0000000000A004BC700015537"

预期输出file3.txt #

"Name","RcopyGroup","State","Type","Prov","UsrCPG","VSize_MB","Usr_Rsvd_MB","Usr_Used_Perc","Usr_Used_MB","Snp_Rsvd_MB","Snp_Used_Perc","PresentTo"
".srdata","-","normal","base","full","-","81920","81920","100.0","81920","0","0.0",""
"admin","-","normal","base","full","-","12288","12288","100.0","12288","0","0.0",""
"LUN1","-","normal","base","tpvv","SSD_CPG","1331200","1159552","85.6","1139240","17408","0.0","Host1;Host3;Host2;Host5;Host4"
"LUN2","-","normal","base","tpvv","FC_CPG","122880","82944","52.3","64304","17408","0.0","Host2"
"LUN3","-","normal","base","tpvv","SSD_CPG","71680","56320","73.7","52861","1024","0.0","Host2"
"LUN4","-","normal","base","tpvv","FC_CPG","40960","33792","77.9","31922","1024","0.0","Host2;Host5"
"LUN5","-","normal","base","tpvv","FC_CPG","10240","11264","99.7","10209","1024","0.0","Host2;Host5"

实现代码 #

# 导入两个源文件
$file1 = Import-Csv -Path "file1.txt"
$file2 = Import-Csv -Path "file2.txt"

# 构建Name到合并后PresentTo的映射表，自动去重重复主机名
$hostMapping = @{}
$file2 | Group-Object -Property Name | ForEach-Object {
    $uniqueHosts = $_.Group.PresentTo | Select-Object -Unique
    $hostMapping[$_.Name] = $uniqueHosts -join ";"
}

# 遍历file1每行，追加PresentTo字段
$outputData = $file1 | ForEach-Object {
    $presentToValue = $hostMapping.ContainsKey($_.Name) ? $hostMapping[$_.Name] : ""
    $_ | Add-Member -MemberType NoteProperty -Name "PresentTo" -Value $presentToValue -PassThru
}

# 导出结果到file3.txt，保留标准CSV引号格式
$outputData | Export-Csv -Path "file3.txt" -NoTypeInformation -Encoding UTF8

说明 #

• 代码使用Group-Object按Name字段分组file2的记录，自动去重同一Name下的重复主机名，避免拼接重复值
• 无匹配Name的记录自动填充空字符串到PresentTo字段，符合输出要求
• 导出时使用标准CSV格式，字段自动保留双引号，无需额外处理格式

内容的提问来源于stack exchange，提问作者Faizal Izham