通过excel批量删除AD域用户账号

Question

工程师您好：

我现在手头上有一个excel表格，里面几千个AD域账号的姓名，如何通过powershell批量把这些用户的AD域账号删除。

谢谢

Answer 1

Microsoft provides us with the PowerShell module for ActiveDirectory, but it needs to be supported by installing RSAT(Remote Server Administration Tools).

The following is based on AD's PS module to achieve batch deletion of AD user function, read the first column(user name) of Excel, and then call the Remove-ADUser function to delete, it is so simple.

Of course, if you want to read from other columns or the second row, then you need to slightly modify the code.

About RSAT: https://learn.microsoft.com/zh-cn/troubleshoot/windows-server/system-management-components/remote-server-administration-tools

About AD's PS module: https://learn.microsoft.com/en-us/powershell/module/activedirectory

#########################################################
##
## Read the username in the first column and delete the user from AD
##
## FileName: RemoveAdUsersWithExcel.ps1
## Usage: RemoveAdUsersWithExcel.ps1 "D:\path\to\file.xlsx"
##
## Author: Allen Cai
## Created: 2024-10-16
##
#########################################################

Import-Module ActiveDirectory

Function RemoveAdUsers ($File) {
    # Create an Excel object
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false

    # Open the workbook
    $wb = $Excel.Workbooks.Open($File)
    
    # Check if the workbook has any sheets
    if ($wb.Sheets.Count -le 0) {
        Write-Host "No sheets found in the workbook"
        $Excel.Quit()
        exit
    }

    # Get the first sheet
    $ws = $wb.Sheets.Item(1)
    
    # Loop through the rows in the sheet
    foreach ($row in 1..$ws.UsedRange.Rows.Count) {
        # Get the username from the first column
        $userName = $ws.Cells.Item($row, 1).Value2
        # Check if the username is not null
        if ($null -ne $userName) {
            try {
                Write-Host "Deleting user: $userName"

                # Remove the user from AD
                $result = Remove-ADUser -Identity $userName
                # Check if the user was deleted successfully
                if ($result) {
                    Write-Host "User $userName deleted" -ForegroundColor Green
                } else {
                    Write-Host "Failed to delete user: $userName" -ForegroundColor Yellow
                }
            }
            catch {
                Write-Host "Failed to delete user: $userName. Error: $_" -ForegroundColor Red
            }
        }
    }

    # Close the excel workbook
    $Excel.Quit()
    exit
}

# Check if a file path was provided as an argument
if ($args.Length -eq 0) {
    Write-Host "Please provide a file path as an argument"
} else {
    # Check if the file or directory exists
    if (-not (Test-Path $args[0])) {
        Write-Host "File or directory does not exist"
        exit
    }

    # Call the function to convert the Excel file to CSV
    $file = Get-Item $args[0]
    RemoveAdUsers $file
}

Answer 2

Microsoft provides us with the PS module for AD, but it needs to be supported by installing RSAT.

The following is based on AD's PS module to achieve bulk deletion of AD user functions, read the first column of Excel, and then call the Remove-ADUser function to delete, it is so simple.

Of course, if you want to read from other columns or the second row, then you need to slightly modify the code.

#########################################################
##
## Read the username in the first column and delete the user from AD
##
## FileName: RemoveAdUsersWithExcel.ps1
## Usage: RemoveAdUsersWithExcel.ps1 "D:\path\to\file.xlsx"
##
## Author: Allen Cai
## Created: 2024-10-16
##
#########################################################

Import-Module ActiveDirectory

Function RemoveAdUsers ($File) {
    # Create an Excel object
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false

    # Open the workbook
    $wb = $Excel.Workbooks.Open($File)
    
    # Check if the workbook has any sheets
    if ($wb.Sheets.Count -le 0) {
        Write-Host "No sheets found in the workbook"
        $Excel.Quit()
        exit
    }

    # Get the first sheet
    $ws = $wb.Sheets.Item(1)
    
    # Loop through the rows in the sheet
    foreach ($row in 1..$ws.UsedRange.Rows.Count) {
        # Get the username from the first column
        $userName = $ws.Cells.Item($row, 1).Value2
        # Check if the username is not null
        if ($null -ne $userName) {
            try {
                Write-Host "Deleting user: $userName"

                # Remove the user from AD
                $result = Remove-ADUser -Identity $userName
                # Check if the user was deleted successfully
                if ($result) {
                    Write-Host "User $userName deleted" -ForegroundColor Green
                } else {
                    Write-Host "Failed to delete user: $userName" -ForegroundColor Yellow
                }
            }
            catch {
                Write-Host "Failed to delete user: $userName. Error: $_" -ForegroundColor Red
            }
        }
    }

    # Close the excel workbook
    $Excel.Quit()
    exit
}

# Check if a file path was provided as an argument
if ($args.Length -eq 0) {
    Write-Host "Please provide a file path as an argument"
} else {
    # Check if the file or directory exists
    if (-not (Test-Path $args[0])) {
        Write-Host "File or directory does not exist"
        exit
    }

    # Call the function to convert the Excel file to CSV
    $file = Get-Item $args[0]
    RemoveAdUsers $file
}

About RSAT: https://learn.microsoft.com/zh-cn/troubleshoot/windows-server/system-management-components/remote-server-administration-tools

About AD's PS module: https://learn.microsoft.com/en-us/powershell/module/activedirectory

Answer 3

尊敬的客户，您好！

感谢您在 Q&A 论坛上发帖。

不好意思我们不直接为用户写script，但可以为您提供一些思路：

1.Excel文件中准备一列域用户名

2.安装ImportExcel模块和ActiveDirectory模块

可以通过以下命令安装：

Install-Module -Name ImportExcel Install-Module -Name ActiveDirectory

3.读取excel表，用Remove -ADuser 删除用户。

如需更多信息，参考链接如下:

Remove-ADUser (ActiveDirectory) | Microsoft Learn

GitHub - dfinke/ImportExcel: PowerShell module to import/export Excel spreadsheets, without Excel

我希望以上信息对您有所帮助。

如果您有任何问题或疑虑，请随时告诉我们。

Best Regards,

Daisy Zhou

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

如果答案有帮助，请点击“接受答案”并投赞成票。