Iterating Large SharePoint Lists with PowerShell

In a previous post, I wrote about creating a large list with PowerShell.  Now that the data is in SharePoint, how do you get the data back out?

As a reminder, here is the structure that I created, where each “subsubfolder” contains 500 items, giving us a grand total of 50,000 items in a list named “LargeList”.

Rather than do something like recurse through the folders in a large list using the SharePoint object model, we can tell the SPQuery object to query recursively, effectively providing us a flat view of the data.  However, that introduces a new problem… we have 50,000 items in that list!  The most we are allowed to query at a time (due to the new list throttling goodness in SharePoint 2010) is 2000 items, we definitely don’t want to pull back all 50,000 items.  Instead, we can use the SPQuery.ListItemCollectionPosition property to query in batches of 2000 items.

 $web = Get-SPWeb https://portal.sharepoint.com
$list = $web.Lists["LargeList"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 2000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        Write-Host $item.Title
    }
}
while ($spQuery.ListItemCollectionPosition -ne $null)
        
        

There you have it… we are using PowerShell to iterate the items in a huge SharePoint list while honoring the 2000 item throttle limit.

For More Information

SPQuery.ListItemCollectionPosition property

Creating a large list with PowerShell

Comments

• Anonymous
  February 13, 2012
  Thanks for posting this example.Cheers,Craig

• Anonymous
  June 07, 2015
  I have tried to get this working but keep getting the error below: The term 'Get-SPWeb' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. I'm running on my local machine and is trying to connect to my online sharepoint. I don't get any errors when adding the lines below: Import-Module SPList Add-Type -Path "c:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.dll" Add-Type -Path "c:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.Runtime.dll" Any suggestions on where I'm going wrong

• Anonymous
  July 13, 2015
  @Error Get-SPWeb ... Google. is your Friend ;) sharing-the-experience.blogspot.co.at/.../sharepoint-online-and-powershell-how-to.html

• Anonymous
  January 11, 2017
  Life saver. Thank you for posting this.