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!
tools2teachAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will SzymkowskiSenior Solution ArchitectCommented:
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.
tools2teachAuthor Commented:
So would my CSV look something like this?
Users.csv
Will SzymkowskiSenior Solution ArchitectCommented:
Yep, exactly.

Will.
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

tools2teachAuthor Commented:
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.
Will SzymkowskiSenior Solution ArchitectCommented:
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.
tools2teachAuthor Commented:
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!
tools2teachAuthor Commented:
Please see attached file of an example ADP file that I have.
example.xlsx
Will SzymkowskiSenior Solution ArchitectCommented:
No it is fine. I have tested this and I got it to work. There was an issues with Line 2. I forgot to add Import-csv lol. I have also changed a few other parameters...

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

       $User.Email
       $User.EmployeeID
       $User.HomeDepartmentID
       $User.Department
       $User.JobDescription
       $User.ManagersName
       
             $GetSAM = Get-ADUser -Filter * -Properties sAMAccountName, Mail | ? { $_.mail -eq $User.Email } | select sAMAccountName
             $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


That should work now.

Will.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.