kwizcom banner advertisement

SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items)

Problem:
A typical PowerShell CSOM script to get list items from a list resulted in an error:
"Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

Root cause: As the error message says, we have 5000 as the hard limit for list view threshold in SharePoint online. Unlike SharePoint on-premises, this threshold limit can't be increased!

Solution: There are various solutions to deal with this issue. Here is mine: Get list items in batch!

PowerShell to Get List Items from Large Lists:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Config Parameters
$SiteURL="https://Crescent.SharePoint.com"
$ListName="Projects"
$BatchSize="1000"

Try {
    $Cred= Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials
        
    #Get the List
    $List = $Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Define Query to get List Items in batch
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = @"
    <View Scope='RecursiveAll'>
        <Query>
            <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        </Query>
        <RowLimit Paged="TRUE">$BatchSize</RowLimit>
    </View>
"@

    #Get List Items in Batch
    Do 
    {
        $ListItems = $List.GetItems($Query)
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()
        $ListItems.count
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
    }
    While($Query.ListItemCollectionPosition -ne $null)

}
Catch {
    write-host -f Red "Error Adding Items to List!" $_.Exception.Message
} 
Just make sure, your batch is lesser than 5000!
SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items) SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items) Reviewed by Salaudeen Rajack on December 03, 2016 Rating: 5

2 comments:

  1. Hi! I'm using this to try and export a large list I have. It ran successfully, but I would like to know where this export went? I read through the code, but I'm not sure how this data is exported.

    Thanks for the assistance

    ReplyDelete
    Replies
    1. Usually on the same directory where your script is saved. Or in your Default Home directory (Start >> Run >> type "." without quote and Enter)

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.