$x = @"
This script updates items in a SharePoint list from a csv file
The csv file does not contain the ID of list items, matching is done on 'key' fields.
Processing:
1. read the csv file into an iterator
2. iterate through the SharePoint list
3. for each list item, iterate through the csv file
4. if cvs row match update the ShartePoint list with values from the csv row
Note! This type of matching is inefficient, but quite ok for SharePoint lists as there few items in lists
and SharePoint per se is easily 'saturated', this matching gives SharePoint pauses to recover.
"@
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$UserId = "USERID"
$Password = ConvertTo-SecureString 'PASSWORD' -AsPlainText -Force
$siteURL = 'https://thehub.group.atlascopco.com/metadata/group_codes'
$listItemstname = "RG_PGC_Codes" # SP list
$readItems = 5000 # Items to read from SharePoint list
# csv column names
$csvRG = "Rate Group Code" #key
$csvPGC = "Product Group Code" #key
$csvGAC = "GAC" #Key
$csvUNIT = "Unit" #key
$csvVALIDTO = "ValidTo" #value
# csv file
$delimeter = ";"
$filePath = "C:\rgupdate.csv"
$csvData = Import-Csv $filePath -Delimiter $delimeter # Load the CSV file containing the updates
$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$ctx.credentials = $credentials
try {
$list = $ctx.web.Lists.GetByTitle($listItemstname)
$listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery($readItems))
$ctx.load($listItems)
$ctx.executeQuery()
foreach($li in $listItems) {
if ($li["ValidTo"] -eq $null){ # Filter; only null 'ValidTo' are interesting
foreach ($line in $csvData){
if (($li["Rate_x0020_Group_x0020_Code"].LookupValue -eq $line.$csvRG) -and ($li["Product_x0020_Group_x0020_Code0"] -eq $line.$csvPGC) ){
Write-Host "Match"
$ID = $li["ID"]
write-host $line.$csvUNIT,$line.$csvGAC,$line.$csvPGC,$line.$csvRG,$line.$csvVALIDTO, $ID
Write-Host "ID -" $li["ID"] ", RG -" $li["Rate_x0020_Group_x0020_Code"].LookupValue ", PGC -" $li["Product_x0020_Group_x0020_Code0"] ", GAC -" $li["GAC0"] "Unit -" $li["Unit"].LookupValue " ,validTo -" $li["ValidTo"]
$li["ValidTo"] = $line.$csvVALIDTO
$li.Update()
$ctx.Load($li)
$ctx.ExecuteQuery();
# 'Break' will stop update an item after first match, 'Return' stop processing after first match (for testing purposes)
Break
# Return
} # endif Match
} # end foreach csv row
}
} # end foreach SP item
} catch {
write-host "$($_.Exception.Message)" -foregroundcolor red
}
|
