Forum Discussion
jebujohn
May 08, 2020Copper Contributor
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
Hello Jacob,
Your CSV has to look something like this:
UserPrincipalName;Department;TelephoneNumber
manfreddelaat@domain.nl;IT;0135113333
manfred@domain.nl;IT;0622222222Your 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
- Arun_SiwakotiCopper ContributorSimple 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 - dnelsonazpainCopper ContributorManfred101. Any idea how to set a field to null?
- Kevin_MorganIron Contributor
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
- rogerdodgerCopper Contributorso 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
- AIT_OURHARBIYCopper ContributorHello 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 - hhbadarinBrass Contributor
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 }
- jebujohnCopper ContributorAgree 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
- esfitzCopper 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.
- leewegenerCopper Contributor
esfitzI got around this by entering a - (dash) into blank fields. Otherwise, you are right, the script will stop there.
- Manfred101Iron Contributor
Hello Jacob,
Your CSV has to look something like this:
UserPrincipalName;Department;TelephoneNumber
manfreddelaat@domain.nl;IT;0135113333
manfred@domain.nl;IT;0622222222Your 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
- edlCopper Contributor
this script doesn't work anymore
- jebujohn1Copper Contributor
Use Microsoft Graph instead. Here is an example for the EmployeeID (o365info.com)
# Connect to Microsoft Graph
Connect-MgGraph -Scopes "User.ReadWrite.All"
# Read the CSV file
$users = Import-Csv -Path "set your path here"
# Go through each user in the CSV and update the EmployeeId property
foreach ($user in $users) {
$userPrincipalName = $user.UserPrincipalName
$employeeId = $user.EmployeeId
# Check if the user exists
$existingUser = Get-MgUser -UserId $userPrincipalName -Property UserPrincipalName, EmployeeId | Select-Object UserPrincipalName, EmployeeId -ErrorAction SilentlyContinue
if ($existingUser) {
# Check if the existing EmployeeId matches the new value
if ($existingUser.EmployeeId -eq $employeeId) {
# EmployeeId already set with the same value
Write-Host "User '$userPrincipalName' already has Employee ID '$employeeId'."
}
else {
# Update the EmployeeId
Update-MgUser -UserId $userPrincipalName -EmployeeId $employeeId
Write-Host "User '$userPrincipalName' updated Employee ID '$employeeId'successfully."
}
}
else {
# User not found
Write-Host "User '$userPrincipalName' not found and couldn't set Employee ID $employeeId."
}
}
- DeletedThank you so much Manfred. Thank you so much.
- AyushDwivedi1610
Microsoft
Manfred101 Could you let me know the performance of this code, if I am trying to update 5000 records, typically how much time will this take?