We can successfully migrate a list to the excel through power-shell, but if a list is having 1 Lakh of data then can we transfer through power-shell? Is there any restriction on number of list items for this type of transfer?


As one of the above comments mentions Kirk Evans has nice articel explaining that.

Basically you would not list all items using (Get-SPWeb http://foo.bar/).Lists["ListName"].Items - this will most likely error because of the threshold.

You would create a query (New-Object Microsoft.SharePoint.SPQuery) which can be limited to a sensible number of items ($spQuery.RowLimit = 2000 in the article above) and then run that query against the list (Get-SPWeb http://foo.bar/).Lists["ListName"].GetItems($spQuery)).

When the query is run the result will have a property ListItemCollectionPosition. If that is $null the RowLimit has not been reached and you have all the items you queried for. If the ListItemCollectionPosition is not null you can use that on the query as a new "starting-point" for the next query ($spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition) - run the query again and you'll get the next batch of items.

So, to "copy" Kirk Evans freely:

$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
    $listItems | % {
        Write-Output [pscustomobject]@{
} while ($spQuery.ListItemCollectionPosition -ne $null)

The result of the above will be a list of objects (note, that you'll need PowerShell v3 for the [pscustomobject@{...}]-feature), wich can be nicely piped to Export-CSV or even Out-GridView.

| improve this answer | |

try this

$MyWeb = WebUrl 
$MyList = MyWeb.Lists["ListName"] 
$MyItems = $MyList.Items 
$MyItems | Select Fields.. | Export-Csv


| improve this answer | |
  • Is there any restriction on number of list items for this type of transfer? – Sunil Dec 11 '14 at 15:56
  • The same limits as SharePoint enforces. Try that technique along with Kirk Evans' - PS:Learned a new word... Lakh – Choggo Dec 11 '14 at 16:02
  • 1 Lakh means 100,000 number – Sunil Dec 11 '14 at 16:21
  • If your list contains 100000 items I would urge you to rethink that. Usually lists have a 5000 item threshold, going over that can severely impact performance, – Colin Mar 28 '16 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.