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

adil 1,266 Reputation points
2024-10-30T21:11:48.6933333+00:00

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 )

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,365 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Emily Du-MSFT 47,221 Reputation points Microsoft Vendor
    2024-10-31T09:32:11.6033333+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.