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

@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/READ... 

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

 

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?

@Greg68 

 

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