Well that was fun! (Bulk deleting items from a SharePoint list)

Today I made the mistake of adding approximately 32,000 new items into the wrong SharePoint list.

Oops.

So I was quickly on the hunt for some PowerShell examples of using batch operations to delete many items (have you ever tried deleting hundreds of list items using SPListItemCollection.DeleteItemById(id) or SPListItemCollection.Delete(index)? It's slow, so it's never going to work with tens of thousands of items, not to mention the problems with list threshold exceptions!).

With a little help from the internet (see blog acknowledgements below) I knocked up a quick script that uses SPQuery to query the list for the all items created since the time of my import (thankfully this list is used to record data imported on a schedule, so I could safely do this). Using the items returned, I built my batch query.

Phew... problem solved with minimal hair loss.

Firstly, thanks to the following blogs that helped:
Tim Odell - powershell-function-to-delete-all-sharepoint-list-items
Kirk Evens - iterating-large-sharepoint-lists-with-powershell

Now, back to the PowerShell...

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><Gt><FieldRef Name="Created"/><Value Type="DateTime" IncludeTimeValue="TRUE">2013-07-10T14:20:00Z</Value></Gt></Where>';

Build the 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 this MSDN documentation for more info.)

$itemCount = 0;
$listId = $list.ID;
[System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder";
$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>");
$command = [System.String]::Format( "<Method><SetList>{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>", $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;$itemCount++;}
    }
}
while ($query.ListItemCollectionPosition -ne $null)

$batchXml.Append("</Batch>");
$itemCount;

And lastly (and most importantly!), run the query

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