What are the limitations and best practices export custom list to excel ?

Question

Hi,

I created some custom lists in Team Site in SharePoint 2019 and created list items with custom columns,

here

1. What are the limitations and best practices export custom list to excel how many number of list items can be export to excel as i required to create many list items morethan 500
2. and attachments

here i required to export as complete .xlsx ( excel file )

Answer 1

1.Limitations about export list to excel.

(1)SharePoint has limitations on the size of files that can be exported to Excel. The maximum number of rows you can export to an Excel file is 5000.

(2)Here are supported data types, please check.

• Text (single line)
• Text (multiple lines)
• Currency
• Date/time
• Number
• Hyperlink (URL)

2.You could use PowerShell to download attachments from SharePoint list.

#Site URL and List Name variables
$WebURL = "https://intranet.crescent.com/sites/purchase"   
$ListName = "test"  
 
#Local folder to which attachments to be downloaded
$DownloadPath = "C:\Docs"    
 
#Get the web
$Web = Get-SPWeb $WebURL
#Get the Library
$List = $Web.Lists[$ListName]   
 
#Loop through each List item
foreach ($ListItem in $List.Items)
{  
    #Set path to save attachment
    $DestinationFolder = $DownloadPath + "\" + $ListItem.ID         
 
    #Check if folder exists already. If not, create the folder
    if (!(Test-Path -path $DestinationFolder))       
    {           
        New-Item $DestinationFolder -type directory         
    }
   
    #Get all attachments
    $AttachmentsColl = $ListItem.Attachments
 
    #Loop through each attachment
    foreach ($Attachment in $AttachmentsColl)   
    {
        #Get the attachment File      
        $file = $web.GetFile($listItem.Attachments.UrlPrefix + $Attachment)       
        $bytes = $file.OpenBinary()               
 
        #Save the attachment as a file 
        $FilePath = $DestinationFolder + " \" + $Attachment
        $fs = new-object System.IO.FileStream($FilePath, "OpenOrCreate")
        $fs.Write($bytes, 0 , $bytes.Length)   
        $fs.Close()   
    }
}

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.