When you are loading content from SharePoint OnPremise to SharePoint Online, the content migration can create some issues or frustations.
The basic case is the huge lists, with for my example more than 200'000 items, which is really more than all the limits implemented by Microsoft
- View limit: 2’000
- Alert into the SPList: 5’000
- SPList change blocked: 20’000
So the solution could be to import the same list content 2 times (at minima) and implement a cleanup to retreive for example:
- First load = Alive list with the last XX month of data (for example the current year)
- Second Load = Archive list with all the content for archive and read access
- Optional loads = If the site owner wants to have a list per Year for example
With that strategy, there are a certain number of lists in SPO which will need to be cleaned after the loading. The only solution is to delete the items (like for my example 180'000) which are not in the alive dataset.
It's not possible to do that using the basic visual solution (WebSite or Access), and the PowerShell script using CSOM is one of the best for that.
That following script gives the capability to delete all the content before the 4th of January 2017:
[string]$username = "YourTenantAccount@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\FOLDERTOSTOREPWD\ExportedPWD-$($username).txt"[string]$SiteCollectionToClean = https://YourTenant.sharepoint.com/sites/YourSiteColl
[string]$ListToCleancontent = "YourListName"
[string]$ItemDateLimitToDelete = "2017-01-04"
[int]$ItemLimitNumber = 0
[int]$MyRowLimit = 500$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwdfunction Load-DLLandAssemblies
{
[string]$defaultDLLPath = ""# Load assemblies to PowerShell session
$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
[System.Reflection.Assembly]::LoadFile($defaultDLLPath)$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
[System.Reflection.Assembly]::LoadFile($defaultDLLPath)$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
[System.Reflection.Assembly]::LoadFile($defaultDLLPath)}
function QuickCleanAllListItems([string]$SiteURL, [string]$ListName, [int]$LimitItemNum)
{
$sw = New-Object System.Diagnostics.StopWatch
$sw.Start()$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
$ctx.RequestTimeout = 1000000 # millisecondsWrite-Host " ---------------------------------------------------------------------------------"
$spoWeb = $ctx.Web
$ctx.Load($spoWeb)
$ctx.ExecuteQuery()$MyListToClean = $spoWeb.Lists.GetByTitle($ListName)
$ctx.Load($MyListToClean)
$ctx.ExecuteQuery()
Write-Host "Site URL: ", $SiteURL
Write-Host "List Name:", $MyListToClean.TitleWrite-Host " Items Number before delete: ", $MyListToClean.ItemCount -foregroundcolor Yellow
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.DatesInUtc = $true
$query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$($ItemDateLimitToDelete)</Value></Eq></Where><OrderBy><FieldRef Name='Created'/></OrderBy></Query><RowLimit>1</RowLimit></View>"$itemLimitList = $MyListToClean.GetItems($query)
$ctx.Load($itemLimitList)
$ctx.ExecuteQuery()if($itemLimitList.Count -gt 0)
{
$ItemLimitNumber = $itemLimitList[$itemLimitList.Count-1].ID
[int]$looplimitcount
[int]$LoopRound = 0
DO
{
Write-Host " ========================================="
Write-Host " Deletion Round Number: ", $LoopRound -foregroundcolor Red$queryclear = New-Object Microsoft.SharePoint.Client.CamlQuery
$queryclear.DatesInUtc = $true
$queryclear.ViewXml = "<View><Query><Where><Lt><FieldRef Name='ID'/><Value Type='Counter'>$ItemLimitNumber</Value></Lt></Where><OrderBy><FieldRef Name='ID'/></OrderBy></Query><RowLimit>$MyRowLimit</RowLimit></View>"
$items = $MyListToClean.GetItems($queryclear)
$ctx.Load($items)
$ctx.ExecuteQuery()
$looplimitcount = $items.Count
if ($items.Count -gt 0)
{
for ($i=$items.Count-1; $i-ge0; $i--)
{
Write-Host " >> Item ID: ", $items[$i].ID, " ====>> Deleted" -foregroundcolor Magenta
$items[$i].DeleteObject()
}
$ctx.ExecuteQuery()
Write-Host " ====>> Update Applied !!!" -foregroundcolor Red
}
$LoopRound += 1
} WHILE ( $looplimitcount -gt 0)}
$sw.Stop()Write-Host " ---------------------------------------------------------------------------------"
$ctx.Load($MyListToClean)
$ctx.ExecuteQuery()
Write-Host "Items total after deletion: ", $MyListToClean.ItemCount -foregroundcolor Yellow
write-host "Items deleted in " $sw.Elapsed.ToString()}
Load-DLLandAssemblies
QuickCleanAllListItems $SiteCollectionToClean $ListToCleancontent $ItemNumberLimitToDelete
You can modify that one if you need to use your own filter (via the CAMLQuery)
For my case, the 180.000 items were deleted after a couple of hours instead of many days with the website.
Original article (in French):
Articles related:
- http://www.c-sharpcorner.com/code/3074/delete-list-items-csom-sharepoint-online.aspx
- https://karinebosch.wordpress.com/2012/02/03/caml-and-the-client-object-model/
- https://blogs.technet.microsoft.com/heyscriptingguy/2014/05/05/powershell-looping-understanding-and-...
- http://blogs.developpeur.org/fabrice69/archive/2016/10/18/office-365-comment-viter-de-taper-son-mot-...
Fabrice Romelard [MVP]