If you have two files, in this example we have 2 files and the goal here is that all the people in the filtered-list.csv do not need to be sent a email, but that file only contains a subset of users, when compared to the full-list.csv which contains everyone.
full-list.csv - A full list of people that should receive an email
already-sent.csv - A subset of people who do not need to be mailed
We therefore want Powershell to give us a new file that is called filtered-list.csv that contains the full-list.csv minus the already-sent.csv items - which you would usually need to do in other applications like Excel.
# Define the file paths
$fullListPath = "full-list.csv"
$alreadySentPath = "already-sent.csv"
$outputPath = "filtered-list.csv"
# Import the CSV files
$fullList = Import-Csv -Path $fullListPath
$alreadySent = Import-Csv -Path $alreadySentPath
# Assume the common column is 'Email' (change as needed)
$commonColumn = "Email"
# Check if the common column exists in both files
if (-not $fullList[0].PSObject.Properties.Match($commonColumn)) {
Write-Error "The column '$commonColumn' does not exist in the full list file."
exit
}
if (-not $alreadySent[0].PSObject.Properties.Match($commonColumn)) {
Write-Error "The column '$commonColumn' does not exist in the already sent file."
exit
}
# Create a hashset of the already sent emails for quick lookup
$alreadySentEmails = @{}
foreach ($entry in $alreadySent) {
if ($entry.$commonColumn -and $entry.$commonColumn.Trim() -ne "") {
$alreadySentEmails[$entry.$commonColumn.Trim()] = $true
} else {
Write-Warning "Skipping an entry in 'already-sent.csv' with missing or empty '$commonColumn'."
}
}
# Filter the full list to exclude the already sent emails
$filteredList = $fullList | Where-Object {
$_.$commonColumn -and $_.$commonColumn.Trim() -ne "" -and -not $alreadySentEmails.ContainsKey($_.$commonColumn.Trim())
}
# Export the filtered list to a new CSV file
$filteredList | Export-Csv -Path $outputPath -NoTypeInformation
Write-Output "Filtered list saved to $outputPath"