Share via

SharePoint: Using PowerShell to Perform a Bulk Delete Operation

  • Article

Introduction

Have you ever written a really great PowerShell script for migrating data into SharePoint, only to have made the mistake of adding tens of thousands of new list items into the wrong SharePoint list?

Oops.

Have you ever tried deleting a few hundred list items using SPListItemCollection.DeleteItemById(id) or SPListItemCollection.Delete(index)? It's painfully slow, so it's never going to work (when you're in a hurry) when you need to delete tens of thousands of items, not to mention the problems with list threshold exceptions!

There is a solution! Perform a batch query. Using PowerShell, use the SPQuery class to query the list for the items that need to be deleted, and then use the query results to build an XML document that can be used with SPWeb.ProcessBatchData to perform a batch delete operation. Easy, and much faster than waiting for a for-each loop to delete all those items using DeleteItemById(id)!

Example

In this example SPQuery is used to query the smarEnteredTerritorialWaters list for items created by the current user since the time of the import. The query results are then used to create an XML file to be used with the SPWeb.ProcessBatchData method. This example comes from a real world scenario where 32,000 items needed to be deleted.

Create the query:

$list = (Get-Spweb http://devmy101).GetList("http://devmy101/Lists/smarEnteredTerritorialWaters")
$query = New-Object Microsoft.SharePoint.SPQuery;
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = 2000;
$query.Query = '<Where><And><Eq><FieldRef Name="Author"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq><Gt><FieldRef Name="Created"/><Value Type="DateTime" IncludeTimeValue="TRUE">2013-07-10T14:20:00Z</Value></Gt></And></Where>';

Build the batch operation command (note the query is limited to returning 2000 items at a time, and uses the ListItemCollectionPosition property to continue retrieving items in batches of 2000 until all the items have been queried. See the MSDN documentation on SPQuery.ListItemCollectionPosition Property for more info.) 

 $itemCount = 0;            $listId = $list.ID;            [System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder";            $batchXml.Append("");$command = [System.String]::Format( "{0}{1}Delete", $listId, "{0}" );do{$listItems = $list.GetItems($query)$query.ListItemCollectionPosition = $listItems.ListItemCollectionPositionforeach ($item in $listItems){if($item -ne $null){$batchXml.Append([System.String]::Format($command, $item.ID.ToString())) | Out-Null;}}}while ($query.ListItemCollectionPosition -ne $null)$batchXml.Append("");

Lastly, and most importantly, run the query!

 $web = Get-Spweb http://inceweb/HKMarineDB;            $web.ProcessBatchData($batchXml.ToString()) | Out-Null;

See Also

Reference

This content originally came from a blog post of Matthew Yarlett, that can be found here: Well that was fun! (Bulk deleting items from a SharePoint list) Credit is also given to the following authors, who were credited in the original blog post: