SharePoint: Best practices for uploading more than 5000 items to a library/list (aka large lists)
Requirement: Store thousands of documents in SharePoint Online (or on-premises) document library without facing any threshold (5000 item limit) issues.
This post is assuming that you either have a bulk of documents to be uploaded along with the metadata (in a CSV file) or expect the library to grow in thousands. So prepare for that.
To being with, you can store up to 30,000,000 documents (as of May 6th, 2017) in a document library by nesting folders (use flat view to get around folders navigation issue – see step 6), or using multiple views with proper filtering on metadata columns. As per TechNet article , This value may vary depending on how documents and folders are organized, and by the type and size of documents stored. Note, this article is for SP 2013, but this limit is same in SPO too - as noted in the same article.
I highly recommend going with multiple document library approach. If documents can be divided into ‘n’ number of categories based on the metadata. Then create ‘n’ number of libraries and upload files to respective libraries. Irrespective of the number of libraries you go with, below are the high level steps that you will need to follow.
Preparation before uploading documents:
It is important that below steps are completed before uploading the documents to the library. This is because once you exceed the 5000 limit, you cannot add indexes, views or columns until and unless the total documents is reduced to <5000.
- Finalize the metadata (columns) for the documents. Plan beyond the already identified metadata in your CSV files. This post is based on assumption that you already have the metadata identified and its in a CSV file. Try to think ahead and add additional metadata columns, even if those are empty/placeholder column types.
- In SharePoint site, create all the site columns corresponding to the metadata columns identified in Step 1.
- Create Content Type. Add all the site columns created in Step 2 to the content type.
- Create a single or multiple SP document libraries depending on the approach that’s decided. Associate the Content Type to the Document Library(ies).
- Go the library settings, and add indexes for all of the Site Columns, that will be used to filter or sort the data by users. These columns will be used in Step 6 for creating views. Note that you can have max up to 20 indices in a list/library.
- Plan and create as many Views as possible for the library. Make sure that the Filter or Sort conditions used for Views are set as Indexed columns. This is to avoid the 5000 item threshold limit. Also, limit the number of items shown in the View using the Item Limit property. If you are using nested folders, then you can leverage the ‘flat view’ property Show all items without folders while creating the views.
- Another great and useful feature that you can leverage is the Metadata Navigation feature. You must first enable it by navigating to Site Settings – Manage Site Features and activate ‘Metadata Navigation and Filtering’ feature. Once enabled, it will add Key Filters panel to the left navigation whenever a user navigates to this library. It automatically manages indexes too. Note: Metadata navigation feature is not supported in Modern library experience. Once this feature is enabled, the library will automatically revert to Classic UI.
- You might need additional prep work if you decide to use Search to let users find documents. See the last section in this email.
There are multiple approaches to upload large number of files.
- Although drag and drop works great, it’s not feasible for a large number of documents.
- Use OneDrive for Business (ODB) Next Gen sync client to sync the library to a local machine and then copy all the files to the local machine ODB folder. Then ODB sync client will take care of syncing/uploading those files to the SP library.
- Use Content Organizer feature – that will create a drop-off library, upload docs to this library and the documents will be routed to folders automatically. These folders are created automatically for every 5000 items.
- Use PowerShell/CSOM to upload the documents, read the metadata from CSV and add metadata to the columns.
- Steps 1 to 3, will require additional step to read the CSV and add the metadata to the columns
How does users consume these documents once uploaded to SharePoint:
- Let users leverage the Views that’s created.
- Let users use the Metadata navigation that can be helpful to filter and find data
- Let search index the documents and use search center or provide custom page that is powered by search API. If planning to depend on search, you may need additional work:
- Ensure all the crawled properties are mapped to managed properties. If new managed properties are created, then you must re-index the library.
- It’s recommended to setup these mappings before uploading bulk of these documents.