none
Scripting Updates to Sharepoint List Items with Powershell? RRS feed

  • Question

  • I've got a list with about 2300 items in it, maybe 40 columns. Recently, someone showed up at my desk(as they're prone to doing) and said: "I have just been given a list of about 150 items that are wrong on this list." The list of edits is a spreadsheet, and the names of the items are what's wrong. The spreadsheet contains two columns: "Find this" and "Replace with this"

     

    What I'm trying to do is along the lines of:

    foreach($item in list.csv) 
    {find the item named for cell A2 and rename the item to the contents of cell B2}
    

     

    Pieces of the puzzle that would help me:

    • Method of connecting to the Sharepoint list via powershell
    • A way to format the name of the list item so that only the text is required.  
    • A function to rename/update a list item.

     

     

    Any advice or tips? Can anyone recommend a good resource for learning powershell automation of Sharepoint list edits?

    Obviously for a single case it'd be faster for me to just find/replace with Excel.  But this happens pretty frequently, and I would love to be able to just collect a spreadsheet from the users that need bulk edits.

     

    Any help, or even a general direction, would be greatly appreciated.


    David Caudill IT Manager
    Wednesday, August 10, 2011 1:07 AM

All replies

  • Hi David,

    There are a lot of things to learn in PowerShell. You can start here: http://technet.microsoft.com/en-us/library/ee806878.aspx

    Also, for list modifications and lots of other common things you can refer to this article: http://blogs.technet.com/b/heyscriptingguy/archive/2010/09/22/use-powershell-to-manage-lists-views-and-items-in-sharepoint.aspx

    For you problem, try using this script (but try on you development environment first)

    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
    $url = "http://servername/web"
    
    function Modify-SPListData() {
    	 $data = Import-Csv "C:\list.csv"
    	 $OpenWeb = Get-SPWeb $url
    	 $list = $OpenWeb.Lists["ListName"]
    	 
    	 foreach($row in $data)
    	 {
    	 	$spQuery = New-Object Microsoft.SharePoint.SPQuery
    	 	$camlQuery = ‘<Where><Eq><FieldRef Name="Title" /><Value Type="Text">$row.OriginalString</Value></Eq></Where>' 
    		$spQuery.Query = $camlQuery 
    		$listItems = $list.GetItems($spQuery)
    		
    		#GetItems() method returns a ListItemCollection, so even if only one value is returned, 
    		#we need to loop through each item
    		
    	 	foreach($item in $listItems)
    		{
    			$item["ColumnName"] = $row.ReplaceString
    			$item.Update()
    		}
    	 }
    	 if($OpenWeb)
    	 {
    		$OpenWeb.Dispose()
    	 }
    }
    
    Modify-SPListData
    
    

     

    Assumptions:
    • Your list is stored in a CSV file (for the Import-Csv command to work)
    • The 2 columns in the CSV are named OriginalString and ReplaceString (you can always change in the CSV and in the script above, I have just used them for simplicity)

    - Sid

    Wednesday, August 10, 2011 2:43 AM
  • Sid,

    Thanks so much for the quality reply!  I have already read both articles- they are bookmarked and will come in handy in the future!  Sharepoint has 500+ powershell cmdlets, it's hard to know the right one for the job sometimes.

    I adjusted the parameters for my test environment and this script ran without error- but it didn't make any changes.  Any ideas about how to troubleshoot this?  There are no ULS errors, and I have tried running from the Sharepoint Management shell with an elevated prompt.  I deliberately changed the list name to something incorrect, and I did get a series of errors that would seem to indicate the importing the CSV and looping through it is happening correctly.  Are there any DLLs I need to load?

    Here are my suspicions based on our experiences using sharepoint as a development platform:

    The list URL contains spaces.

    The item names contain multiple dots/periods. (They look like WyprZ.0108596.d.A.GS00735)

     

    Thanks again for the help- I'm on the right path now!


    David Caudill IT Manager
    Wednesday, August 10, 2011 5:55 PM
  • The best way to troubleshoot is to output your variables.

    For example, I think the problem might be that the CAML query is not returning anything.

    So to output a variable, you just type the variable i.e. $listItems

    Also, you need to understand the CAML query in the above script. We are trying to see if column "Title" matches the OriginalString column in CSV.

    So you can change that if the column you are matching is some other name.

    I would also output $camlquery to see if the query is including the proper $row.OriginalString value.

    Let me know how you go!


    - Sid
    Wednesday, August 10, 2011 11:35 PM
  • Hi Sid,

     

    I've spent the last several days deconstructing this line by line, and WOW have I learned a lot doing it.  

    I think that the problem is definitely the CAML.  When I output the "camlQuery" variable as part of the foreach loop, I can see that the actual CAML being executed is:

    <Where><Eq><FieldRef Name="Title"/><Value Type="Text">$row.OriginalString</Value></Eq></Where>

    So the problem appears to be with the variables from the CSV not being properly inserted into the CAML.    I found this thread about inserting variables in CAML queries:  http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/a7e97851-e82a-460a-a99e-37aab3cd2a63  but I'm having a hard time adapting their code samples to my script to test.  Any ideas about how to troubleshoot this?


    David Caudill IT Manager
    Monday, August 15, 2011 11:33 PM
  • David,

    Try this

    $camlQuery = '<Where><Eq><FieldRef Name="Title" /><Value Type="Text">' + $row.OriginalString + '</Value></Eq></Where>' 
    

     

     

     


    - Sid
    Tuesday, August 16, 2011 5:54 AM
  • Sid- you were dead on about exiting the CAML for the variable.  I beat you to it by about an hour. :)  I was using the wrong method of quoting.  I've pasted my complete code below to help anybody else who might need to script bulk edits.  This will be a huge help to my company.  We frequently have changes in naming convention, model numbers, etc. that will require this kind of replacement task.  Thanks so much for your help!

    Here's the code I got working, with ample commenting:  (I would love any advice you have on efficiency)

    1. <# This script is designed to take a CSV of desired corrections/edits and
    2. perform them on a Sharepoint list. The CSV must contain two columns- "OriginalString" and "ReplaceString".  In this script the list column named "Title" will be corrected.a#>
    3. [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
    4.  
    5. <#Defining the function, and setting some basic variables.  Note the the spWeb variable should be set to the full
    6. path of the parent site of your list, and the spList variable should be set to the full URL of your list (sans view URL).:#>
    7. function Modify-SPListData() {
    8.          $data = Import-Csv "C:\scripts\typos.csv"
    9.          $spWeb = Get-SPWeb -Identity http://yoursite/yoursubsite
    10.          $spList = $spWeb.GetList("http://yoursite/yoursubsite/yourlist")
    11.         <# In this portion, we will use SPQuery to send CAML to the list via web service connection.  This portion loops through every edit in the CSV, searches for what's in column one and replaces it with row two.  
    12.         The CAML query looks a little stilted as designed- this is because we have to exit CAML to call a powershell variable, then jump back in.  You can't call a powershell string variable in CAML.#>
    13.          foreach($row in $data)
    14.          {
    15.                 $spQuery = New-Object Microsoft.SharePoint.SPQuery
    16.                 $camlQuery = '<Where><Eq><FieldRef Name="Title" /><Value Type="Text">' +$row.OriginalString + '</Value></Eq></Where>'
    17.                 $spQuery.Query = $camlQuery
    18.                 $spListItems = $spList.GetItems($spQuery)
    19.        
    20.                
    21.                 <#GetItems() method returns a ListItemCollection, so even if only one value is returned,
    22.                 we need to loop through each item.  A quick echo statement will provide a record of what edits were performed.#>
    23.                
    24.                 foreach($item in $splistItems)
    25.                 {
    26.                
    27.                         echo "$row.originalstring will be replaced with $row.replacestring"
    28.                         $item["Title"] = $row.ReplaceString
    29.                         $item.Update()
    30.                 }
    31.          }
    32.          if($OpenWeb)
    33.          {
    34.                 $OpenWeb.Dispose()
    35.          }
    36. }
    37. <#Now that the function is defined- execute#>
    38. Modify-SPListData


    David Caudill IT Manager
    Wednesday, August 17, 2011 12:34 AM
  • Hi david,

    The only thing is that if you are using $spWeb as your variable, then dispose that not $OpenWeb :)

    1. if($spWeb)
    2. {          
    3.          $spWeb.Dispose()
    4. }

    - Sid
    Thursday, August 18, 2011 11:22 PM
  • Hi,

    What if I want to replace only a single text on all items, that too by manually entering it, not importing from .csv file?

    (I'm New to SP and Powershell)



    Tuesday, May 29, 2012 10:39 AM