SharePoint 2013: PowerShell to copy or update list items across SharePoint sites and farms
Description
A request had come in by a recently established department who wanted items from other department's lists added to their list when a certain choice was selected in a choice column. Since the copy list item action in SharePoint workflow cannot be used across sites or farms, the only possible way was using PowerShell. There are several blogs out there which use the Title column to search existing list items and if a duplicate isn't found then to add a new item, however, not all lists can have the Title column unique hence in this example I have exported the ID column from the target list and added it to the destination list. Now the ID in the destination is searched to avoid duplicates.
This article describes the scripts which can be used to export lists from a 2010 -to-2010, or 2013-to-2013, or 2010-to-2013.
I have also provided three separate PowerShell scripts, one is the export only, second the import and the third does both.
Setting the scene
The Finance department has a list called purchase. The list has a choice column called products. One of the choices of that column is stationary.
When an item is added such that the product selected is stationary, then a copy of that list item needs to be made to the Utility's inventory list. In this example the Utility department has a separate site collection.
List and their columns
Below is a screenshot of the both the utility department's inventory list and the finance departments' purchase list.
Finance
Below is a screenshot of the Finance department's Purchase list i.e. the target list.
https://vw9adw.dm2301.livefilestore.com/y2p6rewuN4NnQpZdv8sYyr8KdSpvxDsXW2RBhlcoSFzeP6EPYOn7DShtuOaw3AVEJSj23EgzNgOWWsGp-VzwtSQ38pRavnlW4kq_nTsebvbcyU/Capture.PNG?psid=1
Below are the choices of the Products column
Computers
Coffee
Stationary
Checks
Utility
Below is a screenshot of the Utility department's Inventory list i.e. the destination list. This list has a single line of text column called ID #. This column stores the ID numbers from the target list. The PowerShell depends on the values of this column before any changes are made.
https://vw9adw.dm2301.livefilestore.com/y2pWolj61zQGES9_1jVMpbWHjzZ5oRru0s0N2a8q39SYX_TBsJdZ1mSFATnP4iRpaRTjzEASuQXsSsKo53bq70gbCWZWDF3tA3Sle90l_wccQQ/Capture2.PNG?psid=1
PowerShell
I have provided three separate PowerShell scripts. There might be instances where a SharePoint administrator might only need the export or the import or all-in-one and hence I have separated them.
Export
This power shell will export all the items from the finance department's site's purchase list whose product column's choice is stationary. We are also going to export the ID column from this target list.
#Load the SharePoint Snapin.
*Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Get the target SPWeb object
$web = Get-SPWeb -identity "http://sp2010.contoso.com/finance/"
#Get the Target List
$list = $web.Lists["Purchase"]
#Array to Hold Result - PSObjects
$ListItemCollection = @()
#Get All List items where Products is Stationary
$list.Items | Where-Object { $_["Products"] -eq "Stationary"} | foreach {
#Load all the properties you want to export into an array
$properties = @{
"Products" = *$_["Products"] *
"Quantity" = $_["Quantity"] * "Vendor" = $_["Vendor"]
"ID #" = $_["ID #"]
"Department" = $_["Department"]
}
#Create an object to hold the properties above
$ExportItem = New-Object PSObject -Property $properties
#Add the object with property to an Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV "\sp2010\C$\Scripts\temp\export.csv" -NoTypeInformation
#Dispose the web Object
$web.Dispose()
Import
This script will loop through the CSV file we created, compare it with the destination list i.e. the inventory list and add a new item if it doesn't exist.
*Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$csvVariable= Import-CSV -path "http://sp2010.contoso.com/finance/"
# Destination SPWeb URL
$WebURL = "http://sp2010.contoso.com/sites/Utility/"
# Destination list name
$listName = "Inventory"
#Get the SPWeb object and save it to a variable
$web = Get-SPWeb -identity $WebURL
#Get the SPList object to retrieve the list
$list = $web.Lists[$listName]
#Get all items in this list and save them to a variable
$items = $list.items
#loop through csv file
foreach($row in $csvVariable)
{
$updated = 0
#loop through SharePoint list
foreach($item in $items)
{
if($item["ID #"] -eq $row."ID #")
{
$updated++
}
}
#add new item if an update wasn't made
if($updated -eq 0)
{
$newItem = $list.items.Add()
$newItem["Product"] = $row."Product"s
$newItem["Quantity"] = $row."Quantity"
$newItem["Vendor"] = $row."Vendor"
$newItem["Department"] = $row."Vendor"
$newItem["ID #"] = $row."ID #"
$newItem["Department"] = $row."Department"
$newItem.Update()
}
}
$web.Dispose() *
All-in-one
This script does both. Once the items from the target list has been saved to a csv file I have set a 10 second delay before the destination list is update. This gives the CSV file enough time to populate.
*Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Get the target SP Web
$webSource = Get-SPWeb -identity "http://sp2010.contoso.com/finance/"
#Get the Source List
$list = $webSource.Lists["Purchase"]
#Array to Hold Result - PSObjects
$ListItemCollection = @()
#Get All List items where Products is Stationary
$list.Items | Where-Object { $_["Products"] -eq "Stationary"} | foreach {
* #Load all the properties you want to export into an array
$properties = @{
"Products" = *$_["Products"] *
"Quantity" = $_["Quantity"] * "Vendor" = $_["Vendor"]
"ID #" = $_["ID #"]
"Department" = $_["Department"]
}
#Create an object to hold the properties above
$ExportItem = New-Object PSObject -Property $properties
#Add the object with property to an Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV "\sp2010\C$\Scripts\temp\export.csv" -NoTypeInformation
#Dispose the web Object
$webSource.Dispose()
#Pause for 10 seconds for the CSV file to populate all the way.
Start-Sleep 10
#Import to destination list
#This section of the PowerShell will loop through the csv file we created, compare it the inventory list and add new item if it didn't exist.
$csvVariable= Import-CSV -path "\sp2010\C$\Scripts\temp\export.csv"
# Destination site collection
$WebURL = "http://sp2010.contoso.com/sites/Utility/"
# Destination list name
$listName = "Inventory"
#Get the SPWeb object and save it to a variable
$webDestination = Get-SPWeb -identity $WebURL
#Get the SPList object to retrieve the list
$list = $webDestination.Lists[$listName]
#Get all items in this list and save them to a variable
$items = $list.items
#loop through csv file
foreach($row in $csvVariable)
{
$updated = 0
#loop through SharePoint list
foreach($item in $items)
{
if($item["ID #"] -eq $row."ID #")
{
$updated++
}
}
#add new item if an update wasn't made
if($updated -eq 0)
{
$newItem = $list.items.Add()
$newItem["Products"] = $row."Products"
$newItem["Quantity"] = $row."Quantity"
$newItem["Vendor"] = $row."Vendor"
$newItem["Department"] = $row."Vendor"
$newItem["ID #"] = $row."ID #"
$newItem["Department"] = $row."Department"
$newItem.Update()
}
}
$webDestination.Dispose() *
Warnings and Limitations
This script is designed to keep the destination list as a copy of the source list. Any removals or additions to the destination list will cause the upload script to duplicate items the next time it runs.
It is only intended to handle lists below the list view threshold. If you need to use this on larger lists you can adapt it using paging.
Conclusion
A previous version of the the three scripts have been saved as a zip file Click Here to download it. These PowerShell scripts can be added to the Task Manager of the servers and setup to repeat as often as you need. You can refer to this TechNet article, 'Task Schedule How To...' to setup your tasks on the server.