fetch more than 5000 items from sharepoint list view in sharepoint online and export it to the CSV File

Question

we have requirement to fetch more than 5000 items from the sharepoint list and export it to csv file using csom / Rest Api

how we can achieve this?

any guidance

Answer 1

To overcome the 5000 item limit you will need to use Batch CAML queries in CSOM and then paginate by using the ListItemCollectionPosition property.

You can refer to code example at this link Large list issue with CSOM

Answer 2

You could create indexed column and then use the indexed column in CSOM CAML Query and then use this CSOM Script to export items to CSV:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint.Client') | Out-Null

$login = '[email protected]'
$password = ConvertTo-SecureString 'password' -AsPlainText -Force
$webUrl = 'https://test.sharepoint.com/sites/test/'
$listName = 'My List'

$context = New-Object Microsoft.SharePoint.Client.ClientContext $webUrl
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials $login, $password

$web = $context.Web
$list = $web.Lists.GetByTitle($listName)
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$items = $list.GetItems($query)

$context.Load($items)
$context.ExecuteQuery()

$items | Select @{ L = "Title"; E = { $_["Title"] } }, @{ L = "Created"; E = { $_["Created"] } } | Export-Csv -Path export.csv

Overcoming the List View Threshold in SharePoint CAML queries