Appending csv with email from users in Active Directory or another csv

I have created a process that brings in a csv with over 5000 employees on it, then compares itself to Active Directory.

The end process then has 2 csv files.

One CSV is the original with all the employees, but no email address.  It does however include the sAMAccountName.

What I need is to be able to add the email addresses from Active Directory, for those that exist, to the original CSV.

I am able to look up the email addresses from the employee CSV by sAMAccountname and make a csv file, so then i'd have 2 CSV files.

Each having the sAMAccountNames on them.  How can I set something that would either just input the user's email address into the current csv after looking up by samname, or compare the 2 CSV files and when they match, import the emailaddress from the CSV?

Example of the Original csv:

Number Display Name Status Business Email First Name Last Name SamAccountName mail SideIndicator
123456 Doe, John G Active John Doe 123456 email needed ==
234567 Jane, David Active David Jame 234567 email needed ==

Example of email csv:

mail sAMAccountName
[email protected] 123456
[email protected] 234567

I need the mail from csv 2 to be imported in csv 1 when they match.  Any good ways to import from 1 csv to another, or better just take the email from.  The code below is how i take the sAMAccountnames from the original csv and make the email csv above. 

Foreach ($user in OriginalCSV){

   Get-ADuser $user.SamAccountName -Properties mail | select sAMAccountName, mail | export-csv C:\temp\usermail.csv -append -NoTypeInformation
}

Parents
  • I now am using a nested foreach loop and that seems to work.  Not sure if there is a cleaner way to do this or not...

    $CSV1 = Import-csv C:\temp\csv1.csv
    $CSV2 = Import-csv C:\temp\csv2.csv
    
    
    $combine = @()
    
     Foreach ($first in $CSV1){
       Foreach ($second in $CSV2){
         If ($first.sAMAccountName -eq $Second.sAMAccountName){
                    $match = New-Object PSObject
                    $match | Add-Member NoteProperty "samaccountname" $first.SamAccountName
                    $match | Add-Member NoteProperty "EmployeeNumber" $first.employeenumber
                    $match | Add-Member NoteProperty "FirstName" $first.'First Name'
                    $match | Add-Member NoteProperty "LastName" $first.'Last Name'
                    $match | Add-Member NoteProperty "EffectiveStart" $TodaysDay
                    $match | Add-Member NoteProperty "ElectronicAddress" $second.mail
                    $combine += $match
               }
            } 
         }
    $combine | Expor-csv C:\temp\combine.csv -notypeinformation

Reply
  • I now am using a nested foreach loop and that seems to work.  Not sure if there is a cleaner way to do this or not...

    $CSV1 = Import-csv C:\temp\csv1.csv
    $CSV2 = Import-csv C:\temp\csv2.csv
    
    
    $combine = @()
    
     Foreach ($first in $CSV1){
       Foreach ($second in $CSV2){
         If ($first.sAMAccountName -eq $Second.sAMAccountName){
                    $match = New-Object PSObject
                    $match | Add-Member NoteProperty "samaccountname" $first.SamAccountName
                    $match | Add-Member NoteProperty "EmployeeNumber" $first.employeenumber
                    $match | Add-Member NoteProperty "FirstName" $first.'First Name'
                    $match | Add-Member NoteProperty "LastName" $first.'Last Name'
                    $match | Add-Member NoteProperty "EffectiveStart" $TodaysDay
                    $match | Add-Member NoteProperty "ElectronicAddress" $second.mail
                    $combine += $match
               }
            } 
         }
    $combine | Expor-csv C:\temp\combine.csv -notypeinformation

Children
No Data