How to Export SharePoint list with over 5000 records to excel

Greg68 

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. 

https://pnp.github.io/powershell/articles/installation.html 

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)
    $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">5000</RowLimit>
    </View>
"@
 

    $allListItems = @()
    #Get List Items in Batch
    Do
    {
        $ListItems = $List.GetItems($Query)
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()
        #$ListItems.count
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

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


    }
    While($Query.ListItemCollectionPosition -ne $null)

    Return $allListItems


}


Function MainProcress()
{

    $siteUrl = "https://contoso.sharepoint.com/sites/template"
    $listUrl = "Lists/TESTList"
    $outputFilePath = "D:\exportedlist.csv"

    $includeHiddenField = $False
    $includeReadonlyField = $False

    Connect-PnPOnline -Url $siteUrl -Interactive

    $ctx = Get-PnPContext
    $ctx.Load($ctx.Web)
    $ctx.ExecuteQuery()

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

    $count = 0

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

    $listItems.Count
    $hashTable=@()  

    # 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+=$obj;  
        $obj=$null;  
    }  
     $hashtable | export-csv $outputFilePath -NoTypeInformation  

}

MainProcress

Reference Links used for this script:

1. https://github.com/pnp/script-samples/blob/main/scripts/spo-export-sharepoint-list-items-to-csv/README.md/#L1 

2. https://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html 

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.