Copy or Replace list items in PowerShell

Copy or Replace list items in PowerShell
2 votes, 5.00 avg. rating (97% score)

Introduction

Based on the request from one of the audience of adicodes, I am providing one post which copies items from one list to another.
If the destination list already has the item of the source list, the following code will update the item. If the item does not exist it will create a new item

Copying/replacing items from source list to destination list

Following code copies/replace items from the list “SourceList” to the list “DestinationList”. Both lists are having same schema.
The key reference column for both the list is ‘Title’ column.






It checks in the ‘DestinationList’ if the ‘Title’ value is already exists or not. If exists item will updated if not item will be created

Remove-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
	try
	{
	$srcListSiteUrl = "http://adisgroup/myteamsite"
	$SourceListName = "SourceList"

	$dstListSiteUrl = "http://adisgroup/myteamsite"
	$DestinationListName = "DestinationList"

	$keyColumnInternalName = "Title"

	$sourceListWeb = Get-SPWeb -identity $srcListSiteUrl
	$sourceListUrl = $sourceListWeb.ServerRelativeUrl + "/lists/" + $SourceListName;

	$dstListWeb = Get-SPWeb -identity $dstListSiteUrl
	$destinationListUrl = $dstListWeb.ServerRelativeUrl + "/lists/" + $DestinationListName;

	$SourceList = $sourceListWeb.GetList($sourceListUrl);
	$DestinationList = $dstListWeb.GetList($destinationListUrl);

	$sourceSPListItemCollection = $SourceList.GetItems();
	foreach($srcListItem in $sourceSPListItemCollection)
	{  

		#CAML query of the common column (generally the title column or any unique column)
		$keyValue = $srcListItem[$keyColumnInternalName]
		$camlQuery =
		 "<Where>
		   <Eq>
			<FieldRef Name=" + $keyColumnInternalName + " />
			  <Value Type='Text'>" + $keyValue + "</Value>
		   </Eq>
		 </Where>"
		$spQuery = new-object Microsoft.SharePoint.SPQuery
		$spQuery.Query = $camlQuery
		$spQuery.RowLimit = 1
		#check if the item is already present in destination list
		$destItemCollection = $DestinationList.GetItems($spQuery)
		if($destItemCollection.Count -gt 0)
		{
			write-host "list item already exists, updating "
			foreach($dstListItem in $destItemCollection)
			{
				foreach($spField in $dstListItem.Fields)
				{
				  if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
				  {
					 $dstListItem[$spField.InternalName] = $srcListItem[$spField.InternalName];
				  }
				} 

			  # Handle Attachments
			  foreach($leafName in $srcListItem.Attachments)
			  {
			    $spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
			    $dstListItem.Attachments.Add($leafName, $spFile.OpenBinary());
			  }
    		  $dstListItem.Update()

			}
		}
		else
		{
			write-host "adding new item"
			$newSPListItem = $DestinationList.AddItem(); 

			foreach($spField in $srcListItem.Fields)
			{
			  if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
			  {
				  $newSPListItem[$spField.InternalName] = $srcListItem[$spField.InternalName];
			  }
			}
			 # Handle Attachments
			foreach($leafName in $srcListItem.Attachments)
			  {
			    $spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
			    $newSPListItem.Attachments.Add($leafName, $spFile.OpenBinary());
			  }
			$newSPListItem.Update()
		}

	}

	}
	catch
	{
		write-host $_.exception

	}
	finally
	{
		if($sourceListWeb -ne $null){$sourceListWeb.Dispose()}
		if($dstListWeb -ne $null){$dstListWeb.Dispose()}

	}

Usage

In the above code, there are five custom variables
$srcListSiteUrl : Site url where the source list is present
$SourceListName : Name of the source list
$dstListSiteUrl : Site url where the destination list is present (In the above code both lists are in the same site)
$DestinationListName : Name of the destination list
$keyColumnInternalName : Column name with which we have to check whether to insert or update.
Note, in the above example $keyColumnInternalName value is ‘Title’. It is Text type. So, the CAML query variable $camlQuery is constructed in that way.
If you have different data type column which you want to use as a reference column, you have to change the variable accordingly as the column type.

Conclusion

Hope you understand the usage of the above code for copying and replacing items from source list to destination list.
Let me know if you have any comments :)

April 24, 2012 В· Adi В· 17 Comments
Tags: , ,  В· Posted in: Packaging and Deployment, Powershell, Sharepoint 2010

17 Responses

  1. Sergio Giusti - April 24, 2012

    Thank you for this.
    Ive ran the script using powershell on the sharepoint server and i get the following error:

    System.Management.Automation.MethodInvocationException: Exception calling “GetList” with “1″ argument(s): “0×8
    0070002″ —> System.IO.FileNotFoundException: 0×80070002
    at Microsoft.SharePoint.Library.SPRequestInternalClass.GetMetadataForUrl(String bstrUrl, Int32 METADATAFLAGS, Guid& p
    gListId, Int32& plItemId, Int32& plType, Object& pvarFileOrFolder)
    at Microsoft.SharePoint.Library.SPRequest.GetMetadataForUrl(String bstrUrl, Int32 METADATAFLAGS, Guid& pgListId, Int3
    2& plItemId, Int32& plType, Object& pvarFileOrFolder)
    at Microsoft.SharePoint.SPWeb.GetList(String strUrl)
    at GetList(Object , Object[] )
    at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformat
    ion methodInformation, Object[] originalArguments)
    — End of inner exception stack trace —
    at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformat
    ion methodInformation, Object[] originalArguments)
    at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArr
    ay, Boolean callStatic, Object valueToSet)
    at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
    at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
    at System.Management.Automation.AssignmentStatementNode.Execute(Array input, Pipe outputPipe, ExecutionContext contex
    t)
    at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputP
    ipe, ArrayList& resultList, ExecutionContext context)

    Have i done something wrong?

  2. Sergio Giusti - April 24, 2012

    please ignore my last comment, it works fine. thank you very much for the excellent solution.

  3. Adi - April 24, 2012

    You are welcome and it will be always good to hear that my posts are helpful. Happy coding :)

  4. Bob Eldredge - June 13, 2012

    Adi – excellent work on this.

  5. Adi - June 13, 2012

    Thanks Bob

  6. Adam Poole - June 18, 2012

    Hi,
    Thanks for the code it is almost what I want, I just need an extra filter in the CAML query. I am trying to copy on events using a specific resource to the new calendar. I have this query working on an xml export but I am struggling to add it to your code. Any ideas would be appreciated. Below is the query.

    ” + $keyValue + ”

    +

    Admin Boardroom

  7. Adi - June 18, 2012

    Hi Adam,
    I did not get the query you are using, as the you cannot write xml nodes in the reply message box. You have to replace

    '<' with "&lt;" and '>' with '&gt;' 

    If you are looking for adding extra filter in the CAML query try this one.
    For example you have add one more condition with another column ‘col2′. Add two more variables after line 27
    $keyColumnInternalName2 = “col2″
    $keyValue2 = $srcListItem[$keyColumnInternalName2]
    And the new camlQuery variable will be

    $camlQuery =
    "<Where>
    <And>
    <Eq>
    <FieldRef Name=" + $keyColumnInternalName + " />
    <Value Type='Text'>" + $keyValue + "</Value>
    </Eq>
    <Eq>
    <FieldRef Name=" + $keyColumnInternalName2 + " />
    <Value Type='Text'>" + $keyValue2 + "</Value>
    </Eq>
    </And>
    </Where>"

    Make sure to provide proper datatype for ‘Type’ attribute.

    Let me know if you have any other queries. Happy Coding.

    Regards,
    Adi

  8. Adam Poole - June 18, 2012

    Hi Adi,

    Thanks for the reply and I was not writing the “And” part correctly.

    However it is not quite working, the query runs and copies all entries across but I only want to copy entries that match $keyValue2.

    On top of this when the script is run again entries where $keyvalue2 matches are updated but where $keyValue2 does not match the entries are duplicated.

    Thanks for your help.

    Adam

  9. Adi - June 19, 2012

    Hi Adam,
    Make sure to provide the caml query condition properly.
    Check the item collection count is coming correctly or not with the caml query. You might be doing wrong somewhere in the condition checking.
    Let me know if you are stuck

    Warm Regards,
    Adi

  10. Adam Poole - June 19, 2012

    Thanks for the help and pointers. I got it sortedin the end. I needed to different queries, one to limit the items pulled from the source list and then yours to perform the copy / replace. Something like this:

    Remove-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    try
    {
    $srcListSiteUrl = “http://adisgroup/myteamsite”
    $SourceListName = “Meeting Rooms”

    $dstListSiteUrl = “http://adisgroup/myteamsite”
    $DestinationListName = “Admin Boardroom”

    $keyColumnInternalName = “Title”
    $filterColumnInternalName = “Category”
    $filterColumnInternalValue = “Admin Boardroom”

    $sourceListWeb = Get-SPWeb -identity $srcListSiteUrl
    $sourceListUrl = $sourceListWeb.ServerRelativeUrl + “/lists/” + $SourceListName;

    $dstListWeb = Get-SPWeb -identity $dstListSiteUrl
    $destinationListUrl = $dstListWeb.ServerRelativeUrl + “/lists/” + $DestinationListName;

    $SourceList = $sourceListWeb.GetList($sourceListUrl);
    $DestinationList = $dstListWeb.GetList($destinationListUrl);

    $filterQuery =
    “<Where>
    <Eq>
    <FieldRef Name=” + $filterColumnInternalName + ” />
    <Value Type=’Choice’>” + $filterColumnInternalValue + “</Value>
    </Eq>
    </Where>”
    $CategoryQuery = new-object Microsoft.SharePoint.SPQuery
    $CategoryQuery.Query = $filterQuery

    $sourceSPListItemCollection = $SourceList.GetItems($CategoryQuery);
    foreach($srcListItem in $sourceSPListItemCollection)
    {

    #CAML query of the common column (generally the title column or any unique column)
    $keyValue = $srcListItem[$keyColumnInternalName]
    $camlQuery =
    “<Where>
    <Eq>
    <FieldRef Name=” + $keyColumnInternalName + ” />
    <Value Type=’Text’>” + $keyValue + “</Value>
    </Eq>
    </Where>”
    $spQuery = new-object Microsoft.SharePoint.SPQuery
    $spQuery.Query = $camlQuery
    $spQuery.RowLimit = 1

    Thanks again for your help and the post it was a great help.

  11. conner - July 3, 2012

    hi,
    im trying to write a powershell script that will turn a list of url’s from http://mysite.com/index.php/593478309909ssaoi
    TO
    mysite.com
    i have got so far by using the foreach-object and replace command however when i look at my lists i now have

    mysite.com
    thisis.asite.co.uk
    welcometomysite.de
    definately.asite.com

    i need to get rid of the first chunk of text before the dot
    so that it looks like this

    mysite.com
    asite.co.uk
    welcometomysite.de
    asite.com

    i have tried countless times and cannot do it because if i try to delete before the first dot then sites that end in .com they get deleted also so i end up with

    com
    asite.co.uk
    de
    asite.com

    if you could give me a script that would delete just the needed bits then i would be very greatful

  12. Adi - July 4, 2012

    Hi,
    Assuming that you are looking to get rid of the first chunk when there are more than one dot
    Check this script. Let me know if you are stuck or if any changes if you want
    $str1 = “mysite.com”
    $str2 = “thisis.asite.co.uk”
    if($str1.IndexOf(“.”) -ne $str1.LastIndexOf(“.”))
    {
    $str1 = $str1.Substring($str1.IndexOf(“.”)+1)
    }
    #the output of $str1 will be ‘mysite.com’

    if ($str2.IndexOf(“.”) -ne $str2.LastIndexOf(“.”))
    {
    $str2 = $str2.Substring($str2.IndexOf(“.”) + 1)
    }
    #the output of $str2 will be ‘asite.co.uk’

    May be you can make it as function like the following

    function RemoveString([string]$strValue)
    {
    write-host “old value :” $strValue
    if($strValue.IndexOf(“.”) -ne $strValue.LastIndexOf(“.”))
    {
    $strValue = $strValue.Substring($strValue.IndexOf(“.”)+1)
    }
    write-host “new value:” $strValue
    return $strValue
    }
    $str1 = RemoveString $str1

  13. Daniel Christian - September 11, 2013

    Thank you for taking the time to create this blog.
    In my case I want to copy lists items from one lists to another on a anonymous access website. Do I need to force any authentication on this powershell? If yes then how?
    I plan on running the script directly on the server.
    Thanks in advance.

  14. Adi - September 13, 2013

    Hello Daniel,

    Does anonymous user have permission to create new list item in the website where you are copying items?(you can check by creating list item) If yes, then no special permissions required.
    Powershell execution will be successful.
    But if anonymous user does not have permission to create list items, then open the powershell window with the user account having permission to create items in the list.

    Hope you got my point.

    Regards,
    Adi

  15. Wout - October 14, 2013

    Thanks Adi!

    This is just what I’m looking for. Unfortunately I’m getting the same message as Sergio GiustГЇ (response of April 24, 2012)

    What is it that I do wrong? Can someone help me out?

    Thanks in advance,
    Wout

  16. Adi - October 14, 2013

    Hello Wout,

    One possible reason to get this error is, you are trying to access the list which you do not have permission.
    May be you try executing the code with elevated permissions or login as another user having permission. Have a quick check from the UI if you have access to the list and having permissions to create/edit/delete items in the list

    Let me know if you want further help.
    Regards,
    Adi

  17. Rohit - November 22, 2013

    Hi,
    I have to copy list item form sharepoint 2007 site to sharepoint 2013, how can i handel Metadata fields
    please suggest.

Leave a Reply

What is 6 + 8 ?
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) :-)