How to load > 1 million items to a SharePoint Online List

0
votes

Can anyone suggest a reasonably practical and efficient way to load 1.2 million test items into a SharePoint Online list?

Background: We've decided to build a new application on top of SharePoint Online. Other application architecture options have all proved non-viable for various reasons. The application will use several SharePoint lists for persistence, one of which will be large, about 1.2 million items at peak. (Yes, we're planning ways to handle the 5000 item view limit.) To test viability of the architecture (including those view limit tactics) we need to create 1.2M test items in a list. Nothing we've tried has been practical:

  • Tried making POST calls to the REST API, with 5 concurrent threads so it will finish in a reasonable time. This fails after a bit with a HTTP 429 "Too many requests".
  • Tried uploading a spreadsheet with 1.2M rows. This fails at 130K entries each time, and I don't see a practical way to either upload / append to an existing list, nor to append items from one list to another existing list.
  • Tried running a Workflow (SharePoint 2013 variety, if that matters). This works but runs way too slow single threaded and I'm hesitant to try multiple concurrent workflows because this is a shared environment and if I trash the server that would be way not good.

Thanks in advance for any pointers!

2
sharepoint

2 Answers

0
votes

You could try to use pnp powershell to add more than 1 million items.

$username = "[email protected]"
$password = "password"
$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $(convertto-securestring $Password -asplaintext -force)
Connect-PnPOnline -Url https://contoso.sharepoint.com/sites/dev -Credentials $cred
$ListName ="testn"
 for($i=0;$i -lt 1000001;$i++){
Add-PnPListItem -List $ListName -Values @{"Title" = "test";}    
}
0
votes

Fastest way to load and process items from SPO using PnPPowershell goes something like following. Idea is to not initialize the items collection in any variable and directly process the items by page size.

Get-PnpListItem -List "{ListName}" -Fields "Field1","Field2","Fieldn" -PageSize 5000 | % {$i=0}{

% {$i=0}{

Do not move { to next line, I know it's weird but if you move, then good luck.

$item = $_

#Do your stuff with $item

Write-Host $item.Id

}