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 В· 
12 Comments
Tags: Client Object Model in SharePoint 2010, PowerShell 2.0, SharePoint 2010 В· Posted in: Packaging and Deployment, Powershell, Sharepoint 2010
12 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): “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?
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
Leave a Reply