Powershell - Help to fill AD Attributes for users from CSV

Question

Hi everyone,

Today we received a CSV with 2k users to change their AD Attributes.

Something like that:

The CSV was already exported and I need to import those informations to their Attributes.

I Tried some scripts but, none of them worked.

Does anybody here has an basic idea of a script that could help me?

Scripts that already tried:

Import-Module ActiveDirectory

$USERS = Import-CSV ""

$USERS|Foreach{
Get-ADUser -filter {name -eq $.name}|Set-ADUSer -MobilePhone $.MobilePhone -StreetAddress $.StreetAddress -City $.City -Title $_.Title}

And

Import-module ActiveDirectory
$users = Import-Csv "C:\ad_import\users.csv"
foreach ($user in $users) {
$props = @{
identity = $user.samaccountname
givenName = if($user.FirstName ){$user.FirstName }else{$null}
surname = if($user.LastName ){$user.LastName }else{$null}
title = if($user.JobTitle ){$user.JobTitle }else{$null}
description = if($user.description){$user.description}else{$null}
state = if($user.State ){$user.State }else{$null}
Office = if($user.Office ){$user.Office }else{$null}
department = if($user.Department ){$user.Department }else{$null}
city = if($user.City ){$user.City }else{$null}
manager = if($user.manager ){$user.manager }else{$null}
company = if($user.company ){$user.company }else{$null}
}
set-aduser @props
}

The structure of CSV is:

Answer 1

Try this version of that script. It corrects the comparison operator in the -Filter parameter and adds the necessary "-ErrorAction STOP" to the Set-ADUser cmdlet. Without the ErrorAction the Set-ADUser won't throw a non-terminating exception and the Catch block will never be run.

#Get CSV content
$CSVrecords = Import-Csv "C:\Test.csv" -Delimiter ";"

#Create arrays for skipped and failed users
$SkippedUsers = @()
$FailedUsers = @()

#Loop trough CSV records
foreach ($CSVrecord in $CSVrecords) {
        $upn = $CSVrecord.UserPrincipalName
        $user = Get-ADUser -Filter "userPrincipalName -eq '$upn'"
        if ($user) {
                try {
                        $user | Set-ADUser -Department $CSVrecord.Department -TelephoneNumber $CSVrecord.TelephoneNumber -ErrorAction STOP
                }
                catch {
                        $FailedUsers += $upn
                        Write-Warning "$upn user found, but FAILED to update."
                }
        }
        else {
                Write-Warning "$upn not found, skipped"
                $SkippedUsers += $upn
        }

Answer 2

In your first example, the only place that has an underbar after the "$" is in the "-Title $<underbar>.Title" parameter. All the other parameter values use only "$.valuename" where they should use "$<underbar>.valuename". However that may be due to the way Microsoft's Q&A editor works! Try using the "Code Sample" icon in the formatting bar when posting code. It's not perfect, but it does eliminate a lot of problems posting code.

Also, in the first example's "Get-ADUser" you're using the "name" value from the CSV in the -Filter. If the filter fails to find that value the result is $null -- the cmdlet doesn't throw an exception. Remove the filer and use the -Identity parameter and the samaccountname from the CSV as you did in the second example.

You also don't mention if all of your user accounts are in the same domain. If you have a multi-domain forest you'll either have to use a distinguished name or search a Global Catalog and specify the root of the forest as the -SearchBase parameter value.

Since you don't mention what the problem is (except to say "none of them worked") it's not possible to tell you what to fix. But at the least, I'd start by verifying that the sAMAccountName values in the CSV don't have any trailing spaces.

I don't recall if the properties your populating in the $props hash with $null values will work if the Set-ADUser cmdlet's parameter specifies it accepts a string type. You may want to construct the $props hash by doing something like this "if ($user.city){$props.City = $user.City}" and simply omit the parameter if it isn't present in the CSV.

Answer 3

Thank you all for the answers.

Error from first script:

Get-ADUser : Cannot process argument because the value of argument "path" is not valid. Change the value of the "path" argument and run the operation
again.
At line:6 char:1

• Get-ADUser -filter {name -eq $.name}|Set-ADUSer -MobilePhone $.Mobile ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [Get-ADUser], PSArgumentException
• FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.Management.Automation.PSArgumentException,Microsoft.ActiveDirectory.Management.Commands.Get
  ADUser

Get-ADUser : Cannot process argument because the value of argument "path" is not valid. Change the value of the "path" argument and run the operation
again.
At line:6 char:1

• Get-ADUser -filter {name -eq $.name}|Set-ADUSer -MobilePhone $.Mobile ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [Get-ADUser], PSArgumentException
• FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.Management.Automation.PSArgumentException,Microsoft.ActiveDirectory.Management.Commands.Get
  ADUser

This is the error when I run the second script:

Set-ADUser : Cannot validate argument on parameter 'Identity'. The argument is null. Provide a valid value for the argument, and then try running the
command again.
At line:17 char:12

• set-aduser @props
• ~~~~~~
• CategoryInfo : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
• FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Set-ADUser : Cannot validate argument on parameter 'Identity'. The argument is null. Provide a valid value for the argument, and then try running the
command again.
At line:17 char:12

• set-aduser @props
• ~~~~~~
• CategoryInfo : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
• FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Answer 4

Hi,

In the first script it should be $_.name, not $.name . See if this works

$USERS|Foreach{  
$name = $_.name  
Get-ADUser -filter {name -eq $name} |Set-ADUSer -MobilePhone $_.MobilePhone -StreetAddress $_.StreetAddress -City $_.City -Title $_.Title}  

The second error indicates there's no cloumn of the name "samaccountname" in your csv file. In the image I can only see ”SamAccou“ in cloumn R. Check if there is a trailing space as Rich suggested above.

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Guys, I found other script and made some adjusts, but, I receive the error below:

Get-ADUser : Error parsing query: 'userPrincipalName eq 'vruiz'' Error Message: 'syntax error' at position: '19'.
At line:13 char:13

• $user = Get-ADUser -Filter "userPrincipalName eq '$upn'"
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : ParserError: (:) [Get-ADUser], ADFilterParsingException
• FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Script

Get CSV content

$CSVrecords = Import-Csv "C:\Test.csv" -Delimiter ";"

Create arrays for skipped and failed users

$SkippedUsers = @()
$FailedUsers = @()

Loop trough CSV records

foreach ($CSVrecord in $CSVrecords) {
$upn = $CSVrecord.UserPrincipalName
$user = Get-ADUser -Filter "userPrincipalName eq '$upn'"
if ($user) {
try{
$user | Set-ADUser -Department $CSVrecord.Department -TelephoneNumber $CSVrecord.TelephoneNumber
} catch {
$FailedUsers += $upn
Write-Warning "$upn user found, but FAILED to update."
}
}
else {
Write-Warning "$upn not found, skipped"
$SkippedUsers += $upn
}