Forum Discussion

jebujohn's avatar
jebujohn
Copper Contributor
May 08, 2020

Bulk update Azure AD with user attributes from CSV

I am looking for a way to update user attributes (OfficePhone and Department) for about 500 users from a CSV to AzureAD using a powershell. Does anyone know of a script that I could use? I am new here and if I have not given enough information, please let me know. I tried using Set-AzureADUser piping records using a foreach statement from a csv that I imported, but it was throwing up errors. 

Thanks!

Jacob

  • jebujohn 

     Hello Jacob,

     

    Your  CSV has to look something like this:

    UserPrincipalName;Department;TelephoneNumber
    manfreddelaat@domain.nl;IT;0135113333
    manfred@domain.nl;IT;0622222222

     

    Your Powershell code:

     

    # Connect to AzureAD
    Connect-AzureAD
    
    # Get CSV content
    $CSVrecords = Import-Csv C:\Temp\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-AzureADUser -Filter "userPrincipalName eq '$upn'"  
        if ($user) {
            try{
            $user | Set-AzureADUser -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
        }
    }
    
    # Array skipped users
    # $SkippedUsers
    
    # Array failed users
    # $FailedUsers

     

     

    Good luck!

     

    Kind Regards, Manfred de Laat

  • Manfred101's avatar
    Manfred101
    Iron Contributor

    jebujohn 

     Hello Jacob,

     

    Your  CSV has to look something like this:

    UserPrincipalName;Department;TelephoneNumber
    manfreddelaat@domain.nl;IT;0135113333
    manfred@domain.nl;IT;0622222222

     

    Your Powershell code:

     

    # Connect to AzureAD
    Connect-AzureAD
    
    # Get CSV content
    $CSVrecords = Import-Csv C:\Temp\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-AzureADUser -Filter "userPrincipalName eq '$upn'"  
        if ($user) {
            try{
            $user | Set-AzureADUser -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
        }
    }
    
    # Array skipped users
    # $SkippedUsers
    
    # Array failed users
    # $FailedUsers

     

     

    Good luck!

     

    Kind Regards, Manfred de Laat

    • mesuhaib's avatar
      mesuhaib
      Copper Contributor

      Manfred101i have modified your script as per my need but somehow its throwing errors, with first four field its working perfect but as i added another entries it says the "WARNING: xyz@abc.org user found, but FAILED to update. please note i have changed the delimiter as per my regional settings. any help regarding this would be appreciated.

      # Connect to AzureAD
      Connect-AzureAD
      
      # Get CSV content
      $CSVrecords = Import-Csv C:\Users\neofreedom\Desktop\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-AzureADUser -Filter "userPrincipalName eq '$upn'"  
          if ($user) {
              try{
              $user | Set-AzureADUser -jobTitle $CSVrecord.jobTitle -Department $CSVrecord.Department -state $CSVrecord.state -country $CSVrecord.country -officeLocation $CSVrecord.officeLocation -city $CSVrecord.city -postalCode $CSVrecord.postalCode -TelephoneNumber $CSVrecord.TelephoneNumber -mobilePhone $CSVrecord.mobilePhone
              $FailedUsers += $upn
              Write-Warning "$upn user found, but FAILED to update."
              }
          }
          else {
              Write-Warning "$upn not found, skipped"
              $SkippedUsers += $upn
          }
      }
      
      # Array skipped users
      # $SkippedUsers
      
      # Array failed users
      # $FailedUsers

       

      • Manfred101's avatar
        Manfred101
        Iron Contributor

        mesuhaib this script is not going to work. On line 16 you are starting a try{} block, but you are missing the catch {} part. A try always needs a catch! Check out this video: (295) Using Try/Catch Blocks In PowerShell - YouTube . You need to resolve that. 

         

        Whene you ar done fixing the try block, you can troubleshoot line 17 by commenting out the options one-by-one: (see below, in this line you only set the jobtitle and department value. Try to find where things are going wrong)

         

        $user | Set-AzureADUser -jobTitle $CSVrecord.jobTitle -Department $CSVrecord.Department #-state $CSVrecord.state -country $CSVrecord.country -officeLocation $CSVrecord.officeLocation -city $CSVrecord.city -postalCode $CSVrecord.postalCode -TelephoneNumber $CSVrecord.TelephoneNumber -mobilePhone $CSVrecord.mobilePhone

         

         

        Good luck!

        Grtz, Manfred de Laat

    • jebujohn's avatar
      jebujohn
      Copper Contributor

      Thanks Manfred101 

      Worked like a charm. Lovely to learn from you!

      Keep safe and stay blessed

    • Kamlesh360's avatar
      Kamlesh360
      Copper Contributor

      Manfred101 

       

      Thanks for sharing script here to update user details. But, I would draw your  attention that script is working with only one user details, when I do it with more than one  row, its giving the following warning and finally users details not updated.

       

      Here is the CSV file content:

       

      Can you help me to resolve this issue?

  • esfitz's avatar
    esfitz
    Copper Contributor

    Manfred101 This worked great!

    One think im stuggling with, is the user doesnt get updated when one of the properties is empty? is there a way around that? i.e. the mobile number for that user is empty in the csv most of the update for that user fails.

    • leewegener's avatar
      leewegener
      Copper Contributor

      esfitzI got around this by entering a - (dash) into blank fields. Otherwise, you are right, the script will stop there.

  • hhbadarin's avatar
    hhbadarin
    Brass Contributor

    jebujohn 

    This is the shortest line to get the job done:
    Assuming your csv file has two columns;; UserPrincipalName and a column with the attribute you wish to update ( the user's Title for example); assuming the file is stored in the D drive and is titled updateusers, then here's you code: 
    Import-CSV D:\updateusers.csv | ForEach-Object { Set-AzureADUser -ObjectID $_.UserPrincipalName -Title $_.Title 

    If you want to update more attributes you can add them to the code, for example to update department just add -Department $_.Department to the code above: 

    Import-CSV D:\updateusers.csv | ForEach-Object { Set-AzureADUser -ObjectID $_.UserPrincipalName -Title $_.Title -Department $_.Department 

    • jebujohn's avatar
      jebujohn
      Copper Contributor
      Agree that code is the simple I just like some feedback on one's that did not work - however things get more challenging with updating the manager which was the last set of comments
  • AIT_OURHARBIY's avatar
    AIT_OURHARBIY
    Copper Contributor
    Hello Everyone I need your help
    Thanks in advenced
    I need to Add and Update profile user in Azur with powershell , I need to add an atributs IP PHONE in user profile in Azure by Script
    • Kevin_Morgan's avatar
      Kevin_Morgan
      Iron Contributor

      dnelsonpd 

       

      Hi, You have to use the ExtensionProperty to set the null value or clear the property with Set-AzureADUser cmdlet.

       

      $properties = [Collections.Generic.Dictionary[[String],[String]]]::new()
      $properties.Add("Mobile", [NullString]::Value)
      Set-AzureADUser -ObjectId "email address removed for privacy reasons" -ExtensionProperty $properties
      
      # Refer to the below post for more details.
      # https://morgantechspace.com/2022/03/update-bulk-azure-ad-user-attributes-using-powershell.html

       

       

      Check this post for more details: Remove or Clear Property or Set Null value using Set-AzureADUser cmdlet 

       

      Check out this link from specmasoft to update bulk user attributes, including setting a manager, updating licenses, and managing extension attributes (e.g., employeeId). You can also set null values or clear existing property values. Additionally, the tool allows you to update passwords for bulk users and add multiple users to any groups or teams.

       

      Update Bulk User Information in Microsoft 365 using a CSV file

       

      • rogerdodger's avatar
        rogerdodger
        Copper Contributor
        so any other set attribute does not get touched at all, correct? I"m only looking to update Titles and plan on testing this but obv don't want anything else to change on the users. . .

        # Connect to AzureAD
        Connect-AzureAD

        # Get CSV content
        $CSVrecords = Import-Csv C:\Temp\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-AzureADUser -Filter "userPrincipalName eq '$upn'"
        if ($user) {
        try{
        $user | Set-AzureADUser -JobTitle $CSVrecord.JobTitle
        } catch {
        $FailedUsers += $upn
        Write-Warning "$upn user found, but FAILED to update."
        }
        }
        else {
        Write-Warning "$upn not found, skipped"
        $SkippedUsers += $upn
        }
        }

        # Array skipped users
        # $SkippedUsers

        # Array failed users
        # $FailedUsers
  • Arun_Siwakoti's avatar
    Arun_Siwakoti
    Copper Contributor
    Simple to see and learn this PowerShell code start with exchangeonline first after that we go through AzureAD: Please email me: email address removed for privacy reasons 🙂

    Import-Module ExchangeOnlineManagement
    Install-Module ExchangeOnlineManagement
    Connect-ExchangeOnline
    $CSVrecords = Import-Csv C:\newdata.csv
    $total = $CSVrecords.Count
    $i = 0
    $newdom = "your.domian.com" #type vefied domain from your domain list
    foreach ($user in $CSVrecords)
    {
    $i = $i + 1
    $oid = $user.ObjectId
    $username = $user.GivenName.ToLower() + "." + $user.Surname.ToLower() #ToLower is to create all character in lowercase ToUpper() for uppercase
    #Write-Host "$username"
    $finalemail = $username + "@" + $newdom
    #Write-Host "$finalemail"
    Set-Mailbox $oid -EmailAddresses $finalemail
    Write-Progress -activity "Processing $user.DisplayName $i out of $total completed"

    }
    Write-Host "All UPN set to domain $newdom"

    Now Again time for AzureAD

    Install-Module AzureAD
    import-Module AzureAD
    Connect-AzureAD
    $CSVrecords = Import-Csv C:\Users\aruns\OneDrive\Desktop\newdata.csv
    $total = $CSVrecords.count
    $i = 0
    $pdomain = "your.domain.com" #New tetantdoamin here betweeen " "
    # Loop trough CSV records
    foreach ($user in $CSVrecords)
    {
    $i = $i + 1
    $oid = $user.ObjectId
    $username = $user.GivenName.ToLower() + "." + $user.Surname.ToLower()
    #Write-Host "$username"
    $finalemail = $username + "@" + $newdom
    #Write-Host "$finalemail"
    Set-AzureADUser -ObjectId $oid -UserPrincipalName $finalemail
    Write-Progress -activity "Processing $user.DisplayName $i out of $total completed"
    }
    Write-Host "Well done Action Completed!"

    If helpful I love you all

Share

Resources