Powershell comparing 2 csv files

Question

I'm trying to compare 2 files that both contain email addresses. For example text1.csv has 800 addresses and text2.csv has 200. Most of the email addresses in text2.csv are in text1.csv but not all of them. I need to export all the email addresses from text2.csv that are NOT in text1.csv. The property on both files is email

I tried something like this but I dont get the correct outcome:

Compare-Object -ReferenceObject (Get-Content -Path C:\scripts\test1.csv) -DifferenceObject (Get-Content -Path C:\scripts\test2.csv) | Where-Object{ $_.SideIndicator -eq "=>" }

    

I also tried this without result

$file2 | Where-Object { $_.email -NotIn $file1 }

Answer 1

• By using Get-Content, you end up comparing whole lines from your input files.

• In order to compare column values from CSV files, you must first convert your input files to objects, using Import-Csv, whose properties (reflecting the column values) you can then compare; in your case, the .email property:

Compare-Object -PassThru (Import-Csv C:\scripts\test1.csv).email `
                         (Import-Csv C:\scripts\test2.csv).email | 
  Where-Object{ $_.SideIndicator -eq '=>' }

Note:

• Compare-Object's -PassThru switch directly passes the differing objects through, decorated with a .SideIndicator ETS property, instead of wrapping them in a [pscustomobject] wrapper whose .InputObject contains the respective original object.

• Even though, with multiple data rows in the CSV, Import-Csv returns an array of objects, you can access .email directly on that array in order to get the property values of all elements in the array, which is a convenient feature known as member-access enumeration.

• For brevity, I'm using positional arguments above (e.g., C:\scripts\test1.csv instead of -Path C:\scripts\test1.csv).

Answer 2

I'd probably do something like this:

$emails = (Import-Csv -Path 'X:\file1.csv').email
$result = Import-Csv -Path 'X:\file2.csv' |
          Where-Object { $emails -notcontains $_.email }

# output to new file
$result | Export-Csv -Path 'X:\missingEmails.csv' -NoTypeInformation