Add/Edit list items using PowerShell in SharePoint 2010

During the last few months I have been asked a few times to help think of a way to edit list values for lists that contain a lot of data. I immediately thought of PowerShell. This is why I want to show you what you can do with PowerShell.

Used set up for this demo
I have created a test environment for this demonstration. I will be using the following:
http://portal.contoso.com (SPWeb URL)
– Demo List (Custom List). This list has 1 extra added column: Information (Multiple lines of text)

image

Basics
To work with a list, you will have to start off by retrieving the list in PowerShell. For demonstration purposes, I will try to make it as easy and clear as possible:

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

#Variables that we are going to use for list editing
$webURL = <a href="http://portal.contoso.com">http://portal.contoso.com</a>
$listName = "Demo List"

#Get the SPWeb object and save it to a variable
$web = Get-SPWeb $webURL

#Get the SPList object to retrieve the "Demo List"
$list = $web.Lists[$listName]

These are the basics. With this, we can start to add items to this list or modify existing items.

Add items to the Demo List
So we got the list in our $list variable, now what?
To add an item to this list, we will need to use the Items.Add() method.

#Create a new item
$newItem = $list.Items.Add()

#Add properties to this list item
$newItem["Title"] = "My first item!"
$newItem["Information"] = "This is a multiple lines of text field `nSo I can use multiple lines"

#Update the object so it gets saved to the list
$newItem.Update()

Here is the output:

image

The `n part in: $newItem[“Information”] = “This is a multiple lines of text field `nSo I can use multiple lines”, is a New line. This works if you use the “Plain text” type, but not when you use “Rich text” or “Enhanced rich text”.

That was pretty easy right?

Add attachments to items
In the comments section, Nino asked if it was possible to add attachments for each new item created. Here is the way to do this.

$filebytes = [System.IO.File]::ReadAllBytes("D:dummyfile.txt")
$newItem.Attachments.Add("DummyFile Title",$filebytes)

Edit existing list items
Now let’s say you want to edit the Information for this “first item”. We can do this with PowerShell too. Of course, if you would only change 1 item, you should do it by using the GUI, but what happens if you have to do the same thing for 5000+ items?

#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()
}
}

After running this part, we get the following result:

image

Hooray! It worked.

Now imagine all the things you can do with PowerShell and huge lists!
You could add column information for every item, or have a .csv or xml file which contains the items that should be changed. The possibilities are endless!

51 thoughts on “Add/Edit list items using PowerShell in SharePoint 2010

  1. Thanks for this post. Is there a simple way to add a list item with Rich Text in the body? I tried $newItem[“Body”] = Get-Content “somefile.rtf” but this simply adds “System.Object[]” to the list item body.

  2. Hi Wictor,

    Thanks for your reply. There are some things you have to keep in mind:

    1. When you use the Get-Content cmdlet, it will retrieve each line and save each line in an object. This is wh it returns System.Object[].
    If this were the only issue, you could create a $string variable that contains every item in the array like this:
    Get-Content “C:Usersnmadmindesktopsomefile.rtf” | %{$string += $($_.toString())}

    2. You would have to make sure the formatting is done in the same way as SharePoint does it.
    For instance, when I create a .rtf file with WordPad, it will look like this:

    I am creating some nice b bold b0 and i italic i0 text in this file

    The way SharePoint does it it looks like this:

    I am creating some nice bold and italic text in this file

    If the formatting is correct, you could use my line of code to convert the items in the array to a single string and use that $string variable to add to $newItem. So: $newItem[“Body”] = $string

    If you have any questions, please let me know.

  3. Pingback: Blog de P@blo - Sharepoint Server

  4. Hi Nino,

    If you post the script you have so far, I can try to add the attachments with each item, as this is specific to your situation.

    In general, to add attachments to a new item, you can enter the following information when creating the $newItem (Before the $newItem.Update()):

    $filebytes = [System.IO.File]::ReadAllBytes(“D:dummyfile.txt”)
    $newItem.Attachments.Add(“DummyFile Title”,$filebytes)

    If you have questions, please let me know.

  5. Working at the NHS, I had to add a 5-6million line long Xml into an SharePoint list (a port of defect tracking system into SharePoint) with thousands of attachments… If anyone is interested in something similar, I can dig out the code.

  6. Hi Qamar,

    Personally, I would be very interesting in the code you used. With your permission, I might even use it to improve my current code, or add it to the current post. Of course, all credit for this will go to you 🙂

    Greets

  7. Have you worked with setting Image fields in a list? I tried setting the ImageUrl first, but that didn’t work. The Image field is a compound field. Then I tried the following:

    $listItem = $spList.additem()
    $image = new-object Microsoft.SharePoint.Publishing.Fields.ImageFieldValue
    $image.ImageUrl = $ImageUrlValue
    $listItem[“Rollup Image”] = $image
    $listItem.Update()

    Any suggestions would be much appreciated.

    • Hi Peter,

      Thanks for replying.
      I just added a image column called “Image”.
      Afterwards, I set the “Format URL as” to Image, so it becomes an image field.

      To add an item and also fill the picture field I did the following:

      $list = (Get-SPWeb “https://sp2010.sharepointrelated.com).Lists[“List”]
      $newItem = $list.Items.Add()
      $newItem[“Title”] = “New Item”
      $newItem[“Image”] = “http://nicosharepoint.files.wordpress.com/2012/05/image1.png”
      $newItem.Update()

      Refreshing the page resulted in an image displaying in the list. Please let me know what problems you are facing when using this code.

      Regards, Nico

    • Hi John,

      In order to fill a person field, you have to get the user object first.
      For instance, if I would want to fill the person field with my “sprelatednmadmin” account, what I would do is:

      $user = Get-SPUser “sprelatednmadmin” -Web https://portal.sharepointrelated.com/yoursite

      Now I am able to add this to the person field, so:

      $newItem = $list.Items.Add()
      $newItem[“Title”] = “Test”
      $newItem[“Person”] = $user
      $newItem.Update()

      Make sure the $user variable is filled, otherwise you will get an error.
      If you need any help with this, please let me know.

      Greets, Nico

  8. OK Got the URL to pull but now I am getting I am missing a termination where the
    $item[“Title”] = “My first edited item!” line is

    • Hi Fran,

      What is it you are trying to do? Are you trying to add a new item or are you trying to modify an existing item?
      If you want to edit an existing item, make sure your “where” statement is retrieving an item, after that you will be able to modify it any way you want.

      Let me know if you need help. If you are not sure, please post the script you used.

      Greets, Nico

  9. Ok Here is my script. I am trying to modify an existing item (field) in the list depending on the value and the Project ID. Here is the code:

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

    #Variables that we are going to use for list editing
    $list = (Get-SPWeb “https://www.test).Lists[“Support Request”]

    #$webURL = https://www.test.whatever
    #$listName = “Support Request”

    #Get the SPWeb object and save it to a variable
    $web = Get-SPWeb $webURL

    #Get the SPList object to retrieve the “Support Request”
    $list = $web.Lists[$listName]

    #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 “Run Workflow” column value not equals “Yes” do something with it
    if($item[“Run Workflow”] -ne “Yes” -AND $item[“Project Team:ID”] -eq ‘1’)
    {
    #Change the value of the “Run Workflow” column
    $item[“Run Workflow”] = “Yes”

    #Update the item
    $item.Update()
    }
    }

    I got an error using the webURL that you used, no error with the one un-commented. Getting a parser error on:
    $item[Run Workflow”] = “Yes”
    Missing terminator
    Than you for any help

  10. I don’t get it.
    It returns: “Name=1;#My First Name 2;#My Last Name

    How do I get rid of the 1;# part?

    #Go through all items
    foreach($item in $items){
    write-host “Name= ” $item[“First Name”] $item[“Last Name”]
    if($item[“Last Name”] -eq $whatlastname -and $item[“First Name”] -eq $whatfirstname){
    write-host “match”
    $item[“Account”] = $whatusername
    $item.Update()
    }

    • Hi Gabriel,

      I am guessing that you are using a person field to display the first name and the last name.
      In SharePoint, the value behind the displayed name is the “User” object. That is what it is showing you when you try to retrieve the data from PowerShell.

      For now I do not know a quick way to check the data, you could use the substring or split function to filter the name like this:

      $firstname = $item[“First name”]
      $name = ($firstname.split(“#”,($firstname.length)))[1]

      Regards, Nico

  11. Hi Nico! – how would one use the contents of the “Title” column in a foreach loop? I have a SharePoint list of computers I need to do a file check on, column A, that would then update column B based on the file check results.

    Would it be:
    foreach ($computer in $list.items.computer) {…}
    Where $list.items.computer is the column name?

  12. Hi Lucas,

    So what you’re saying is you got a list with 2 columns (that are relevant).
    for each item where column A = “Computer A” , Column B should be updated?

    In this case, you can use the following:

    foreach($item in $items)
    {
    if($item[“Column A”] -eq “Computer A”)
    {
    $item[“Column B”] = “Some other value”
    }
    }

    Please let me know if you have any questions.

    Regards, Nico

    • Nico – this is what I have. Instead of using a txt file with the computers, I’d like to use the SharePoint list column “Computer”. I’m not sure what to replace this line with: foreach ($computer in $computerlist)

      # Import Computer List
      $computerlist = Get-Content ‘\serverc$scriptsonline_computers.txt’

      # Get day and time
      $dayandtime = Get-Date

      foreach ($computer in $computerlist)
      {
      IF ( (Test-Path “\$computerc$success.flag”) -eq “True”)
      {
      $items = $list.items
      # Go through all items
      foreach($item in $items)
      {
      # If the “Computer” column value equals the computername, update the status
      if(($item[“Computer”] -eq “$computer”) -and ($item[“Status”] -eq “No”) -and ($item[“Skip”] -ne “Skip”))
      {
      # Update the “Status” column
      $item[“Status”] = “Yes”
      $item[“Deployment Category”]=”Status changed to Yes at ” + $dayandtime
      $item.Update()
      }
      }
      }
      }

      • Hi Lucas,

        I just recreated a part of your configuration, the following script should work. Please change any variables that need changing! I hope it works for you

        #Add SharePoint PowerShell Snapin which adds SharePoint specific cmdlets
        Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
         
        #Variables that we are going to use for list editing
        $webURL = "http://URL"
        $listName = "Computers"
         
        # Import Computer List
        $computerlist = Get-Content "\serverc$scriptsonline_computers.txt"
        
        # Get day and time
        $dayandtime = Get-Date
         
        #Get the SPWeb object and save it to a variable
        $web = Get-SPWeb $webURL
         
        #Get the SPList object to retrieve the "Demo List"
        $list = $web.Lists[$listName]
        
        #Get all items in this list and save them to a variable
        $items = $list.items
         
        foreach ($computer in $computerlist)
        {
        Write-host $computer
        IF ((Test-Path "\$computerc$Infosuccess.flag") -eq "True")
        {
        # Go through all items
        foreach($item in $items)
        {
        # If the "Computer" column value equals the computername, update the status
        if(($item["Computer"] -eq $computer) -AND ($item["Status"] -eq "No") -and ($item["Skip"] -ne "Skip"))
        {
        # Update the "Status" column
        $item["Status"] = "Yes"
        $item["Deployment Category"]="Status changed to Yes at + $dayandtime"
        $item.Update()
        }
        }
        }
        }
        
  13. Hi, Niko!
    Thank you for this post! If I work with the list of a URL, how can I add a description to an element? This field has no “FieldName” parameter

    • Hi Nikita,

      If you are working with a hyperlink field, you can use the following:
      $newitem = $list.items.Add()
      $newitem[“URLField”] = “http://yoururl.com, Your description”
      $newitem.Update()

      So use the comma (,) as a separator.
      Hope this helps.

  14. Hi Niko
    how can I add more many items to the same list, I will be retrieving those items with powershell script from AD

    thanks in advance

    • Hi,

      You could create a foreach loop to add multiple items.
      Ofcourse, I am not sure how you retrieve your items from AD. If you put them in the $itemsfromAD it could be something like this.

      foreach($itemfromAD in $itemsfromAD)
      {
      $newItem = $list.Items.Add()

      #Add properties to this list item
      $newItem[“Title”] = $itemfromAD.property1
      $newItem[“Information”] = $itemfromAD.property2

      #Update the object so it gets saved to the list
      $newItem.Update()
      }

      • Hey Nico.

        Thanks for the quality content. Hoping you can help!

        I’ve got a directory with 50+ CSV files each contain 10-30 server names. each csv is named after the Service Line the containing servers present. (eg Exchange.csv).

        What I’m trying achieve is the automated import into a new Sharepoint list for each service line. (named after the relevant CSV) and each list contains the content of each csv.

        I can, using powershell, import a service csv’s contents into a preexisting list, but not create one on each import.

        Can you help? thanks v much in advance,

        • Hi James,

          If I understand correctly, you would like to create a SharePoint list (custom) for each serviceline.
          Then for each csv import the data into the list you created.

          I’ll get back to you in about an hour, shouldn’t be too hard.

          Regards, Nico

          • So, I got the part to create a list for each service CSV.

            Can you tell me which columns you have in your CSV, so I can check how to create the columns + add the information to them?

          • Hi James,

            I recommend creating a testsite first, before doing this in production, as it is very specific.
            Let me know the outcome.

            #variables
            $CSVPath = "C:Temp"
            $siteURL = "https://sharepoint.yourdomain.com/sites/site/subsite"
            
            asnp *sh* -EA SilentlyContinue
            
            #Check CSVPath variable
            if(!(Test-Path $CSVPath)){Write-Host "$CSVPath does not exist" -ForegroundColor Red;exit}
            #Add CSV files to array
            else{$CSVs = $CSVPath | Get-ChildItem |?{$_.extension -eq ".csv"}; Write-Host "$($CSVs.count) CSV files added to array" -ForegroundColor Green}
            
            #Check siteURL variable
            if(!(Get-SPWeb $siteURL)){Write-Host "$SiteURL is not a valid SharePoint web object" -ForegroundColor Red; exit}
            #Get the SPWeb object for the siteURL variable
            else{$web = Get-SPWeb $siteURL;Write-Host "$siteURL loaded" -ForegroundColor Green}
            
            #Get the custom list template
            $listTemplate = $web.ListTemplates["Custom List"]
            
            
            foreach($CSV in $CSVs)
            {
            ##Create lists for each CSV
            try{
            $web.Lists.Add($CSV.BaseName,$CSV.BaseName,$listTemplate) | Out-Null
            Write-Host "$($CSV.BaseName) Created" -ForegroundColor Green
            }
            catch
            {
            Write-Host "Something went wrong while creating list $($CSV.BaseName)" -ForegroundColor Red
            }
            
            ##Add columns to list
            try{
            $fieldType = [Microsoft.SharePoint.SPFieldType]::Text
            $list = $web.Lists[$CSV.BaseName] 
            $field1 = $list.Fields.Add("MasterVm",$fieldType,$false)
            $field2 = $list.Fields.Add("DataStore",$fieldType,$false)
            $list.Update()
            }
            catch
            {
            Write-Host "Something went wrong while adding columns" -ForegroundColor Red
            }
            
            ##Add fields to view
            try{
            #Get default view
            $view = $list.DefaultView
            #Add fields to view
            $view.ViewFields.Add($field1)
            $view.ViewFields.Add($field2)
            $view.Update()
            }
            catch
            {
            }
            
            ##Add items from CSV
            try
            {
            $CSVContent = Import-CSV $CSV.FullName
                foreach($line in $CSVContent)
                {
                    $item = $list.Items.Add()
                    $item["MasterVm"] = $line.MasterVm
                    $item["DataStore"] = $line.DataStore
                    $item.Update()
                }
            }
            catch
            {
            Write-Host "Something went wrong while adding items" -ForegroundColor Red
            }
            }
            
  15. Hey Nico, thanks very much for the excellent code. it successfully added all of the lists with the correct names which is great & it managed to successfully add the first 4 (of 47) then “something went wrong” for the rest. ive checked the CSVs and they are all formatted correctly. if i try to run this again – will it duplicate the data? cheers.

  16. I would recommend moving the CSV that were successful into another folder. Then use the following code to try again for the lists that failed (I removed some code to ensure no double data is added):

    #variables
    $CSVPath = "C:Temp"
    $siteURL = "https://sharepoint.yourdomain.com/sites/site/subsite"
    
    
    asnp *sh* -EA SilentlyContinue
    
    #Check CSVPath variable
    if(!(Test-Path $CSVPath)){Write-Host "$CSVPath does not exist" -ForegroundColor Red;exit}
    #Add CSV files to array
    else{$CSVs = $CSVPath | Get-ChildItem |?{$_.extension -eq ".csv"}; Write-Host "$($CSVs.count) CSV files added to array" -ForegroundColor Green}
    
    #Check siteURL variable
    if(!(Get-SPWeb $siteURL)){Write-Host "$SiteURL is not a valid SharePoint web object" -ForegroundColor Red; exit}
    #Get the SPWeb object for the siteURL variable
    else{$web = Get-SPWeb $siteURL;Write-Host "$siteURL loaded" -ForegroundColor Green}
    
    
    
    ##Add items from CSV
    try
    {
    $CSVContent = Import-CSV $CSV.FullName
        foreach($line in $CSVContent)
        {
            $item = $list.Items.Add()
            $item["MasterVm"] = $line.MasterVm
            $item["DataStore"] = $line.DataStore
            $item.Update()
        }
    }
    catch
    {
    Write-Host "Something went wrong while adding items" -ForegroundColor Red
    }
    
  17. Hey Nico,

    Thanks for the detailed explanation. I have a question here.

    I have a web application with good number of site collections. Most of the sites uses a custom list which is having rich text field enabled. Is there a way I can generate a report of list items having rich text feature enabled?

    Regards,

    Prajith.

  18. Pingback: Optimizing PowerShell workflows in vRealize Orchestrator - VMware Cloud Management

  19. Hi,
    Thanks a lot for this tip. It works almost perfectly on my environment but I got a small issue: This script works in Powershell ISE with no error on small list, but when I tried to do the same on the bigger list, it didn’t work at all. My intention is to replace all “Przemek” in User column into “1”.

    #Variables that we are going to use for list editing
    $webURL = “http://contoso.com”
    $listName = “test_list”

    #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 “User” column value equals “Przemek” do something with it
    if($item[“User”] -eq “Przemek”)
    {
    #Change the value of the “User” column
    $item[“User”] = “1”

    #Update the item
    $item.Update()
    }
    }

    As I didn’t got any error message, I copied this script to the powershell and I have received:

    Cannot index into a null array.
    At line:4 char:10
    + if($item[ <<<< "User"] -eq "Przemek")
    + CategoryInfo : InvalidOperation: (User:String) [], RuntimeExcep
    tion
    + FullyQualifiedErrorId : NullArray
    I checked that my list is null but don't know why. ($list -eq $null) – True

  20. OK let me answet for my own question:
    I didn’t connected list with the web site. Now I have the final script:

    #Variables that we are going to use for list editing
    $WebURL = “http://contoso”
    $ListName = “Test”

    $Web = Get-SPWeb $WebURL
    $List = $Web.Lists[$ListName]

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

    #Go through all items
    foreach($item in $items)
    {
    #display all items below in the log
    #$item
    #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”] = “Changed record”

    #Update the item
    $item.Update()
    }
    }

    Now I will thank to myself 😉

Leave a Reply

Your email address will not be published. Required fields are marked *