update AD users from csv with blank cells

Ben S
Ben S used Ask the Experts™

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

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

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).


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?

Top Expert 2014
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.
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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial