Kirk Evans Blog

.NET From a Markup Perspective

Iterating Large SharePoint Lists with PowerShell

Iterating Large SharePoint Lists with PowerShell

Rate This
  • Comments 1

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”.

image

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 http://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

  • Thanks for posting this example.

    Cheers,

    Craig

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
Translate This Page
Search
Archive
Archives