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.