比较两个CSV，匹配2个或更多列上的列，使用Powershell从两个csv导出特定列 [英] Compare Two CSVs, match the columns on 2 or more Columns, export specific columns from both csvs with powershell

问题描述

我有2个CSV

left.csv

Ref_ID,First_Name,Last_Name,DOB
321364060,User1,Micah,11/01/1969
946497594,User2,Acker,05/28/1960
887327716,User3,Aco,06/26/1950
588496260,User4,John,05/23/1960
565465465,User5,Jack,07/08/2020

right.csv

right.csv

First_Name,Last_Name,DOB,City,Document_Type,Filename
User1,Micah,11/01/1969,Parker,Transcript,T4IJZSYO.pdf
User2,Acker,05/28/1960,,Transcript,R4IKTRYN.pdf
User3,Aco,06/26/1950,,Transcript,R4IKTHMK.pdf
User4,John,05/23/1960,,Letter,R4IKTHSL.pdf

最终结果:

Combined.csv

Combined.csv

Ref_ID,First_Name,Last_Name,DOB,Document_Type,Filename
321364060,User1,Micah,11/01/1969,Parker,Transcript,T4IJZSYO.pdf
946497594,User2,Acker,05/28/1960,Transcript,R4IKTRYN.pdf
887327716,User3,Aco,06/26/1950,Transcript,R4IKTHMK.pdf
588496260,User4,John,05/23/1960,Letter,R4IKTHSL.pdf

我需要在First_Name，Last_Name，DOB上进行匹配，然后从左侧返回Ref_ID，first_name，last_name，DOB.csv和从右侧返回Document_Type，Filename.csv

I need to match them on First_Name,Last_Name,DOB then return Ref_ID, first_name, last_name, DOB from the left.csv and Document_Type,Filename from the right.csv

使用Compare-Object:仅返回其中一个csv的列，而不返回两个中的列.

Use Compare-Object: that only returns columns from one of the csvs, not columns from both.

使用联接对象:这真是我的伟大希望，但是那只能让我在一个列上进行匹配，我需要匹配多个列(不能弄清楚如何做多个)

Use join-object: This was my great hope, but that only lets me match on one Column, I need to match multiple Columns (cant figure out how to do Multiple)

我不确定从这里去哪里，欢迎提出建议.

Im not sure where to go from here, open to suggestions.

推荐答案

$left = Import-Csv C:\left.csv
$right = Import-Csv C:\right.csv

Compare-Object -ReferenceObject $left -DifferenceObject $right -Property First_Name,Last_Name,DOB -IncludeEqual -ExcludeDifferent | 
    ForEach-Object {
        $iItem = $_
        $ileft = $left.Where({$_.First_Name -eq $iItem.First_Name -and $_.Last_Name -eq $iItem.Last_Name -and$_.DOB -eq $iItem.DOB})
        $iright = $right.Where({$_.First_Name -eq $iItem.First_Name -and $_.Last_Name -eq $iItem.Last_Name -and$_.DOB -eq $iItem.DOB})
        [pscustomobject]@{
            Ref_ID=$ileft.Ref_ID
            first_name=$ileft.first_name
            last_name=$ileft.last_name
            DOB=$ileft.DOB
            Document_Type=$iright.Document_Type
            Filename=$iright.Filename
        }
    } | Export-Csv C:\Combined.csv -NoTypeInformation

这篇关于比较两个CSV，匹配2个或更多列上的列，使用Powershell从两个csv导出特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！