Import a list using powershell that overwrites existing data

Question

Hi

I am trying to copy a list from one site to multiple other sites, i need to do this in powershell on an hourly basis, 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 if the master list data is amended that 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.

can anyone help.

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

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"

---

Sergio Giusti

Answer 1

If your using Customer Code as a primary key, in the list options make sure the field is set i.e. to not allow repeated content http://cube4.com.au/?p=413

Secondly you should probally do this as a timer job and not a windows scheduled task.

Thirdly, instead of just adding, you would want to first do a CAML query based on Customer Code, if the count of the query is 1 then you need to update the existing record instead of adding a new record.

---

SharePoint 2010 MCITP & MCPD | Office 365 MCITP My Blog | My Work

Answer 2

thank you for your response but im afriad i dont know how to do a CAML query based on Customer Code to update the items, settings it as Enforce Unique Values also doesnt work because this time instead of giving me two rows when i run the 2nd import, i just get errors while doing the import advising me that the data already exists and cant be written to.  

Can you provide any advice on how to write the CAML query?

---

Sergio Giusti

Answer 3

Hi Sergio,

Correct, you wont be able to do a import the second time.

Your going to have to check that the item exists instead of just blindly adding the item.

theres a guide here on how to use CAML inside powershell: http://blogs.flexnetconsult.co.uk/colinbyrne/2008/02/26/PowerShellSharePointThreeWaysToDeleteAListItemIDCAMLStringMatching.aspx

---

SharePoint 2010 MCITP & MCPD | Office 365 MCITP My Blog | My Work

Answer 4

thanks

unfortunatley i dont know how to write CAML and my powershell scripting isnt the best either, the link you provided only shows how to delete rows from the table before the4 new ones are added which is not of any use to me as the data is used as lookups in other lists, removing the rows removes the lookedup data in other lists which like deleting the list and then importing it kind of negates what im trying to achieve.

thanks for your help anyway.

---

Sergio Giusti

Answer 5

Thank you very much to Dasari Adiseshu for providing me with a full code answer to my query.

her response is here for anyone else who may want it:

http://adicodes.com/copy-or-replace-list-items-in-powershell/#comment-38 

---

Sergio Giusti