Update employee ID from CSV

I had to change how we update employeeID’s from a CSV sheet that is provided to me by Oracle. The script we used to use did comparisons between the sheet by email address, and now oracle isn’t supplying those so i need to write something to match employee id’s and update them. Here is what i have so far.

Import latest adp file, set as variable, only selecting objects from file that have email field populated.
$adplist = import-csv "C:\Temp\Test2.csv" # | Where-Object {$_.'EMPLID'}
#write-host $adplist

   #Loop through each selected object in the csv.    
ForEach ($employee in ($adplist | Where {$_.Company -Match "CompanyA"})) {    
       Write-Host $employee.'First_Name' $employee.'Last_Name' $employee.'EMPLID' $employee.Company
            #Locate AD user based on employeeID match in specific OU
			if ($employee.'EMPLID' -ne ""){
                #Write-Host "Employee ID Found as"$employee.'EMPLID'
				$EmployeeID = $employee.'EMPLID'
			$adUserByEmployeeID = Get-ADUser -SearchBase "OU=Employees,OU=User Accounts,DC=CompanyA,DC=com" -Properties Emailaddress, employeeid -filter 'EmployeeID -eq $EmployeeID' #$employee.'Email Address'" 
                #Write-Host "adUserByEmployeeID: " $adUserByEmployeeID
            #If AD user resolves by EmployeeID
            if ($adUserByEmployeeID) {
                #Sets the format of EmployeeID to Oracle CSV
                $id = [int]$employee.emplid
                 Write-Host "Employee ID: " $id
                #Gets the EmployeeID of the user from AD
                $existingEmployeeID = get-aduser $adUserByEmployeeID -properties employeeid | select $_.employeeid
                  write-host "existing employee id"$existingEmployeeID              
                #Check if the employee ID is already set and set to ID given by Oracle
                if ($existingEmployeeID.employeeid -eq "" -or $existingEmployeeID.employeeid -ne "") {
                    #Set Employee ID of user in AD - only if it was previously unset
                    Set-ADUser $adUserByEmployeeID -EmployeeID $id
                    write-host "TRIED TO WRITE NEW ID"
                write-host "Did not write new ID"

It gets through most of it but doesn’t seem to update the employee ID’s from the CSV sheet. There is something in the end that is stopping it and i’m not sure what.

It’s hard to tell from a quick look at the script especially without the input/output. I would run the script in ISE and set breakpoints and/or step through the code line by line. Examine the contents of the variables before each lines executes, check the results immediately after, etc.

Some observations though:

if ($existingEmployeeID.employeeid -eq “” -or $existingEmployeeID.employeeid -ne “”) {

You may have done this on purpose thinking it would execute regardless of the value, but “” and null are 2 different things. I always use [string]::IsNullOrEmpty($var) as my test when I don’t know or don’t care if the value can be null.

Also, are you positive there will never be a duplicate employeeID? If there is one, $existingEmployeeID will be an array.

I’m having trouble following your logic and understanding the goal of your code.

  1. Loop through Oracle data
  2. Find a user with a matching employeeID
  3. Get the user again (the user is already found with your first Get-ADUser)
  4. if this user is or is not empty (this will always be true, because it's one or the other)
  5. Set the employee ID on the user (you just searched for the user on this criteria, so the employee ID is already set to this value)

Can you explain what you are trying to accomplish? The only change in your code is casting the employeeID to a int, which would remove leading zeros. You don’t need a CSV to fix that, you could just find any employee with a ID set and reset the employee ID to a non-padded value.

Get-ADUser -Filter "EmployeeID -like '*'" | Set-ADUser -EmployeeID $([int]$_.EmployeeID) -WhatIf

There will never be an identical employee number. If there ever is then HR made a huge mistake!

The end goal is that I need to change every employee’s ID’s to match what oracle gives on the csv. After that I will need to then have a script to automate the matching of employees from the CSV and update any information in Active Directory and add a field as well.

I think his problem is that he lost the only way to link the Oracle data to AD. He was using the email address, which creates a positive match between the 2 sets of data. Now, he’s lacking a method to cross reference them. You could resort to name matching, but that would be unreliable. Matching by employeeid, in order to update employeeid, won’t work.


Crap! I am trying to get them to sent work emails so the original script would work, but for some reason they’re sending personal emails and I cannot match those to anything in active directory. It’s very stupid and lazy i think. Getting Oracle to do something outside what they are doing is like running up a hill made of ice!

I have changed this a little bit so that what I need it to do now is to match the Active Directory Email address to the email address on the CSV, then move forward. The part that it’s getting stuck at is the variable to make it equal to the User’s email address.

My variable $adEmail is adding too much data such as @{EmailAddress=UserA@emailaddress.com} when I need $adEmail = UserA@emailaddress.com so i can match what is in AD to the CSV.

How can I get only the results without all the other junk like the headers?

My guess would be that you pulled ADEmail with a select-object, and you need to add -expand.

I’m using basically…

$ademail = get-Aduser -SearchBase “OU=” -properties Emailaddress | select Emailaddress

The output is good just has too much extra header junk in the variable to match what would be on the CSV sheet

So simply adding -expandproperty after select worked like a charm…thus.

$ademail = get-Aduser -SearchBase “OU=” -properties Emailaddress | select -ExpandProperty Emailaddress

Alrighty, moving onwards… :slight_smile:

select -expand Emailaddress

That will convert the object to a string.

Right now, if you were to ouput $ademail, you would get someething like this:

[PS] $ademail



If you add -expand, you just get the data.

[PS] $ademail


You could also have just used: $ademail.Emailaddress

Select-OBJECT just pulls out the the selected columns, the result is just a sub-setted object.

$user = get-aduser blah -Properties mail

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    ADUser                                   Microsoft.ActiveDirectory.Management.ADAccount

($user | select mail).gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    PSCustomObject                           System.Object

($user | select -expand mail).gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object