update AD users from csv with blank cells
Hi,
I need to update around 1000 users from a csv, I have the script below which runs until it gets to an empty cell then I get an error, can anyone suggest away to stop the error?
I need to update around 1000 users from a csv, I have the script below which runs until it gets to an empty cell then I get an error, can anyone suggest away to stop the error?
#Import AD Module
Import-Module ActiveDirectory
write-Host 'Starting to update AD Attributes.......' -NoNewline -ForegroundColor Yellow
# Import CSV into variable $users
$users = Import-Csv -Path C:\temp\users.csv
# Loop through CSV and update users if the exist in CVS file
foreach ($user in $users) {
#Search in specified OU and Update existing attributes
Get-ADUser -Filter "displayName -eq '$($user.displayName)'" -Properties * -SearchBase "OU=Users,DC=company,DC=local" |
Set-ADUser -Title $($user.Title) -Department $($user.Department) -MobilePhone $($user.MobilePhone) -OfficePhone $($user.OfficePhone) -Office $($user.Office) -City $($user.City)
}
Write-Host 'done!' -ForegroundColor Green
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The most straight-forward solution is to not have empty cells.
But if that's not possible, then you either have to a) come up with different workflows for each error situation, i.e. what do I do when:
1) displayname field is empty
2) title field is empty
3) department field is empty
...etc., or b) handle them all in the same way, i.e. skip processing of the line (this is really what's happening now when you encounter an error).
But if that's not possible, then you either have to a) come up with different workflows for each error situation, i.e. what do I do when:
1) displayname field is empty
2) title field is empty
3) department field is empty
...etc., or b) handle them all in the same way, i.e. skip processing of the line (this is really what's happening now when you encounter an error).
Hi footech,
Unfortunately some of the cells will always be empty.
I would like it to skip the empty cell and move to the next, any suggestion how to do that?
Regards,
Unfortunately some of the cells will always be empty.
I would like it to skip the empty cell and move to the next, any suggestion how to do that?
Regards,
Here's one method. We'll create a hashtable of all the properties to set. For each line, you have to check every field. If it's not blank, then add an entry to the hashtable for the parameter to set and the matching value. Then use splatting with the Set-ADUser cmdlet.
The bulk of the field checking is done in the switch statement. I've filled out a few conditions, so you can see the pattern and do the others yourself.
BTW, there's no reason to retrieve all the properties from the AD user object if you're not going to do anything with them - it'll just slow things down.
The bulk of the field checking is done in the switch statement. I've filled out a few conditions, so you can see the pattern and do the others yourself.
BTW, there's no reason to retrieve all the properties from the AD user object if you're not going to do anything with them - it'll just slow things down.
foreach ($user in $users) {
If ( $user.displayname -eq "" )
{ continue } #can't get a user so just skip any processing for this line
$props = [ordered]@{}
switch ($user)
{
#use this form: $props.Add("<name of parameter>",<object property>)
{$_.title -ne "" } { $props.Add("Title",$user.title) }
{$_.department -ne ""} { $props.Add("Department",$user.department) }
{$_.mobilephone -ne ""} { $props.Add("MobilePhone",$user.MobilePhone) }
}
#Search in specified OU and Update existing attributes
Get-ADUser -Filter "displayName -eq '$($user.displayName)'" -SearchBase "OU=Users,DC=company,DC=local" |
Set-ADUser @props
}
Premium Content
You need an Expert Office subscription to comment.Start Free Trial