CODEWITHSUNDEEP
powershell
joko_88
joko_88

Reputation: 85

Matching content between 2 CSVs and write the values

Each user has a domain1.com and domain2.com separate email address. I would like to compare email addresses within CSV1 & CSV2 and if the prefix name on their emails match, pull the value into CSV1. This will later allow me to run other powershell commands on a source / target kind of scenario.

I have it somewhat, working below although the domain2_email column isn't matching the user row. It looks to be trying to import all of them into an array.

I have used Joe.Bloggs here to test if the IF is working, but ideally - I would like to have it search each entry in CSV1.

End Goal:

# Pull in mailbox data
Get-EXOMailbox  -Filter {EmailAddresses -like *@domain1.com -and RecipientTypeDetails -notlike "SharedMailbox"} |  Export-Csv -Path './domain1export.csv'
Get-EXOMailbox  -Filter {EmailAddresses -like *@domain2.com -and RecipientTypeDetails -notlike "SharedMailbox"} |  Export-Csv -Path './domain2xexport.csv'
 
$outfile = './outfile.csv'
$finalOutfile = './finalOutfile.csv'
 
# Check if outfile.csv exists, if it does - delete.
if (test-path $outfile) {
    Remove-Item $outfile
    Write-Host "$outfile has been deleted"
}
 
$CSV1 = Import-Csv -Path './domain1export.csv'
$CSV2 = Import-Csv -Path './domain2export.csv'
 
$CVS1 | foreach {
    # Splits the name prefix from email
    $_ | Select-Object *,@{Name='Name_Prefix';Expression={$_.PrimarySmtpAddress.Split("@")[0] }} | Export-Csv -Path $outfile -Append -NoTypeInformation
}
 
$outfile2 = Import-Csv -Path $outfile
foreach ($item in $outfile2) {
    if ($outfile2.Name_Prefix -match "Joe.Bloggs") {
        $item | Select-Object *,@{Name='Domain2_email';Expression={$CSV2.PrimarySmtpAddress}} | Export-Csv -Path $finalOutfile -Append -NoTypeInformation
    }
}

Data

CSV1

UserPrincipalName,Alias,DisplayName,EmailAddresses,PrimarySmtpAddress,RecipientType,RecipientTypeDetails
[email protected],d1bloggsj,Domain1 Joe Bloggs,SIP:[email protected] SMTP:[email protected],[email protected],UserMailbox,UserMailbox
[email protected],d1barf,Domain1 Foo Bar,SIP:[email protected] SMTP:[email protected]  ,[email protected],UserMailbox,UserMailbox

CSV2

UserPrincipalName,Alias,DisplayName,EmailAddresses,PrimarySmtpAddress,RecipientType,RecipientTypeDetails
[email protected],d2bloggsj,Domain2 Joe Bloggs,SIP:[email protected] SMTP:[email protected],[email protected],UserMailbox,UserMailbox
[email protected],d1barf,Domain2 Foo Bar,SIP:[email protected] SMTP:[email protected]  ,[email protected],UserMailbox,UserMailbox

Upvotes: 1

Views: 58

Answers (2)

Santiago Squarzon
Santiago Squarzon

Reputation: 61103

I would do it like this, first create a hash table using the Csv1 where the Keys are the mail prefix and the Values are the mail suffix and the, using a calculated property with Select-Object you can search for each line on Csv2 if the mail's prefix exists and obtain the corresponding Values (mail suffix):

Note, below code assumes that there are unique mail prefixes, if this was not the case, an if condition would need to be added to the first loop to avoid Key collision. I would also recommend you to use the MailAddress Class to parse your emails.

$map = @{}

foreach($line in $csv1) {
    $mail = [mailaddress]$line.PrimarySmtpAddress
    $map[$mail.User] = $mail
}

$csv2 | Select-Object *, @{
    Name = 'SecondaryDomain'
    Expression = { $map[([mailaddress]$_.PrimarySmtpAddress).User].Host }
}, @{
    Name = 'SecondarySmtpAddress'
    Expression = { $map[([mailaddress]$_.PrimarySmtpAddress).User].Address }
}

$result | Format-Table

Upvotes: 0

Theo
Theo

Reputation: 61218

So both CSV1 and CSV2 would contain a column called PrimarySmtpAddress and you need to match those, correct?

Try

$CSV1 = Import-Csv -Path './domain1export.csv'
$CSV2 = Import-Csv -Path './domain2export.csv'

$result = foreach ($item in $csv1) {
    $prefix = $item.PrimarySmtpAddress.Split("@")[0]
    # try and find a match
    $matching = $csv2 | Where-Object { $_.PrimarySmtpAddress.Split("@")[0] -eq $prefix }
    $item | Select-Object *, @{Name = 'Name_Prefix'; Expression = {$prefix }},
                             @{Name = 'Domain2_email';Expression = {$matching.PrimarySmtpAddress }} 
}

$finalOutfile = './finalOutfile.csv'
$result | Export-Csv -Path $finalOutfile -NoTypeInformation

BTW. According to the docs, the -Filter parameter is a string, not a scriptblock, so you should use

Get-EXOMailbox  -Filter "EmailAddresses -like '*@domain1.com' -and RecipientTypeDetails -ne 'SharedMailbox'"

Upvotes: 1

Related Questions