Using PowerShell to remove duplicates in a SharePoint list

I recently had the requirement to remove all the duplicate values out of a SharePoint list. It turned out that the items were not completely unique, but shared multiple column values. After many failed attempts and intense google-fu, I found the following PowerShell script on a blog that had found it on a different blog.

I think this is a very elegant solution, it groups items in the given list by a number of columns and then either lists all the items or deletes all but the first item in each group.

Add-PSSnapin microsoft.sharepoint.powershell
$web = Get-SPWeb -Identity <SP Site>
$list = $web.Lists["<List>"]
$AllDuplicates = $list.Items.GetDataTable() | Group-Object <Item Properties> | where {$_.count -gt 1}
$count = 1
$max = $AllDuplicates.Count
foreach($duplicate in $AllDuplicates)
{
#
### uncomment string below to delete duplicated records
#
#$duplicate.group | Select-Object -Skip 1 | % {$list.GetItemById($_.ID).Delete()}
#
### string below displays found duplicated records
#
Write-Host $duplicate.Name
}
Remove-PsSnapin Microsoft.SharePoint.PowerShell

I hope this proves as useful to everyone as it did to me, and if you know the originator of this snippet, please let me know in the comments and I’ll link to the original source.

Advertisement