Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am trying to copy a list from one site to multiple other sites, i have tried to do this in powershell on an hourly basis using scheduled tasks, i was intending to write a powershell script to do the copy and set it up as a scheduled task to run each hour. The problem i have is that although i have done the above i am getting a problem which is that instead of changes to rows being updated in the copied lists, new rows are being added.

my master table looks something like this

Customer Code | Customer Name | Data 1 | Data 2
ABCD               Alphabet       abc      

I can use powershell to export this list to a cmp file and then use powershell to import this list into the other sites, what im trying to achieve is, that if the master list data is amended, when the export/imports happen the changes are added to the imported lists. At present what is happening is that if my master list were to change from the above to the following

Customer Code | Customer Name | Data 1 | Data 2
ABCD              Alphabet       abc      def 

my imported lists would all look like this

Customer Code | Customer Name | Data 1 | Data 2
ABCD             Alphabet        abc  
ABCD             Alphabet        abc      def

i.e. instead of amending the row already there, a 2nd row of data is added. As my table is a customer table and each row in the table should be a unique customer, this poses an issue for me as the local lookups to the imported lists dont update with the new info.

Here is the Powershell i am using to export, import to one site and set security on the imported list as well as removing it from the quick launch (i am a beginner at powershell and have copied and amended most of the script below to match my requirements)

Add-PsSnapin Microsoft.SharePoint.Powershell –ErrorAction SilentlyContinue

#This is the source web that is hosting the lists to move
$sourceWebUrl = "http://intranet/customerdb"

#Location to store the export file
$path = "\\MY SHAREPOINT SERVER HOSTNAME\SHARE NAME\"

#comma delimited list of List Names to copy
$lists = @("Customers")

#Loop through the lists, export the list from the source, and import the list into the destination
foreach($list in $lists)
{
"Exporting " + $sourceWebUrl + "/lists/" + $list
export-spweb $sourceWebUrl -ItemUrl ("lists/" + $list) -IncludeUserSecurity -IncludeVersions All -path ($path + $list + ".cmp") -Force
"Exporting complete."

#Import Exported List into other sites

$spBIWeb = Get-SPWeb http://intranet/BI
"Importing " + $spBIWeb + "/lists/" + $list
import-spweb $spBIWeb -IncludeUserSecurity -path ($path + $list + ".cmp") -UpdateVersions Overwrite -Force
$spBIList = $spBIWeb.GetList("http://intranet/BI/Lists/Customers")
$spBIList.OnQuickLaunch = $false
$spBIList.Update()
"Importing Complete"

#Set Security Group on Imported list with Full Control

"Setting Security"
$web = Get-SPWeb http://intranet/BI
$account = $web.EnsureUser("DOMAIN NAME\Business Intelligence Team")
$role = $web.RoleDefinitions["Full Control"]
$list = $web.Lists["Customers"]
$list.BreakRoleInheritance($true)
$assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
$assignment.RoleDefinitionBindings.Add($role)
$list.RoleAssignments.Add($assignment)
$list.Update()
"Security Set"

I understand that I may be able to add some CAML code into the powershell to check where the rows are the same and then update the row but I have no idea how to write CAML and my powershell is copied and amended in the main and i dont know how to set up this powershell script to make the updates if the row already exists.

Can anyone help me with what changes i need to make to the script to accomplish my goal.

share|improve this question

1 Answer 1

Since your goal is to overwrite copied lists with fresh data my guess is that you only want to display main list data on other sites. There is much more simpler solution for this:

Share a List View Between Sites in SharePoint 2010

Exporting/importing lists on time interval with PS looks like overkill to me. If you really want to copy/update your data I would suggest you first delete old destination list, then recreate it and populate it with new data. However you can experience difficulties on a long run with this approach and with large amounts of data. Also you would need to take care of some leftovers eg. deleted lists in recycle bin to make things smooth.

Edited:

If destination lists are used as source for lookup column then maybe better solution is to use Lookup Field with Picker 2010. It is enhanced lookup column that supports cross site picking.

share|improve this answer
    
the copied list is to be used as a lookup column, those lookups are used in the document info panel, datasheet views etc. If i delete the list first, the lookups already set get removed so this is not an option. neither is deleting the existing row before the new row is added. –  Sergio Giusti Apr 24 '12 at 12:13
    
@SergioGiusti then deleting is not an option. Check this as alternative (supports cross site picking) ilovesharepoint.codeplex.com/releases/view/44989. I really thing PS and scheduled tasks are not a good solution –  Vedran Rasol Apr 24 '12 at 12:16
    
thanks but i have already got the ilove sharepoint cross site lookup installed, however that free solution and all the paid solutions (sharepoint boost, bamboo etc) do not function with the document information panel, i.e. if you use a cross site lookup column on a document library and open a document from within that library (thats using the content type with that cross site lookup column attached) then you get an error. –  Sergio Giusti Apr 24 '12 at 12:52
    
You can also not amend the items in the list in datasheet view as the cross site lookup column is read only. These issues are resolved by using a local lookup which is why im copying the master list locally to all department sites that need the customer table, however that solution only works if i can find a way to update the copied rows and not add extra ones as the lookups to the data dont pick up the amended info if its in a new row (i.e. has a different list ID). –  Sergio Giusti Apr 24 '12 at 12:53
1  
Full Code Resolution to this issue can be found at: adicodes.com/copy-or-replace-list-items-in-powershell/… –  Sergio Giusti Apr 25 '12 at 8:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.