Update, Delete & Copy list items with powershell in sharepoint 2010

Update, Delete & Copy list items with powershell in sharepoint 2010
7 votes, 4.71 avg. rating (93% score)

Introduction

This post will cover the list utilities such as how to update, delete and copy list items with powershell. With the generic code, now the handling list should be easy in powershell.




Updating Items of List

Two common methods used for updating sharepoint list are GetItemById and GetItems

GetItemById method

GetItemById requires the id of the item to update

function Get-SPList($webUrl, $lstUrl)
{
	$webObj = Get-SPWeb -identity $webUrl
	$lstObj = $webObj.GetList($lstUrl);
	return $lstObj
}
$spList = Get-SPList -webUrl "http://mysitecollecton/MySite" -lstUrl "http://mysitecollecton/MySite/Lists/EmpInfo"
$spListItem = $spList.GetItemById(99)
$spListItem["Department"] = "NewDepartment"
$spListItem.Update()

The above snippet updates the list item where id is 99

GetItems method

GetItems method can be used to get all the results are subset of results using CAML query and then update the result set.
Following snippet gets employee information from ‘EmpInfo’ list whose department is ‘HR’ and updates their Description field value

function Get-SPList($webUrl, $lstUrl)
{
	$webObj = Get-SPWeb -identity $webUrl
	$lstObj = $webObj.GetList($lstUrl);
	return $lstObj
}
$spList = Get-SPList -webUrl "http://mysitecollecton/MySite" -lstUrl "http://mysitecollecton/MySite/Lists/EmpInfo"
$spQuery = new-object Microsoft.SharePoint.SPQuery
 $camlQuery =
 "<Where>
   <Eq>
    <FieldRef Name='Department' />
      <Value Type='Text'>HR</Value>
   </Eq>
 </Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 100
 $spListItemCollection = $spList.GetItems($spQuery)
  $spListItemCollection | ForEach-Object {
 $_["Description"] = "Hi there..."
 $_.Update()
 }



We can also use ‘GetItems’ method directly without using CAML query. Once after getting the results we can do a query.

$spList = Get-SPList -url "http://mysitecollecton/MySite/Lists/EmpInfo"
$spListItemCollection = $spList.GetItems()

But, with this method all the items in the list are read into memory; therefore, large lists may consume a lot of memory which hinders performance. So, always better to use CAML query with rowlimit to the get specific results and work only on them.

Deleting Item in List

For removing item in the list, first get an item using GetItemById method and then use Delete method of the SPListItem object
GetItemById method to retrieve a list item, and then remove the item.
Following code delete the item whose Id is 99

function Get-SPList($webUrl, $lstUrl)
{
	$webObj = Get-SPWeb -identity $webUrl
	$lstObj = $webObj.GetList($lstUrl);
	return $lstObj
}
$spList = Get-SPList -webUrl "http://mysitecollecton/MySite" -lstUrl "http://mysitecollecton/MySite/Lists/EmpInfo"
$spListItem = $spList.GetItemById(99)
$spListItem.Delete()

Removing many items in a batch

Better approach for removing many items from a list is to use the ProcessBatchData method provided by the SPWeb class. First, you need a collection of the list items you want to remove, and then you can use the Get-SPListItem function to retrieve a list of items that match a given criteria. Then build a batch string with resultant items and call the ProcessBatchData method.

function Get-SPList($webUrl, $lstUrl)
{
	$webObj = Get-SPWeb -identity $webUrl
	$lstObj = $webObj.GetList($lstUrl);
	return $lstObj
}
$spList = Get-SPList -webUrl "http://mysitecollecton/MySite" -lstUrl "http://mysitecollecton/MySite/Lists/EmpInfo"
$spQuery = new-object Microsoft.SharePoint.SPQuery
 $camlQuery =
 "<Where>
   <Eq>
    <FieldRef Name='Department' />
      <Value Type='Text'>HR</Value>
   </Eq>
 </Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 100
 $spListItemCollection = $spList.GetItems($spQuery)

  # Create batch remove CAML query
  $batchRemove = '<?xml version="1.0" encoding="UTF-8"?><Batch>';

  # The command is used for each list item retrieved
  $command = '<Method><SetList Scope="Request">' +
  $spList.ID +'</SetList><SetVar Name="ID">{0}</SetVar>' +
  '<SetVar Name="Cmd">Delete</SetVar></Method>';

  foreach ($item in $spListItemCollection)
  {
    
     $batchRemove += $command -f $item.Id;
  }
  $batchRemove += "</Batch>";

  # Remove the list items using the batch command
  $spList.ParentWeb.ProcessBatchData($batchRemove) | Out-Null

Copy items of a list

Copy items from one list in SharePoint 2010 to another list can be done by AddItem method provided by the SPListItem class. We have to read the field values from an existing list item and create a new list item in another list based on these values.
Note:This requires that both lists contain the same fields.
The copying is achieved with the code that we have learned so far.

The following is the generic function that copy’s items from one list to another. I have a created separate post for this
http://adicodes.com/copy-or-replace-list-items-in-powershell/

Conclusion

With this, we have covered the list utility methods(adding, updating, deleting and copying) in powershell that are commonly required.

You can leave your comments, if you require any generic methods or code. Happy coding :)

April 13, 2012 · Adi · 11 Comments
Tags: , , , , , , , ,  · Posted in: CAML, Packaging and Deployment, Powershell, Sharepoint 2010

11 Responses

  1. Sergio Giusti - April 23, 2012

    Hello

    Ive been reading your blog site looking for a solution to my problem but I haven’t found one, however based on what ive read im quite sure you may be able to help.

    I am trying to copy a list from one site to multiple other sites, i need to do this in powershell on an hourly basis, i was intending to write a powershell script to do the copy and set it up as a scheduled task to run each hour. The problem i have is that although i have done the above i am getting a problem which is that instead of changes to rows being updated in the copied lists, new rows are being added.

    my master table looks something like this

    Customer Code | Customer Name | Data 1 | Data 2
    ABCD Alphabet abc

    I can use powershell to export this list to a cmp file and then use powershell to import this list into the other sites, what im trying to achieve is, that if the master list data is amended, when the export/imports happen the changes are added to the imported lists. At present what is happening is that if my master list were to change from the above to the following

    Customer Code | Customer Name | Data 1 | Data 2
    ABCD Alphabet abc def

    my imported lists would all look like this

    Customer Code | Customer Name | Data 1 | Data 2
    ABCD Alphabet abc
    ABCD Alphabet abc def

    i.e. instead of amending the row already there,

  2. Adi - April 24, 2012

    Hi Sergio Giusti,
    I understood your requirement and I think its a valid scenario for many. I have created a new post with generic code based on your requirement
    copy-or-replace-list-items-in-powershell
    Let me know if the new post resolves your requirement.
    Happy coding :)
    Regards
    Adi

  3. Sabs - September 4, 2012

    Hi Adi,

    Thanks for the excellent post! I am not an expert with PS. But I have a requirement to copy the values of a list column to another column in the same list. The reason I want to use PS is because the list contains over 20,000 items. Is there an easy solution?

    Many thanks – Sabs

  4. Adi - September 4, 2012

    Hi Banarjee,
    Did you check my post http://adicodes.com/copy-or-replace-list-items-in-powershell/?
    You can use post reference and do the following changes
    >Make the source list and destination list as same
    >No need to use else part where new item gets created(its always update for your case)
    >In the if condition, remove the foreach loop of fields and you can hardcode on your column names

    Let me know if you want further help.

    Regards,
    Adi

  5. Martin - December 2, 2012

    Hi! I Dont really understand this
    GetItems method can be used to get all the results are subset of results using CAML query and then update the result set.
    Following snippet gets employee information from ‘EmpInfo’ list whose department is ‘HR’ and updates their Description field value

    Can maybe somehelp me ?

  6. Adi - December 2, 2012

    Hi Martin,
    >>If we are using $spList.GetItems(), that means it gets all items from the list object; here the list object is ‘EmpInfo’
    in other words it is like “select * from table”
    >>If we are using $spList.GetItems($spQuery), that means it gets items from the list object which satisfies the condition specified in the $spQuery object
    in other words it is like “select * from table where id = xxx”

    Hope you got the point

    Regards,
    Adi

  7. Chandra - October 26, 2013

    I am getting the error message below.

    The term ‘Get-SPList’ is not recognized as the name of a cmdlet

    I did include pssnapin Microsoft.SharePoint.PowerShell

    Is Get-SPList not available in SP2010?

  8. Adi - October 30, 2013

    Hi Chandra,

    It is my bad, I have missed out that method. I have realized getting list object directly with list url without web url require additional logic to get weburl from list url. It is more comfortable to have two parameters. I have added function in the post which should be easy to understand.

    Hope the issue you faced is resolved now. Happy coding :)

    Regards,
    Adi

  9. sai vara prasad - February 18, 2016

    private static void InsertIntoList(DataTable listTable)
    {
    string siteUrl = “url”;
    //ClientContext clientContext = new ClientContext(siteUrl);
    string listname = “PPCustomList”;//”Emp-Report”;// “MyPPList”;

    using (ClientContext clientContext = new ClientContext(siteUrl))
    {
    clientContext.Credentials = new NetworkCredential(“userid”, “password”, “domain”);
    List list = clientContext.Web.Lists.GetByTitle(listname);
    clientContext.Load(list);
    clientContext.ExecuteQuery();
    CamlQuery query = new CamlQuery();
    query.ViewXml = @”

    “;
    ListItemCollection items1 = list.GetItems(query);
    clientContext.Load(items1, items => items.Include(item => item[“Eastings”], item => item.ContentType, item => item[“DocIcon”], item => item[“Northings”], item => item.ContentType, item => item[“DocIcon”]));
    clientContext.ExecuteQuery();

    clientContext.Load(list);
    clientContext.Load(list.GetItems(query));
    clientContext.ExecuteQuery();
    Console.WriteLine(“Success”);

    // Create new DataTable and DataSource objects.
    DataTable table = new DataTable();
    // Declare DataColumn and DataRow variables.
    DataColumn column;
    DataRow row;

    // Create new DataColumn, set DataType, ColumnName and add to DataTable.
    column = new DataColumn();
    // column.DataType = System.Type.GetType(“System.String”);
    //column.DataType = System.Type.GetType(“System.Int32”);
    // column.ColumnName = “Title”;
    // column.ColumnName = “Name”;
    table.Columns.Add(column);

    // Create new DataRow objects and add to DataTable.
    foreach (ListItem item in items1)
    {
    for (int i = 0; i < items1.Count; i++)
    {
    row = table.NewRow();
    //row["Title"] = "Title" + i.ToString();
    //row["Title"] = "Title" + i.ToString();
    table.Rows.Add(row);
    item["Eastings"] = items1.GetById(0);
    item["Eastings"] = "123";
    item.Update();
    }

    Console.WriteLine(Convert.ToInt32(item["Eastings"]) + " " + Convert.ToInt32(item["Northings"]));
    }

    Console.ReadKey(false);

    }

    }

  10. sai vara prasad - February 18, 2016

    Hi Adi ,
    In the above code iam trying to retrieve the list data , and it is working fine .but i want to update the perticular list item value .
    so please help me in doing update a few list item columns data.

    Many thanks,
    Sai Vara Prasada Rao.

  11. sai vara prasad - February 18, 2016

    Hi Adi ,
    In the above code iam trying to retrieve the list data , and it is working fine .but i want to update the perticular list item value .
    so please help me in updating a few list item columns data using a Caml Query.

    Many thanks,
    Sai Vara Prasada Rao.

Leave a Reply

What is 13 + 15 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)