Link to home
Start Free TrialLog in
Avatar of tools2teach
tools2teach

asked on

Import Active Directory user attributes from CSV with only email address information.

I have a CSV file from payroll which includes the following information that needs to be imported into Active Directory.
-Employee ID
-Home Department ID
-Department
-Job Description
-Managers name

The only consistent and accurate information that I have is the email account which is also populated in AD.  I do not have the SAM account to go off of in the CSV

I need a script (powershell) that will pull the email account from the CSV file and fill in the attributes above.  Any suggestions on the best way to go about this would be appreciated.  Also, a working script or example.

Thanks!
Avatar of Will Szymkowski
Will Szymkowski
Flag of Canada image

You can achieve this using the email address you cannot directly use the Set-ADUser cmdlet -identity parameter because it does not accept an email address as an Identity value.

Couple of things...
for this to work correctly you need to make sure that all of your column headings in your CSV have listed DO NOT have any SPACES. Example Employee ID = EmployeeID.

You will also need another column called Email and then all of the email addresses for each user you have below.

Then use the script below...

Import-Module activedirectory
$CSVData = "c:\filename.csv"
ForEach ($User in $CSVData)
    {

       $User.Email
       $User.EmployeeID
       $User.HomeDepartmentID
       $User.Department
       $User.JobDescription
       $User.ManagersName
       
             $GetSAM = Get-ADUser -Filter { mail -eq $User.Email } -Properties sAMAccountName, Mail
             $GetSAM.sAMAccountName

             Set-ADUser -Identity $GetSAM.sAMAccountName -EmployeeID $User.EmployeeID -Company $User.HomeDepartmentID -Department $User.Department -Title $User.JobDescription -Manager $User.ManagersName

}

Open in new window


Will.
Avatar of tools2teach
tools2teach

ASKER

So would my CSV look something like this?
Users.csv
Yep, exactly.

Will.
Thanks Will,  so just to confirm.   The script will look at all the emails in CSV and then populate other attributes into AD?

I tried creating a dummy account and it appears to have completed successfully, but their were no fields populated.
I did not test this but it should work. I will test this in my lab shortly and post back. Basically it is going to reference the email addresses in your CSV using the Email Column. It then filters the AD account based on the email address. I then get the samaccountname from there and then reference that in the Set-aduser and the rest of the properties that are in the CSV file.

I will post back.

Will.
Not sure what would be an easier more efficient way to do this but I also

The following information into each column.

First Name
Last Name
AD Name:  ex.  Doe, John

What are thoughts?

Thank You!
Please see attached file of an example ADP file that I have.
example.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Will Szymkowski
Will Szymkowski
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial