Bulk AD Update durch Excel Datei

On 21. Dezember 2021, in Allgemein, Von

Comment : This script updates AD users attributes from excel sheet.

Version : 1.1

This command has been added to script.

[threading.thread]::CurrentThread.CurrentCulture = ‚en-US‘


Declare file path and sheet name

$ErrorActionpreference = „continue“

[threading.thread]::CurrentThread.CurrentCulture = ‚de-DE‘

$file = „C:\Path\TEST.xlsx“

Create an Excel.Application instance and open that file

$Excelobject = New-Object -ComObject Excel.Application
$Workbook = $Excelobject.Workbooks.Open($file)
$sheetName = „Tabelle1“
$sheet = $Workbook.Worksheets.Item($sheetName)


Count max row

$rowMax = ($sheet.UsedRange.Rows).count

Count max column

$colMax = ($sheet.UsedRange.Columns).count
$hash = @{}
$server = „DC01.company.com“

Specify starting positions

$row,$col = 1,1
$updatedCount = 0

loop for rows

for ($i=1; $i -le $rowMax-1; $i++)

#loop for columns
for($c=0; $c -le $colMax-1; $c++)
  #Get all columns values to a hash
  $hash += @{$sheet.Cells.Item($row,$col+$c).text =  $sheet.Cells.Item($row+$i,$col+$c).text}


#Create an object and assign hash keys as object property
$Object = New-Object -TypeName PSObject -Property $hash

#Get User via SamAccountname
$user = Get-ADUser -Filter {„SamAccountName -eq ‚$($Object.sAMAccountName)'“} -Server $server -Credential $credential

#Set Users attribute with matched object attribute
$user | Set-ADUser -GivenName $Object.GivenName -Surname $Object.SN
-DisplayName $Object.Displayname -OfficePhone $Object.PhoneNumber
# -Description $Object.Description -Country $Object.Country
-Office $Object.Office -Title $Object.Title
-Company $Object.Company -Department $Object.Department
-Manager $Object.Manager `
-Mobile $Object.Mobile
#-Fax $Object.Fax

#If you want to edit Object common name, you can remove enable two lines below.

#$userguid = $user.ObjectGUID.Guid
#$user | Rename-ADObject -NewName $Object.DisplayName -Server $server -Credential $credential

$hash = @{}
Write-Host $User.Name „- User attributes have been updated.“ -ForegroundColor Yellow
Start-Sleep -s 1
$updatedCount += 1


Write-Host $updatedCount „Users have been updated“ -ForegroundColor Green

close excel file



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert