List vieew threshold and exporrt to excel issue

sns 9,241 Reputation points
2023-01-30T15:45:05.1533333+00:00

Library is showing below warning

User's image

and when I tried to export to excel for this library, it is showing below warning even for admins.

User's image

now we want to export to excel, how we can achieve this? Please suggest. ( we don't want to unlock list view for this library nor we don't want to increase the limit)

and second question is how we can indexing the columns for this library? Please suggest steps, based on which column we should choose indexing the columns in our case.

End user wants permanent solution for this issue ( exporting to excel)

library has around 35 to 40 look up columns,

choice columns around 10 to 12 columns

Person or group also around 10 columns

Please suggest. It is SharePoint 2016

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,420 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,186 questions
{count} votes

Accepted answer
  1. Yanli Jiang - MSFT 28,881 Reputation points Microsoft Vendor
    2023-02-01T07:20:27.3466667+00:00

    Hi @sns ,

    As for exporting the list to csv, because the number of items exceeds the threshold, it cannot be done directly through the UI interface. It is recommended that you use powershell to export.

    Run the following code:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
      
    #Set config variables
    $WebURL="https://projects.crescent.com"
    $ListName ="Projects"
    $OutPutFile = "C:\temp\Report.csv"
     
    #Delete the Output File if exists
    If (Test-Path $OutPutFile) { Remove-Item $OutPutFile }
      
    #Get Web and List Objects
    $Web = Get-SPWeb $WebURL
    $List = $Web.Lists[$ListName]
    Write-host "Total Number of Items Found:"$List.Itemcount
       
    #Define the CAML Query
    $BatchSize = 500
    $Query = New-Object Microsoft.SharePoint.SPQuery
    $Query.ViewXml = @"
        <View Scope='Recursive'>
            <Query>
                <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
            </Query>
            <RowLimit Paged='TRUE'>$BatchSize</RowLimit>
        </View>
    "@
     
    $Counter = 0
    #Process items in batch
    Do
    {
        #Get List Items
        $ListItems = $List.GetItems($Query)
        $Counter =  $Counter+$ListItems.Count
        Write-Progress -PercentComplete ($Counter / ($List.ItemCount) * 100) -Activity "Exporting List Items of '$($List.Title)'" -Status "Processing Items $Counter of $($List.ItemCount)"
     
        #Array to Hold Result - PSObjects
        $ListItemCollection = @()
        
        #Get All List items
        $ListItems | ForEach {
            #write-host "Processing Item ID:"$_["ID"]
       
            $ExportItem = New-Object PSObject
            #Get Each field - Exclude hidden
            Foreach($Field in $_.Fields | Where {!$_.hidden})
            {
                $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $_[$Field.InternalName] 
            }
            #Add the object with property to an Array
            $ListItemCollection += $ExportItem
        }
        #Export the result Array to CSV file
        $ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation -append
     
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
    }
    While($Query.ListItemCollectionPosition -ne $null)
     
    Write-host "List data exported to CSV Successfully!" -f Green
    
    

    Regarding adding indexes, you can follow the steps below to add indexes according to your actual situation.

    Go to list settings-->column (section)-->Indexed columns-->Create a new index.

    • To create a simple index, select a column that is not a Lookup column in the Primary Column section, under Primary column for this index.
    • To create a compound index, select a lookup column as the primary column for this index. Use a different column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.
    • Notes: 
      • Be sure to select a supported column to index
      • If you are adding an index to avoid a List View Threshold error, ensure that the field used is not a lookup field. Please see supported column to determine which field types are lookup fields.

    For more information, please refer to:

    https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0


    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 additional answers

Sort by: Most helpful

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.