Update employee ID from CSV

This topic contains 11 replies, has 3 voices, and was last updated by Profile photo of Ron Ron 3 weeks, 3 days ago.

  • Author
    Posts
  • #57433
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #57436
    Profile photo of Ron
    Ron
    Participant

    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.

  • #57439
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
  • #57442
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

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

    Rob,
    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.

  • #57445
    Profile photo of Ron
    Ron
    Participant

    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.

  • #57446
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Ron...

    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!

  • #57881
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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?

  • #57896
    Profile photo of Ron
    Ron
    Participant

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

  • #57899
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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

  • #57904
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

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

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

    Alrighty, moving onwards.... 🙂

    • #57919
      Profile photo of Ron
      Ron
      Participant

      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
      $user.gettype()
      
      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
      
  • #57913
    Profile photo of Ron
    Ron
    Participant

    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

    Emailaddress
    ————
    Blah@blah.com

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

    [PS] $ademail

    Blah@blah.com

You must be logged in to reply to this topic.