Using PowerShell to query Active Directory and add data to new column of existing csv

Question

I have a CSV with hundreds of usernames listed on it. In PowerShell I would like to run Get-ADUser and output their DisplayName in the adjacent cell. I also want to keep all the other information within the correct rows, and make sure if any usernames are not found in AD, that the output doesn't write the following DisplayName out of order. Can someone help me figure this out? I'm relatively new to PowerShell, and very new to using it to modify Excel.

For confidentiality, lets say my spreadsheet looks like this:

This is what I have so far...

$userlist = Get-Content $home\Desktop\names.csv

ForEach ($user in $userlist) {
    Get-ADUser $user -Properties DisplayName | 
          Select-Object Name,DisplayName |
          Export-CSV $home\Desktop\new_names.csv -NoTypeInformation
}

Answer 1

Import-CSV $home\Desktop\names.csv |
    ForEach-Object {
      $samAccountname = ""
      $displayname = ""
      $samAccountname = $_."last logon"
      $machinename = $_.'machine name'
      $displayname = (get-aduser $samaccountname -Properties displayname).displayname
      $ou = $_.OU
      [pscustomobject]@{
            machineName=$machinename
            displayname=$displayname 
            OU=$ou 
            samAccountname=$samAccountname 
        }
    } |
    select samAccountname ,machineName,displayname,ou |
    Export-CSV new_names.csv -Append -NoTypeInformation

In the above I am making the assumption that "Last Logon" in your spreadsheet is each user's samaccountname. Steps taken:

• Import the original csv
• On each loop we assign each value from each row to a variable
• For our new column "displayname" we use the get-aduser cmdlet along with the samaccountname info from our csv
• Create a psobject to hold this information from the variables
• We then use Select-Object to select the order of the spreadsheet headers prior to passing it to export-csv to produce our updated csv