Copy or Replace list items in PowerShell
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 В· 
22 Comments
Tags: Client Object Model in SharePoint 2010, PowerShell 2.0, SharePoint 2010 В· Posted in: Packaging and Deployment, Powershell, Sharepoint 2010
22 Responses
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): “0x8
0070002” —> System.IO.FileNotFoundException: 0x80070002
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?
please ignore my last comment, it works fine. thank you very much for the excellent solution.
You are welcome and it will be always good to hear that my posts are helpful. Happy coding
Adi – excellent work on this.
Thanks Bob
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
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
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
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
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
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.
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
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
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.
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
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
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
Hi,
I have to copy list item form sharepoint 2007 site to sharepoint 2013, how can i handel Metadata fields
please suggest.
I am facing same that was posted in First Blog.
Please can any body help me.
I need to copy all the version history as well for each list items. could you please guide on how to do that? thanks!
Does it work between External List and native sharepoint list?
Dear Adi
Really great work but i found some problem when i used
as i am copying items from one list to another the destination list is empty and there is no item the query
says item already exits and start updating means it is showing unexpected behavior any specific reason.
Thanks in advance
Hi Adi,
My requirement is to update list items in another list by date created. We want to move all list items to archive list that is greater than 1 year.
Could you suggest me how to achieve this?
Thank you!!
Leave a Reply