none
Powershell script to replace a section of url in a particular field of an item in sharepoint List RRS feed

  • Question

  • Hi,

    I have a sharepoint list with column named URL and the field type is hyperlink and it has the value as http://abc.com/xyz=123 and i need to replace this column value as http://test.com/abc=123 . I have to modify around 1000 list items. How do i replace the url in the Sharepoint list using powershell.

    Any help is much appreciated. Thank u


    Smile Always

    Friday, January 30, 2015 4:14 AM

Answers

  • [string]$first = "http://test.com/abc="
    $web = Get-SPWeb http://YourSite
    $list = $web.Lists["Yourlist"]
    $items = $list.items
    
    foreach($i in $items)
    {
        $change = $i["URLCOLUMN"]
        $Last = $change.Substring(19,3)
        $i["URLCOLUMN"] = $first + $last
    
        $i.Update()
    }

    Please try this in test first. I have tried it on mine at it works just fine:

    Please try this in test first. I have tried it on mine at it works just fine:

    Starting List:

    Finished List:

    I should also mention that you will probably need to change the substring numbers to fit the URL you're using. Eg: http://google.com/xyz=123 you would use:

    $change.substring(22,3)

    Count the letters to where you want it to stop so in the example above 22 characters will take you up to the = and give you back the last 3 (123)

    You could also use a workflow


    If this is helpful please mark it so. Also if this solved your problem mark as answer.







    • Edited by taylor.l Friday, January 30, 2015 10:11 AM
    • Marked as answer by Smile_Always Saturday, January 31, 2015 5:37 AM
    Friday, January 30, 2015 9:51 AM

All replies

  • try the following command:

    Add-PSSnapin Microsoft.SharePoint.PowerShell

    #Variables that we are going to use for list editing

    $webURL = <a href="http://sitecollection.com"> http://sitecollection.com </a>

    #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
    
    
    #Go through all items
    foreach($item in $items)
    {
    if($item["UrlColumn"] -eq "http://abc.com/xyz=123")
    {
    #Change the value of the "Title" column
    $item["UrlColumn "] = " http://test.com/abc=123 "
    
    
    #Update the item
    $item.Update()
    }

    }

    In case of issues check

    http://sharepointrelated.com/2012/05/03/addedit-list-items-using-powershell-in-sharepoint-2010/


    Please Mark it as answer if this reply helps you in resolving the issue,It will help other users facing similar problem

    Friday, January 30, 2015 5:16 AM
  • Hi,

    Thaks fopr your reply,

    But the URL column has unique values like below for each item

    1. http://abc.com/xyz=123

    2. http://abc.com/xyz=124

    3. http://abc.com/xyz=125

    4.http://abc.com/xyz=126

    And i need to replace this " http://abc.com/xyz=" to this "http://test.com/abc=" 


    Smile Always

    Friday, January 30, 2015 8:08 AM
  • [string]$first = "http://test.com/abc="
    $web = Get-SPWeb http://YourSite
    $list = $web.Lists["Yourlist"]
    $items = $list.items
    
    foreach($i in $items)
    {
        $change = $i["URLCOLUMN"]
        $Last = $change.Substring(19,3)
        $i["URLCOLUMN"] = $first + $last
    
        $i.Update()
    }

    Please try this in test first. I have tried it on mine at it works just fine:

    Please try this in test first. I have tried it on mine at it works just fine:

    Starting List:

    Finished List:

    I should also mention that you will probably need to change the substring numbers to fit the URL you're using. Eg: http://google.com/xyz=123 you would use:

    $change.substring(22,3)

    Count the letters to where you want it to stop so in the example above 22 characters will take you up to the = and give you back the last 3 (123)

    You could also use a workflow


    If this is helpful please mark it so. Also if this solved your problem mark as answer.







    • Edited by taylor.l Friday, January 30, 2015 10:11 AM
    • Marked as answer by Smile_Always Saturday, January 31, 2015 5:37 AM
    Friday, January 30, 2015 9:51 AM
  • Thank you so much. It worked like charm :).

    I have 57000 records in a list and it takes 40 minutes to update all the urls and during that 40 minutes if any user logs in to the site and when he tries to update or add any record in the list what happens?


    Smile Always

    Saturday, January 31, 2015 5:36 AM
  • The PowerShell script will enter a session when you run it. So it will only see everything from the point of it running. If anything is added during it running it shouldn't effect anything.  


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Saturday, January 31, 2015 8:56 PM