Friday, April 12, 2019

Quickly delete list items in batch, through CSOM and PowerShell

"Manage Site Content and Structure" in SharePoint Online had gone. So, we cannot manually delete the items easily, anymore.

If there is less than 5000 items in the list, things are much easier: we can save the list template, then delete the list, and rebuild it through the list template. But, if there are more than 10,000 items, it's time consuming.

Here is a simple solution: Using PowerShell to load all items into an array variable, then delete the items one by one, and then commit the requests in batch.

During my test, to delete 48622 items from one list, it took around 6300 seconds. So it's around 7.7 items per second.

If you know any way to improve the performance, please let me know.

Below is the major part of the PowerShell script.

$i = 0
$iItemId = 0

$web = $ctx.Web
$lists =$web.Lists
$listTarget = $lists.GetByTitle($strListName)
$ctx.Load($web)
$ctx.Load($lists)
$ctx.Load($listTarget)

$ctx.ExecuteQuery()

$viewFields = ""
$caml = "$viewFields1000"
$caml += '
'$position = $null
$allItems = @()

Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ViewXml = $caml
    $camlQuery.ListItemCollectionPosition = $position

    $listItems = $listTarget.getItems($camlQuery)
    $ctx.Load($listItems)
    $ctx.ExecuteQuery()

    $position = $listItems.ListItemCollectionPosition
    $allItems += $listItems
}
Until($position -eq $null)

if ($allItems.Count -lt 1){
    Write-Host "$(__FILE__), No item needs to be deleted. caml=$caml"
    return 0
}

$iItemCount = $allItems.Count
$progressBarTitle = "$(__FILE__), Scan list '$strListName', iItemCount=$iItemCount, deleteSubFolders=$deleteSubFolders, DaysOfDataToKeep=$DaysOfDataToKeep, ReadOnly=$ReadOnly"
foreach($item in $allItems){
    $i++
    Write-Progress -Activity $progressBarTitle -PercentComplete (($i/$iItemCount)*100) -Status "Working"
 
    $iItemId = $item.Id
    if($item.FileSystemObjectType -eq "File" -or ($item.FileSystemObjectType -eq "Folder" -and $deleteSubFolders)){
        $listTarget.getitembyid($iItemId).Recycle() > $null
    }
    else{
        Write-Host "Invalid FileSystemObjectType, item(FSObjType: $($item.FileSystemObjectType), FileRef: $($item.FieldValues.FileRef))."
        Continue
    }
    if ($i % 1000 -eq 0){
        $ctx.ExecuteQuery()
    }
    Write-Verbose "item(FSObjType: $($item.FieldValues.FSObjType), FileRef: $($item.FieldValues.FileRef)) is deleted."
}
$ctx.ExecuteQuery()

No comments:

Post a Comment