Replacing values in a list

Question

This is my first Powershell script ever so bear with me.

I'm trying to replace values in a sharepoint list from a CSV file:

Param(
 [Parameter(Mandatory=$true)][string] $csvFileName = $null, 
 [Parameter(Mandatory=$true)][string] $sharePointWebUrl = $null
)
$isError = $false
cls 
$csvFileName = $csvFileName.Trim()
$sharePointWebUrl = $sharePointWebUrl.Trim()
if ([string]::IsNullOrEmpty($csvFileName))
{
 Throw "Please enter parameter values for CSVFileName"
}
if ([string]::IsNullOrEmpty($sharePointWebUrl))
{
 Throw "Please enter parameter values for sharePointWebUrl"
}
Add-PSSnapin Microsoft.Sharepoint.Powershell
try
{
 $csv = Import-Csv $csvFileName
 $subWeb = get-spweb -identity "$sharePointWebUrl" 
 $mylistitems = $subWeb.Lists["MyList"].Items | sort Title
 $csvCollection = New-Object System.Collections.ArrayList
 $csv | ForEach-Object {
 $temp = "" | select "Topics", "Column1"
 $temp.Topics = $_.Topic
 $temp.Column1 = $_.NewTopic
 $csvCollection.Add($temp) | Out-Null
 }
 try
 {
 foreach ($li in $mylistitems) {
 $loopcount=0
 :loop foreach ($si in $csvCollection) 
 {
 $loopcount =$loopcount + 1
 if ($li["Topics"] -eq $si.Topics)
 {
 $li["Topics"] = $si.Column1
 $isFound =$true
 $li.Update()
 break loop
 }
 }
 } 
 }
 catch
 {
 $isError = $true
 $time = Get-Date
 $ErrorMessage = $_.Exception.Message
 "Error Occurred ($time) with error message: $ErrorMessage"| out-file ".\error.log" -append 
 }
}
catch
{
 $isError = $true
 $time = Get-Date
 $ErrorMessage = $_.Exception.Message
 "Error Occurred ($time) with error message: $ErrorMessage"| out-file ".\error.log" -append 
}
finally
{
 if ($web) {
 $web.dispose();
 }
 if ($isError) {
 write-host "script completed with error(s), please look into error.log file" -ForegroundColor Red
 } else {
 write-host "script completed successfully without error(s)" -ForegroundColor Green
 }
}
Remove-PsSnapin Microsoft.SharePoint.PowerShell

Basically I am looping through a sharepointlist with a nested loop for the CSV file and comparing values. Is this the most efficient/correct way to do this?

Here are some sample lines from the CSV:

Topic,NewTopic,Comments
Building,Appliance,
Your workplace,My Workplace

• Dangph: Please show some sample lines from the CSV file. Put fake values in it if you like.
• bier hier: @Dangph added some lines of the csv

Answer 1

I'd replace the ArrayList with a hashtable for faster lookup and less code. Replace:

$csv = Import-Csv $csvFileName
$subWeb = get-spweb -identity "$sharePointWebUrl" 
$mylistitems = $subWeb.Lists["MyList"].Items | sort Title
$csvCollection = New-Object System.Collections.ArrayList
$csv | ForEach-Object {
 $temp = "" | select "Topics", "Column1"
 $temp.Topics = $_.Topic
 $temp.Column1 = $_.NewTopic
 $csvCollection.Add($temp) | Out-Null
}
try
{
 foreach ($li in $mylistitems) {
 $loopcount=0
 :loop foreach ($si in $csvCollection) 
 {
 $loopcount =$loopcount + 1
 if ($li["Topics"] -eq $si.Topics)
 {
 $li["Topics"] = $si.Column1
 $isFound =$true
 $li.Update()
 break loop
 }
 }
 } 
}

With:

#Create hashtable for topics to update
$TopicUpdates = @{}
Import-Csv $csvFileName | ForEach-Object {
 #Assuming you only have one line per Topic (old value) as hashtable keys needs to be unique 
 $TopicUpdates[$_.Topic] = $_.NewTopic
}
$subWeb = get-spweb -identity "$sharePointWebUrl" 
$mylistitems = $subWeb.Lists["MyList"].Items | Sort-Object Title
try
{
 foreach ($li in $mylistitems) {
 if($TopicUpdates.ContainsKey($li["Topics"]) {
 #Found Topic in hashtable - Updating
 $li["Topics"] = $TopicUpdates[($li["Topics"])]
 #Not sure what this variable is for
 $isFound = $true
 $li.Update()
 }
 } 
}

You should also cleanup your try/catch-design. There's no need for the inner try/catch as it does exactly the same as the parent try/catch.

• TheMadTechnician: You can deal with potential duplicate topics if you don't mind slowing down the script a little bit. Import-Csv $csvFileName | Group Topic | %{ $TopicUpdates[$_.Name] = $_.Group -join ',' }

Answer 2

SharePoint lists can be queried for list items.

With that in mind, the following line can be improved:

$mylistitems = $subWeb.Lists["MyList"].Items | sort Title

That line asks SharePoint for all of the list items, and then your PowerShell host sorts the items by Title only after they're returned.

To get the items already sorted by Title, we use the SPQuery object and specify a valid CAML query.

$query = new-object Microsoft.SharePoint.SPQuery
$query.Query = '<OrderBy><FieldRef Name="Title"/></OrderBy>'
$mylistitems = $subWeb.Lists["MyList"].getItems($query)

But hey, not only can we sort it, we can filter it...

That means you shouldn't have to loop through every item to check whether a certain field has a certain value. Just tell SharePoint to only give you items where the desired field explicitly equals the desired value!

$csv | ForEach-Object {
 $oldTopic = $_.Topic
 $newTopic = $_.NewTopic
 $query = new-object Microsoft.SharePoint.SPQuery
 $query.Query = '<Where><Eq><FieldRef Name="Topics"/><Value Type="Text">'+$oldTopic+'</Value></Eq></Where>'
 $mylistitems = $subWeb.Lists["MyList"].getItems($query)
 $myListItems | ForEach-Object { 
 $_["Topics"] = $newTopic # update the field
 $_.Update() # commit the update
 }
}

Note that to improve performance on the SharePoint side, you can go into the list settings and adjust the "indexed columns." Setting the Topics column to be indexed will improve the performance of queries that sort or filter by that column.