I have a simple CSV file with 4 columns. I am trying to use the OwnerEmail column and Azure AD (or On-Prem Active Directory) to populate the employee ID column using PowerShell.

Original:

After:

I've only gotten as far as adding the Employee Id column to the Csv file which did not exist before. I have not been able to find anything on this. A little Advice and direction would be really helpful. I am new to PowerShell

How I added Employee ID Column:

$CSVImport | Select-Object "employeeID",*

CodePudding user response：

When searching for answers online, first split up the task into smaller tasks, then search for those individual tasks.

You already figured out how to add a new column to a collection, so now you need to:

1. Loop through items in a collection
2. Get an AD account by email address and read the employeeID
3. Export collection to CSV

If you search "powershell" plus any of those, you'll find lots of answers.

But I'll help you with steps 1 and 2, which will look something like this (for reading from on-prem AD):

$employees = $CSVImport | Select-Object "employeeID",*

foreach ($employee in $employees) {
    $user = Get-ADUser -Filter "EmailAddress -eq '$($employee.OwnerEmail)'" -Properties employeeID
    $employee.employeeID = $user.employeeID
}

Then you can use Export-Csv to save $users back to a file.

And you may want some error handling in case a user account is not found.

CodePudding user response：

Use a Calculated Property to insert the value by querying ad again:

$CSVImport | 
    Select-Object @{
        Name = "employeeID"
        Expression = { 
            try
            {
                (Get-ADUser -Filter "EmailAddress -eq '$($_.OwnerEmail)'" -Properties 'EmployeeID').EmployeeID
            }
            catch 
            {
                'Not Found'
            }
        }
    }, *

Since the AD cmdlets are error terminating by default, I placed it inside a try{} catch{} statement to handle the error if the user wasn't found by their email.