How to Export SharePoint list with over 5000 records to excel

New Contributor

Hello everyone,


Do you have an idea of how to export a SharePoint list with large data, over then 5000 records?

when I export to csv, it shows only 5000 and if I export it as Excel query, it show only 500 records!


Thanks you for any suggestions.

4 Replies



Do you want to export it one time only? If yes then you can use below PowerShell script to export list items into CSV.

Below script is using PnP.PowerShell Module so you need to install it before using it. 


Make sure that you update few variables added in MainProcess function i.e. $siteUrl, $listUrl and $outputFilePath


Function GetAllListItems($ctx,$listUrl)
   #Get the List
    $List = $Ctx.Web.GetList($ctx.Web.ServerRelativeUrl + "/" + $listUrl)
    #Define Query to get List Items in batch
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = @"
    <View Scope='RecursiveAll'>
            <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        <RowLimit Paged="TRUE">5000</RowLimit>

    $allListItems = @()
    #Get List Items in Batch
        $ListItems = $List.GetItems($Query)
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

       foreach($listItem in $ListItems)
            $allListItems += $listItem

    While($Query.ListItemCollectionPosition -ne $null)

    Return $allListItems


Function MainProcress()

    $siteUrl = ""
    $listUrl = "Lists/TESTList"
    $outputFilePath = "D:\exportedlist.csv"

    $includeHiddenField = $False
    $includeReadonlyField = $False

    Connect-PnPOnline -Url $siteUrl -Interactive

    $ctx = Get-PnPContext

    $listFields = Get-PnPField -List $listUrl |? { $_.Hidden -eq $includeHiddenField -AND $_.ReadOnlyField -eq $includeReadonlyField} | Select -ExpandProperty internalname

    $count = 0

    $listItems= GetAllListItems -ctx $ctx -listUrl $listUrl


    # Loop through the list items  
    foreach($listItem in $listItems)  
    {   $count = $count+1
        Write-Progress -Activity "Exporting" -Status "$($count/$listItems.Count*100)% Complete:" -PercentComplete $($count/$listItems.Count*100)
        $obj=New-Object PSObject              

        $listItem.FieldValues.GetEnumerator() | Where-Object { $_.Key -in $listFields }| ForEach-Object{ 
                    if($_.Value.LookupValue){$obj | Add-Member Noteproperty $_.Key $_.Value.LookupValue}
                    else{$obj | Add-Member Noteproperty $_.Key $_.Value}                                        
     $hashtable | export-csv $outputFilePath -NoTypeInformation  




Reference Links used for this script:




Hope it will helpful to you and if so then please mark this as best response and like this answer for better reach of your question to other people.



@kalpeshvaghela  Seems this needs an administrator account to run, I am the creator of the list and the site on SharePoint but it asks for an admin account.
Is there a way to overcome this issue?



As this script is using PnP.PowerShell, it will require one time consent which can be done by Global Admin User Only.

See Authentication steps here.


If it's not possible for you then you need to convert this script to only CSOM PowerShell


Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community