While working with SharePoint, I was often required to insert more than 5,000 items into a SharePoint Online list. This will be very time-consuming if you do it through a browser, so you can use a PowerShell script. In this tutorial, I will show you how to add more than 5,000 items to a SharePoint Online list using PnP PowerShell.
Add More Than 5000 Items to a SharePoint Online List using PnP PowerShell
SharePoint Online lists have a default view threshold of 5000 items, which can pose challenges when working with large datasets. SharePoint Online imposes this threshold to maintain performance. However, PnP PowerShell can efficiently add more than 5000 items to a SharePoint Online list.
Here, I have created a SharePoint Online list having the following columns:
- Title (Default column)
- Description
- Quantity
The empty list looks like the following screenshot:
I will show you how to add more than 5000 items to the above SharePoint list using the below two methods.
Method 1: Batch Processing
The most practical approach is to use batch processing. This method allows us to add items in smaller chunks, ensuring we don’t hit the list view threshold.
Here’s an example script using PnP PowerShell:
# Connect to SharePoint Online
Connect-PnPOnline -Url "https://szg52.sharepoint.com/sites/PowerPlatformFAQs/" -Interactive
# Define list name
$listName = "LargeInventoryList"
# Prepare data (example with 5500 items)
$items = 1..5500 | ForEach-Object {
@{
Title = "Item $_"
Description = "Description for item $_"
Quantity = Get-Random -Minimum 1 -Maximum 1000
}
}
# Process items in batches
$batchSize = 2000
for ($i = 0; $i -lt $items.Count; $i += $batchSize) {
$batch = $items[$i..($i + $batchSize - 1)]
$list = Get-PnPList -Identity $listName
$batch | ForEach-Object {
Add-PnPListItem -List $list -Values $_
}
Write-Host "Processed items $i to $($i + $batch.Count - 1)"
}
Write-Host "All items added successfully!"This script adds items in batches of 2000, which helps avoid hitting the list view threshold.
The screenshot below shows that it added 5,500 items to the SharePoint list. I executed the script using VS code, but you can use any editor according to your preferences.
To confirm this, open the SharePoint Online list settings page, where you can see the total number of items presented on the list.
Check out Delete All Items from a SharePoint List using PnP PowerShell
Method 2: Using PnP Batch Cmdlet
Let me show you the other method to bulk insert items into a SharePoint Online list using PnP PowerShell.
PnP PowerShell provides a dedicated batch cmdlet that can further optimize the process.
Here is the complete PowerShell script.
# Connect to SharePoint Online
Connect-PnPOnline -Url "https://szg52.sharepoint.com/sites/PowerPlatformFAQs/" -Interactive
# Define list name
$listName = "LargeInventoryList"
# Prepare data (example with 5500 items)
$items = 1..5500 | ForEach-Object {
@{
Title = "Item $_"
Description = "Description for item $_"
Quantity = Get-Random -Minimum 1 -Maximum 1000
}
}
# Process items in batches
$batchSize = 2000
for ($i = 0; $i -lt $items.Count; $i += $batchSize) {
$batch = $items[$i..($i + $batchSize - 1)]
Invoke-PnPQuery -ErrorAction Stop
$batchCmd = New-PnPBatch
$batch | ForEach-Object {
Add-PnPListItem -List $listName -Values $_ -Batch $batchCmd
}
Invoke-PnPBatch -Batch $batchCmd
Write-Host "Processed items $i to $($i + $batch.Count - 1)"
}
Write-Host "All items added successfully!"This method uses the New-PnPBatch and Invoke-PnPBatch cmdlets to create and execute batches, which can be more efficient for large operations.
Conclusion
By using PnP PowerShell, you can easily add more than 5000 items to a SharePoint Online list. I have explained two methods and shown you how to add more than 5000 items to a SharePoint Online list using PnP PowerShell.
If you still have any questions, feel free to comment below.
You may also like the following tutorials:
- Create, Read, Update, and Delete an Item from a SharePoint Online List using PnP PowerShell
- Delete and Recover a SharePoint Online Site Using PowerShell and PnP PowerShell
- Disable Versioning in a SharePoint Library Using PnP PowerShell
- Import Data from Excel to SharePoint List using PnP PowerShell
Bijay Kumar is an esteemed author and the mind behind PowerShellFAQs.com, where he shares his extensive knowledge and expertise in PowerShell, with a particular focus on SharePoint projects. Recognized for his contributions to the tech community, Bijay has been honored with the prestigious Microsoft MVP award. With over 15 years of experience in the software industry, he has a rich professional background, having worked with industry giants such as HP and TCS. His insights and guidance have made him a respected figure in the world of software development and administration. Read more.
