In Focus

Copy List Items From One SharePoint List To Another Using CSOM PowerShell

In this article, you will learn how we can copy list items from one SharePoint list to another using CSOM PowerShell.

Introduction

In this article, you will learn how to copy list items from one list to another list using CSOM Powershell on SharePoint. This is mainly focused on using PowerShell scripts for any SharePoint 2013 / SharePoint online sites.

Steps Involved

The following prerequisites need to be executed before going for any operations using CSOM PowerShell on SharePoint sites.

  1. Add the references using the Add-Type command with the necessary reference paths. The necessary references are Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll.
    1. Add-Type –Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"  
    2. Add-Type –Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"  
  2. Initialize client context object with the site URL.
    1. $siteURL = ""  
    2. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
  3. If you are trying to access SharePoint Online site then you need to setup the site credentials with credentials parameter and load it to the client context. 
    1. #Not required for on premise site - Start  
    2. $userId = ""  
    3. $pwd = Read-Host -Prompt "Enter password" -AsSecureString  
    4. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)  
    5. $ctx.credentials = $creds  
    6. #Not required for on premise site - End  
  4. If you are trying to access the SharePoint on premise site then the credentials parameter is not required to be set to the context but you need to run the code on the respective SharePoint Server or you need to pass the network credentials and set the context.
    1. #Credentials for on premise site - Start    
    2. $pwd = Read-Host -Prompt "Enter password" -AsSecureString    
    3. $creds = New-Object System.Net.NetworkCredential("domain\userid", $pwd)    
    4. $ctx.Credentials = $creds    
    5. #Credentials for on premise site - End  

Copy Operation

We will see how we can copy the list items from one list to another list. Here, the list should have identical SharePoint list fields. The values will be copied only if the corresponding fields are present on both source and destination lists.
  • Using the context, access the web.

  • From the web, get the source list and destination list using the list names.

  • From the source list, get the items using the query object. The query object should be initiated to access the necessary source list items. In this case, I have used All Items query to get all the items.

  • Load and execute the query.
    1. $list1 = $ctx.Web.Lists.GetByTitle("List1")  
    2. $list2 = $ctx.Web.Lists.GetByTitle("List2")  
    3. $list1Items = $list1.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())  
    4. $fields = $list1.Fields  
    5. $ctx.Load($list1Items)  
    6. $ctx.Load($list1)  
    7. $ctx.Load($list2)  
    8. $ctx.Load($fields)  
    9. $ctx.ExecuteQuery()  
After executing the above steps, we can access the source list, destination list, source list items and source list columns (fields). Now we will see how we can copy items. For each source list item available, execute the below operations.
  • For destination list, create list item creation information object and add the object to the destination list.

  • Access all the fields using for each loop from the source list.

  • For every field, check whether field is not read only field. If it is read only field, then we will not be able to write it to the destination list.

  • Also the fields should not be hidden, attachment or content type, since the write operations will not be possible on these fields.

  • For other custom fields, execute the copy operations and update the item.
    1. foreach($item in $list1Items){  
    2.     Write-Host $item.ID  
    3.     $listItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
    4.     $list2Item = $list2.AddItem($listItemInfo)  
    5.       
    6.     foreach($field in $fields){  
    7.         #Write-Host $field.InternalName " - " $field.ReadOnlyField   
    8.         if((-Not ($field.ReadOnlyField)) -and (-Not ($field.Hidden)) -and ($field.InternalName -ne  "Attachments") -and ($field.InternalName -ne  "ContentType"))  
    9.         {  
    10.             Write-Host $field.InternalName " - " $item[$field.InternalName]  
    11.             $list2Item[$field.InternalName] = $item[$field.InternalName]  
    12.             $list2Item.update()  
    13.         }  
    14.     }  
    15. }  
    16. $ctx.ExecuteQuery()  
Summary

Thus, you have learned how to copy the items from one list to another using CSOM PowerShell on SharePoint 2013 / SharePoint online sites.