Powershell - Help to fill AD Attributes for users from CSV

Victor Florêncio 1 Reputation point
2021-01-04T17:03:27.317+00:00

Hi everyone,

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

Something like that:

53269-image.png

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:

53362-image.png

Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
6,652 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,555 questions
{count} votes

9 answers

Sort by: Most helpful
  1. Rich Matheisen 46,816 Reputation points
    2021-01-06T15:40:42.97+00:00

    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
            }
    
    1 person found this answer helpful.

  2. Rich Matheisen 46,816 Reputation points
    2021-01-04T19:45:27.82+00:00

    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.

    0 comments No comments

  3. Victor Florêncio 1 Reputation point
    2021-01-05T23:48:57.97+00:00

    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
    0 comments No comments

  4. Ian Xue 37,706 Reputation points Microsoft Vendor
    2021-01-06T03:14:40.607+00:00

    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.


  5. Victor Florêncio 1 Reputation point
    2021-01-06T12:28:52.94+00:00

    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
    }


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.