개체 다중 값 비교 PowerShell

Question

제발, 여러 필드 열이있는 csv 파일을 처리하는 솔루션을 찾도록 도와 주시겠습니까?

File1.csv

Teams,Category,Members
Team1,A,Smith;Johnson
Team1,C,Jones;Miller;Garcia
Team3,E,Wilson;Martinez
Team4,A,Martin;Jackson;White;Williams

File2.csv

Teams,Category,Members
Team1,A,Smith;Johnson
Team2,C,Jones;Miller;Garcia
Team3,E,Wilson;Martinez;Gonzalez;Hall
Team4,A,Martin;Jackson;Williams

차이 :

1. 팀 3에 Gonzalez 및 Hall 추가
2. 팀 -4에서 흰색 제거

    $1 = Import-Csv -Path ".\File1.csv" -Delimiter ',' $2 = Import-Csv -Path ".\File2.csv" -Delimiter ','
    Compare-Object $1 $2 -Property Members -PassThru

결과 :

Teams Category Members                       SideIndicator
Team3 E        Wilson;Martinez;Gonzalez;Hall =>           
Team4 A        Martin;Jackson;Williams       =>           
Team3 E        Wilson;Martinez               <=           
Team4 A        Martin;Jackson;White;Williams <=           

예상되는 것 :

Teams Category  Members                       SideIndicator
Team3 E         Gonzalez and Hall              =>
Team4 A         White                          <=

Answer 1

먼저 개체를 비교하여 차이점을 찾은 다음 (다른 팀 구성원이 일치하는 경우 누락 된 항목을 피하기 위해 Teams와 Members라는 두 속성을 비교합니다) 일치하는 개체에서 생성 된 배열을 비교합니다.

$1 = Import-Csv -Path ".\File1.csv" -Delimiter ',' $2 = Import-Csv -Path ".\File2.csv" -Delimiter ','
$comparisonRes = Compare-Object $1 $2 -Property Teams,Members -PassThru foreach ($obj in $comparisonRes | Where-Object SideIndicator -eq "=>") { # $obj = ($comparisonRes | Where-Object SideIndicator -eq "=>")[0] $matchingEntry = $1 | Where-Object {$_.Teams -eq $obj.Teams} $matchingEntryMembers = $matchingEntry.Members -split ";" $currentEntryMembers = $obj.Members -split ";" $diffMembers = Compare-Object $matchingEntryMembers $currentEntryMembers
  
  # Uncomment to log
  # $diffMembers # Do something with $diffMembers here
}

Answer 2

배열과 숫자를 지원하는 csv 대신 json을 사용할 수 있습니다. 그렇지 않으면 팀은 두 개의 세미콜론으로 구분 된 문자열처럼 보입니다.

file1.json

[
  {"Teams":"Team1","Category":"A","Members":["Smith","Johnson"]},
  {"Teams":"Team1","Category":"C","Members":["Jones","Miller","Garcia"]},
  {"Teams":"Team3","Category":"E","Members":["Wilson","Martinez"]},
  {"Teams":"Team4","Category":"A","Members":["Martin","Jackson","White","Williams"]}
]

file2.json

[
 {"Teams":"Team1","Category":"A","Members":["Smith","Johnson"]}, 
 {"Teams":"Team2","Category":"C","Members":["Jones","Miller","Garcia"]}, 
 {"Teams":"Team3","Category":"E","Members":["Wilson","Martinez","Gonzalez","Hall"]}, 
 {"Teams":"Team4","Category":"A","Members":["Martin","Jackson","Williams"]}
]

$1 = cat file1.json | convertfrom-json $2 = cat file2.json | convertfrom-json
Compare-Object $1 $2 -Property Members -PassThru

Teams Category Members                            SideIndicator
----- -------- -------                            -------------
Team3 E        {Wilson, Martinez, Gonzalez, Hall} =>
Team4 A        {Martin, Jackson, Williams}        =>
Team3 E        {Wilson, Martinez}                 <=
Team4 A        {Martin, Jackson, White, Williams} <=

여기에 더 가까운 답변이 있습니다. 멤버에 대해 한 번에 한 줄만 비교 개체를 실행 한 다음 팀과 범주를 추가합니다.

$1 = cat file1.json | convertfrom-json $2 = cat file2.json | convertfrom-json

for($i = 0; $i -lt $1.length; $i++) {
  compare-object $1[$i].members $2[$i].members | 
    select @{n='Teams';    e={$1[$i].teams}},
           @{n='Category'; e={$1[$i].Category}},
           @{n='Members';  e={$_.inputobject}},
      sideindicator
}

Teams Category Members  SideIndicator
----- -------- -------  -------------
Team3 E        Gonzalez =>
Team3 E        Hall     =>
Team4 A        White    <=

다음은 zip 함수 PowerShell / CLI를 사용하는 또 다른 방법 입니다. 두 개체 목록 모두에 여러 배열이있는 "Foreach"루프입니다 .

$1 = cat file1.json | convertfrom-json
$2 = cat file2.json | convertfrom-json function Zip($a1, $a2) { # function allows it to stream while ($a1) {
        $x, $a1 = $a1 # $a1 gets the tail of the list
        $y, $a2 = $a2 [tuple]::Create($x, $y) } } zip $1 $2 | % { $whole = $_ # will lose this $_ in the select
  compare-object $whole.item1.members $whole.item2.members |
    select @{n='Teams';    e={$whole.item1.teams}}, @{n='Category'; e={$whole.item1.Category}},
      inputobject,sideindicator
}

Teams Category InputObject SideIndicator
----- -------- ----------- -------------
Team3 E        Gonzalez    =>
Team3 E        Hall        =>
Team4 A        White       <=