SharePoint: Using PowerShell to Perform a Bulk Delete Operation

SharePoint: Using PowerShell to Perform a Bulk Delete Operation

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) with tens of thousands of items, not to mention the problems with list threshold exceptions!

There is a solution! 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. The 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.ListItemCollectionPosition
foreach ($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;

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:

Tim Odell - powershell-function-to-delete-all-sharepoint-list-items
Kirk Evens - iterating-large-sharepoint-lists-with-powershell
Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Richard Mueller edited Revision 3. Comment: Removed extra space in tag "SharePoint  2010"

  • Matthew Yarlett edited Revision 2. Comment: Fixed HTML tags at the end of the document

  • Matthew Yarlett edited Revision 1. Comment: Minor changes to wording.

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Richard Mueller edited Revision 3. Comment: Removed extra space in tag "SharePoint  2010"

  • Matthew Yarlett edited Revision 2. Comment: Fixed HTML tags at the end of the document

  • Matthew Yarlett edited Revision 1. Comment: Minor changes to wording.

  • The link to the blog at the end seems to be truncated (the title, the link itself works)

Page 1 of 1 (4 items)