Add-PnPFile cmdlet issue with List View Threshold in SPO

Question

Am working on migrating about 600,000 files from another system into Sharepoint Online. Using a .csv export from the source system and the ImportExcel module to pull the metadata and file locations into PowerShell, and PnP Powershell to upload the files and set the metadata column values in SPO. The folder/file structure we are creating is one folder at the top level of a library, with a large number of folders underneath, and files in each folder. The issue I am seeing is that as soon as Powershell attempts to upload the 5001st folder to the second level of the library folder structure ( Library/Level1/Level2-Folder5001), we get a List View Threshold error in Powershell. This occurs regardless of whether the upload starts with zero folders in the destination or 4999.

Interesting thing is that I don't see the error when adding additional folders from the SPO GUI. All indexable metadata columns are indexed, am using the latest cross-platform PnP Powershell module (not the daily release) but had the same problem with the previous version.

How to work around this? Not familiar with using CSOM in PowerShell, but is that a better option? Is cycling through the spreadsheet array and uploading the files, hitting some arbitrary limit not recognized by the Default view in the SPO GUI? Stuck on this one. Thanks in advance for help!

Answer 1

Hi @DaveR ,

If you would like to upload a complete structure of folders and subfolders on Sharepoint Online you can do it using Powershell and CSOM, using the following script.

param(  
[Parameter(Mandatory=$true,HelpMessage="The root site collection URL")][string]$url_sharepoint,  
[Parameter(Mandatory=$true,HelpMessage="The relative path for the site site URL ___ /sites/subsite")][string]$url_site,  
[Parameter(Mandatory=$true,HelpMessage="The name for the library")][string]$library  
)  
$t1  = get-date  
$source_folder = ((new-object -com Shell.Application).BrowseForFolder(0, "Select the source folder", 0, "")).Self.Path  
$source_folder  
$url = $url_sharepoint+$Url_site  
  
cls  
Add-Type -Path "your_path\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "your_path\Microsoft.SharePoint.Client.Runtime.dll"  
Import-Module MSOnline  
$global:cred1 = Get-Credential  
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($url)  
$cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred1.username, $cred1.Password)   
$clientContext.Credentials = $cred  
$web = $clientContext.Web  
$clientContext.Load($web)  
$clientContext.ExecuteQuery()  
($web.Lists).Count  
$mylist = $web.GetList($url+"/"+$library)  
$clientContext.Load($mylist)  
$clientContext.ExecuteQuery()  
###################### get folders existing  in the SOURCE  
$directories= @()  
foreach ($file in (Get-ChildItem  -Recurse -Path $source_folder -Attributes Directory))  
{  
    ($file.FullName.ToLower()).replace($source_folder.ToLower()+'\','')  
    $directories +=($file.FullName.ToLower()).replace($source_folder.ToLower()+'\','')  
}  
foreach ($directory in $directories)  
{  
    $myfolder = $mylist.RootFolder  
    $clientContext.Load($myfolder)  
    $clientContext.ExecuteQuery()  
    $myfolder = $myfolder.Folders.Add($directory.split('\')[0])  
    $clientContext.Load($myfolder)  
    $clientContext.ExecuteQuery()  
    for ($i = 1; $i -le ($directory.split('\').Count-1) ; $i++)  
    {  
        #$directory.split('/')[$i]  
        $myfolder = $myfolder.folders.Add(($directory.split('\'))[$i])  
        $clientContext.Load($myfolder)  
        $clientContext.ExecuteQuery()  
    }  
}  
cls  
$t1  = get-date  
$i=1  
$count = ((Get-ChildItem -File  -Recurse -Path $source_folder) | Measure-Object -Property FullName ).Count  
foreach ($file in (Get-ChildItem -File  -Recurse -Path $source_folder))  
{  
    $t01  = get-date  
    $url_dest = $url_sharepoint+$Url_site+'/'+$library+(($file.FullName.ToLower()).Replace($source_folder.ToLower(),'')).Replace('\','/')  
    $FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)  
    $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation  
    $FileCreationInfo.Overwrite = $true  
    $FileCreationInfo.ContentStream = $FileStream  
    $FileCreationInfo.URL = $url_dest  
    $Upload = $mylist.RootFolder.Files.Add($FileCreationInfo)  
    $listItem = $upload.ListItemAllFields  
    $listItem['Title']=($file.Name).split('.')[0]  
    Write-Host " Uploading file $i/$count $url_dest"  
    $listItem.update()  
    $clientContext.Load($Upload)  
    $clientContext.ExecuteQuery()  
    $t02  = get-date  
    $speed ="{0:n2}" -f ($file.Length/($t02-$t01).TotalSeconds/1mb)  
    Write-Host "...................upload speed was " $speed " MB/sec"  
    $i++  
}  
$t2=get-date  
$size = "{0:n2}" -f (gci -path $source_folder -recurse | measure-object -property length -sum).sum  
cls  
$speed ="{0:n2}" -f ($size/($t2-$t1).TotalSeconds/1mb)  
Write-Host "Medium upload speed was " $speed " MB/sec"  

I uploaded my folder structure(more than 5000 subfolders) successfully to SharePoint Online. Please take notes to the below when using the script.

• url_sharepoint: the URL for root site collection(looks like: https://tenant.sharepoint.com)
• url_site: the relative path of the site (looks like: /sites/subsite_name)
• library: the name of the destination library

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
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.