Powershell
Powershell

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?

#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

LVL 1

Ben SAsked: 2017-11-03

Who is Participating?

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 43

footechCommented: 2017-11-03

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

LVL 1

Ben SAuthor Commented: 2017-11-05

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,

LVL 43

footechCommented: 2017-11-05

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

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