by royse at 2013-04-08 08:24:43
I need to update properties on AD users with values from a .csv.by coderaven at 2013-04-08 08:47:52
I know this has been done many times, but most of the discussions I find use the samAccountName as a key.
The .csv in my case is generated by an external source and does not contain the samAccountName.
The primary key in my .csv would be the employeeID, which is listed in the .csv as empID.
From the empID I need to find the user and set the rest of the properties.
The csv looks like this:
EmpID,Last Name,First Name,Middle Initial,Preferred Name,Work Phone,Extension,Home Department,Location,Manager Last Name,Manager First Name, etc
I’ve tried a few things mostly using get cmdlets to see what I could come up with.
This will get me the correct user:
Import-Csv ‘.\Users.csv’ | ForEach-Object {Get-ADUser -Prop employeeid -Filter "employeeid -eq $($.empid)"}
But every variation I’ve tried with a set user command fails to find the user object. ie
Import-Csv ‘.\Users.csv’ | ForEach-Object {Set-ADUser -Prop employeeid -Filter "employeeid -eq $($.empid)" -officephone $user.Extension}
I would sure appreciate some help getting this to work!
So I am assuming the employeeid is filled for each user in your AD or at least for the ones you are going to update from the CSV.by royse at 2013-04-09 12:13:00
There are a few options, but with what know, you will have to do a little extra processing to pull up the account. I hope I can get you started.$Users = Get-ADUser -Filter * -Properties EmployeeID
# This is just me, but I create a hashtable so I can quickly search/match the what am looking for with out having to do any extra pulling or querying from AD
$UserHash = @{}
foreach ($User in $Users)
{
$UserHash.Add($User.EmployeeID, $User.SamAccountName)
}
Clear-Variable Users #We don't need this anymore, so a little cleanup may not hurt because this could be big.
$CSV = Import-Csv -Path \path\to\file
foreach ($Record in $CSV)
{
Set-ADUser -Identity ($UserHash.Item($Record.EmpID)) -SurName $Record."Last Name" -…
}
This is just a quick approach I would take. If it is a regular process, it will need more work. The code is untested, please test.
I had a chance to do some testing CodeRaven, thanks.by ArtB0514 at 2013-04-09 13:07:07
Everything seemed to work except that some properties aren’t accessible via cmdlet.
I used -add to update those by their LDAP names.
Here’s the relevant part of that code:Import-module ActiveDirectory
$Users = Get-ADUser -Filter * -Properties EmployeeID
$UserHash = @{}
foreach ($User in $Users)
{
$UserHash.Add($User.EmployeeID, $User.SamAccountName)
}
Clear-Variable Users
$CSV = Import-Csv -Path ‘.\Users.csv’
foreach ($Record in $CSV)
{
Set-ADUser -Identity ($UserHash.Item($Record.EmpID)) -OfficePhone $Record."Work Phone" -Add @{otherTelephone = $Record."Extension"
}
The initial population of the hashtable is not very clean.
It throws errors for every user that does not have an empoyeeID.
Every service account, training account, shared mailbox, etc.
This won’t matter for my use, but something to watch for if any else tries it.
No to figure out some error trapping and logging…
You could try something like this:foreach ($User in ($Users | Where-Object {-not [String]::IsNullOrEmpty($_.EmployeeID)}))
{
$UserHash.Add($User.EmployeeID, $User.SamAccountName)
}