1

I've got a list where theres one column that needs to be updated on a monthly basis (figures get changed). The list already has a person column so over-writing the whole list each month isn't what I'm after.

I have see some examples on updating a field based on a value. So in this example from this site its possible to update items where a value equals something. Which is good because I had a unique 'key' value in the way of a Customer number which I can match on. So basically what I need to do is export a csv of those 2 columns and run through the list see if it matches and update if it does not. My Powershell skills have been a bit neglected of late so its just something I'm trying to put together.

does anyone have an example of a script that when given a CSV it can match and update the 'money' column? thanks

#Get all items in this list and save them to a variable
$items = $list.items

#Go through all items
foreach($item in $items)
{
#If the "Title" column value equals "My first item!" do something with it
if($item["Title"] -eq "My first item!")
{
#Change the value of the "Title" column
$item["Title"] = "My first edited item!"

#Update the item
$item.Update()
}
}
  • What is the help you are looking for ? – Pushpendra Aug 6 '15 at 6:31
3

You need to use the Import-Csv to import the CSV data into PowerShell, and then you can loop through it.

So if you have a specific value you want to compare against and then update a list item in SharePoint, in your case comparing Money and then updating the list item matching the Customer number, the following can be an inspiration:

# Load in the CSV
$delimeter = ";"
$filePath = "path to the CSV file"
$csvData = Import-Csv $filePath -Delimiter $delimeter

# SharePoint column names
$moneyColumn = "Money"
$customerNoColumn = "CustomerNo"

# Get SharePoint list
$web = Get-SPWeb "site url"
$list = $web.Lists["list name"]

# Loop through each line in the CSV file
foreach ($line in $csvData)
{
    # Get item matching $customerNo
    $customerItem = $list.Items | Where $_.$customerNoColumn -eq $line.$customerNoColumn

    if ($customerItem -ne $null)
    {
        if ($customerItem[$moneyColumn] -ne $line.$moneyColumn)
        {
            $customerItem[$moneyColumn] = $line.$moneyColumn
            $customerItem.Update();
        }
    }
}
  • That's great thanks Daniel. I ended up having to do a nested loop but works as I need. Basically these figures are going to change each month so I've just opted to update the whole list each month. – pigeon Aug 7 '15 at 6:09
1

As per what Daniel suggested;

#Add SharePoint PowerShell Snapin which adds SharePoint specific cmdlets
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

#Load in the CSV
$delimeter = ","
$csvData = Import-Csv C:\CustomerData\customerspend.csv -Delimiter $delimeter

#Site Collection where you want to upload files
$webURL = "http://portal/site/ccp"
#List to update
$listName = "Customer List"

#SharePoint column names
$moneyColumn = "Spend"
$customerNoColumn = "Customer Number"

#Get the SharePoint List
$web = Get-SPWeb $webURL
#contains the Customer List variable
$list = $web.Lists[$listName]

$items = $list.Items


foreach ($line in $csvData)
{ 
    foreach($item in $items)
    {        
        if($item[$customerNoColumn] -eq $line.'Customer Number')
        {
           $item[$moneyColumn] = $line.Spend
            $item.Update();        
        }
     }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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