-1

I have two CSV files with tab-separated values.

CSV1:

GivenName   Surname DisplayName  Manager    
John        Smith   John Smith   Oliver Twist

CSV2:

SAMAccountName    mail             DistinguishedName
John Smith      johns@example.com   CN=John Smith,CN=Users,DC=example,DC=com
Oliver Twist    olivert@example.com CN=Oliver Twist,CN=Users,DC=example,DC=com

What I've wanted to do is to compare the CSV1 with CSV2 and match the manager name in Manager column in CSV1 file with CSV2 DistinguishedName column value (i.e. "CN=Oliver Twist") as in this example and export to a new CSV file like this:

SAMAccountName    mail              Manager
John Smith        johns@example.com Oliver Twist

There might be hundreds of distinguished names in CSV2 file as mentioned and all I want is output from comparing and matching the DistinguishedName column value "CN=whatever name " with the Manager column in CSV1.

Here is the script I tried:

$csv1 = Import-Csv -Path 'C:temp\AD.csv'
$csv2 = Import-Csv -Path 'C:\temp\AD_ExportDN.csv'

$csv3 = foreach ($record in $csv2) {
    $matchingRecord = $csv1 | Where-Object { $_.manager -eq $record.manager }
    # if your goal is to ONLY export records with a matching email address,
    # uncomment the if ($matchingRecord) condition

    #if ($matchingRecord) {
        $record | Select-Object *, @{Name = 'manager'; Expression = {$matchingRecord.DistinguishedName}}
    #}
}

$csv3 | Export-Csv -NoTypeInformation -Force -Path 'C:\temp\merged_for_AD2.csv'
Lance U. Matthews
  • 15,725
  • 6
  • 48
  • 68
denprash
  • 23
  • 1
  • 4
  • 2
    Where is the code you need help with? Post what you’ve tried and include any errors or pertinent details. – Doug Maurer Nov 26 '21 at 08:17
  • Are these tab-separated values? Or fixed-width columns? Otherwise, that several columns contain spaces will make parsing more difficult. Also, how are the `John Smith` entries in both tables related? That `CSV1.DisplayName == CSV2.SAMAccountName` seems like the only way, in which case why can't the `Manager` be found via `SAMAccountName`, too, instead of `DistinguishedName`? – Lance U. Matthews Nov 26 '21 at 09:03

1 Answers1

0

Your question inspired me to add an new feature to the Join-Object script/Join-Object Module I am maintaining (see also: In Powershell, what's the best way to join two tables into one?).
The feature is explained in issue: #29 key expressions.

In your case the required command will be something like:

$Csv1 |Join $Csv2 -On Manager -Equals { [RegEx]::Match($_.DistinguishedName, '(?<=CN=).*(?=,CN=Users)') }

Which returns:

GivenName         : John
Surname           : Smith
DisplayName       : John Smith
Manager           : Oliver Twist
SAMAccountName    : Oliver Twist
mail              : olivert@example.com
DistinguishedName : CN=Oliver Twist,CN=Users,DC=example,DC=com

For the specific regular expression '(?<=CN=).*(?=,CN=Users)':

  • .* refers to any sequence of characters that is
    • Preceded by CN= ((?<=CN=), regex Lookbehind)
    • and followed by CN=Users ((?=,CN=Users), regex lookahead)
iRon
  • 20,463
  • 10
  • 53
  • 79