Copy or Replace list items in PowerShell

5.00 / 5 5
1 / 5
2 / 5
3 / 5
4 / 5
5 / 5

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

Email this page

April 24, 2012 · Adi · 18 Comments
Tags: Client Object Model in SharePoint 2010, PowerShell 2.0, SharePoint 2010 · Posted in: Packaging and Deployment, Powershell, Sharepoint 2010

18 Responses

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?
Sergio Giusti - April 24, 2012

please ignore my last comment, it works fine. thank you very much for the excellent solution.
Adi - April 24, 2012

You are welcome and it will be always good to hear that my posts are helpful. Happy coding
Bob Eldredge - June 13, 2012

Adi – excellent work on this.
Adi - June 13, 2012

Thanks Bob
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
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
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
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
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.
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
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
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.
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
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
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
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.
sivakumar - July 22, 2014

I am facing same that was posted in First Blog.
Please can any body help me.