How to compare two CSV files with different header rows and remove duplicates from the first one?

user_12841198 0
Viewed 759

I have two CSV files. The first one contains the following headers and data:

Name,Email,OfficePhone
Bill,Bill@jump.com,123-456-7890

The second one contains just:

primaryEmail
Bill@jump.com

I would like to compare the two and remove any duplicate rows from the first file where the email in the first file exists in the second one. I'm trying to use compare-object, but not sure where to go from here.

$File1 = Import-Csv C:\it\newuser.csv
$File2 = Import-Csv C:\it\email.csv

Compare-Object $File1 $File2 -Property email
csv powershell
2 Answers

The easiest way is probably just fetching the primary emails first from the 2nd CSV, then using Where-Object to filter out duplicate rows with duplicate emails from the 1st CSV.

# Get primary emails from 2nd CSV
$csv2 = (Import-Csv -Path .\2.csv).primaryEmail

# Remove rows from 1st csv that don't have an email in $csv2
$removedDuplicateRows = Import-Csv -Path .\1.csv | Where-Object {$_.Email -notin $csv2}

# Export filtered rows into output.csv
$removedDuplicateRows | Export-Csv -Path .\output.csv -NoTypeInformation

If your header column has quotes around it, then you need to include those quotes (e.g. .primaryEmail becomes ."primaryEmail").

If you're using PowerShell 7, you can use -UseQuotes Never from Export-Csv to not include quotes in your output CSV.

user_52389150

RoadRunner's helpful answer offers an effective solution.

However, with large input sets performance can become a concern, because a linear search of the array of email addresses ($csv2) is performed for every CSV input row.

Using System.Collections.Generic.HashSet<T> offers a solution, because lookups in hash sets are consistently fast.

The System.Linq.Enumerable.ToHashSet() method offers a convenient way to construct such a hash set from an object implementing System.Collections.Generic.IEnumerable<T>, such as an array.

# Build a case-insensitive hash set of email addresses from $File2 
# whose elements are to be excluded from $File1.
# Note that the cast to [string[]] is required in order for PowerShell
# to find the right generic method overload.
$refEmailsHashSet = [Linq.Enumerable]::ToHashSet(
  [string[]] (Import-Csv $File2).primaryEmail,
  [StringComparer]::CurrentCultureIgnoreCase
)

# Import $File1 and filter out the email addresses from $File2
# Pipe to `Export-Csv -NoTypeInformation -Encoding ...` to save to a new CSV file.
Import-Csv $File1 | Where-Object { -not $refEmailsHashSet.Contains($_.Email) }

An additional - somewhat obscure - way to improve performance is to work around the inefficient implementation of the Where-Object (and ForEach-Object) cmdlet, as of PowerShell 7.2, as discussed in GitHub issue #10982

# Works the same as above, with faster alternative to Where-Object
Import-Csv $File1 | 
  & { process { if (-not $refEmailsHashSet.Contains($_.Email)) { $_ } } }

Note that this independent optimization, which can equally be applied to the linear-lookup -notin solution in RoadRunner's answer, has the largest impact on performance overall.

user_453750
Related