CODEWITHSUNDEEP
powershellcsvcomparepowershell-2.0powershell-4.0
jdripper1991
jdripper1991

Reputation: 1

Powershell script to compare csv rows

I have two csv files with following columns

Csv1

Name| address| phone 
John| Blah | Blah 
Dave| Blah | Blah 
Rick| Blah | Blah

Csv2

Name |address| phone 
John| Blah| Blah 
Dave| Blah| Blah

I want unique rows from first csv

What i want

Name | address| phone 
Rick | Blah   | Blah

My code

$csv1 = import-csv <file> $csv2 = import-csv <file>
$output = $csv1 | where-object {$_.name -notin $csv2.Name -and $_.address -notin $csv2.address -and $_.phone -notin $csv2.phone}

Upvotes: 0

Views: 2694

Answers (1)

Sage Pourpre
Sage Pourpre

Reputation: 10333

Your statement | where-object {$_.name -notin $csv2.Name -and $_.address -notin $csv2.address -and $_.phone -notin $csv2.phone}

The logic when evaluating Rick's row

  • Is Rick a name that is in $Csv2 ? (Answer:No)
  • Is Blah an address in $Csv2 ? (Answer: Yes)
  • Is Blah a phone number in $Csv2 ? (Answer: Yes)

You are evaluating every single row against all of the rows of $csv2 so when you check Rick's address ($_.address -notin $csv2.address), it is actually in $Csv2 for John and Dave. Based on how you formulated your condition, that does eliminate Rick from the unique rows.

You can use Compare-Object to compare both csv files and find items that aren't a match. Here is a demonstration comparing both csv using the 3 properties as criterias.

Compare-Object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property Name,Address,phone  | Where-Object SideIndicator -eq '<='

Complete self-contained example

$Csv1 = @'
Name|address|phone
John|Blah|Blah
Dave|Blah|Blah
Rick|Blah|Blah
'@ | ConvertFrom-Csv -Delimiter '|'


$csv2 = @'
Name|address|phone
John|Blah|Blah
Dave|Blah|Blah
'@ | ConvertFrom-Csv -Delimiter '|'


$Results = Compare-Object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property Name,Address,phone  | Where-Object SideIndicator -eq '<='

$Results | Select Name,Address,Phone

Output

Name Address phone
---- ------- -----
Rick Blah    Blah

Upvotes: 2

Related Questions