Kirk Evans Blog

.NET From a Markup Perspective

Iterating Large SharePoint Lists with PowerShell

Iterating Large SharePoint Lists with PowerShell

Rate This
  • Comments 3

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
$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 

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



  • 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 Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"

    Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

    Any suggestions on where I'm going wrong

  • @Error Get-SPWeb ...

    Google. is your Friend ;)

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
Translate This Page