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?
2 Answers
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]@{
ID=$_.ID;
Title=$_.Title;
WhatEverField=$_["someCoolField"];
}
}
} 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.
try this
$MyWeb = WebUrl
$MyList = MyWeb.Lists["ListName"]
$MyItems = $MyList.Items
$MyItems | Select Fields.. | Export-Csv
FilePath
$MyWeb.Dispose()
Sources:
-
Is there any restriction on number of list items for this type of transfer?Sunil– Sunil2014-12-11 15:56:15 +00:00Commented Dec 11, 2014 at 15:56
-
The same limits as SharePoint enforces. Try that technique along with Kirk Evans' - PS:Learned a new word... LakhChoggo– Choggo2014-12-11 16:02:32 +00:00Commented Dec 11, 2014 at 16:02
-
1 Lakh means 100,000 numberSunil– Sunil2014-12-11 16:21:27 +00:00Commented Dec 11, 2014 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– Colin2016-03-28 05:22:00 +00:00Commented Mar 28, 2016 at 5:22