Replace Field Values

Posted on Posted in SharePoint 2007, STSADM Commands

Because I’m doing so much moving around of the post-upgraded sites I’m running into lots of issues with broken links. In an attempt to solve this problem I’ve begun working on 3 commands. This first command, gl-replacefieldvalues, will take in a search string and a replacement string and modify every list item where a match is made. The searching can be scoped to the entire farm, a single web application, a single site collection (and all sub-sites), a single web site (and all sub-sites) or a single list and can be further restricted to only update a specific field.

The command uses Regex.Replace() so you can get pretty complex with your replacements (I’m no regular expression expert so I’ve kept my uses fairly simplistic). The other two commands I’ll document later (one will apply all the URL changes identified in the Upgrade Area Url Mappings list and the other will attempt to handle content within web parts – I’m still creating these and should be finished some time this week).

The large bulk of this code is just a series of methods with different loops in them to handle the various scoping capabilities. The core code itself is simply looking at all fields of type string and doing a regular expression replace using the provided details. I’ve also added the ability to dump all changes to a log file as well as to run the command in a "test" mode where it will show you what it would change but not actually make the change – I’d strongly recommend you use this first to verify all the changes that will be made. The core code is shown below:

   1: private static void ReplaceValues(SPList list, Settings settings)
   2: {
   3:  Log(settings, "Processing List: " + list.DefaultViewUrl);
   4:  
   5:  Regex regex = new Regex(settings.SearchString);
   6:  
   7:  foreach (SPListItem item in list.Items)
   8:  {
   9:   if (item.File != null && !Utilities.IsCheckedOutByCurrentUser(item))
  10:   {
  11:    continue;
  12:   }
  13:   bool wasCheckedOut = true;
  14:   bool modified = false;
  15:  
  16:   foreach (SPField field in list.Fields)
  17:   {
  18:    if (item[field.Id] == null || field.ReadOnlyField)
  19:     continue;
  20:  
  21:    if (list.Title == "98d3057cd9024c27b2007643c1" && field.Title == "V2ServerRelativeUrl")
  22:     continue; // We don't want to change this url because then external links will break.
  23:  
  24:    Type fieldType = item[field.Id].GetType();
  25:  
  26:    if (fieldType != typeof(string))
  27:     continue; // We're only going to work with strings.
  28:  
  29:    string fieldName = field.Title.ToLowerInvariant();
  30:    if (settings.UseInternalFieldName)
  31:     fieldName = field.InternalName.ToLowerInvariant();
  32:  
  33:    if (settings.FieldName == null || settings.FieldName.ToLowerInvariant() == fieldName)
  34:    {
  35:     bool isMatch = regex.IsMatch((string)item[field.Id]);
  36:     
  37:     if (!isMatch)
  38:      continue;
  39:     string result = regex.Replace((string) item[field.Id], settings.ReplaceString);
  40:  
  41:     Log(settings, string.Format("Match found: List={0}, Field={1}, Replacement={2} => {3}", item.Url, field.Title, item[field.Id], result));
  42:  
  43:     if (!settings.Test)
  44:     {
  45:      if (item.File != null && item.File.CheckOutStatus == SPFile.SPCheckOutStatus.None)
  46:      {
  47:       item.File.CheckOut();
  48:       wasCheckedOut = false;
  49:      }
  50:      item[field.Id] = result;
  51:      modified = true;
  52:     }
  53:    }
  54:   }
  55:   if (modified && !settings.Test)
  56:    item.Update();
  57:  
  58:   if (modified && item.File != null)
  59:    item.File.CheckIn("Checking in changes to list item due to automated search and replace (\"" + settings.SearchString + "\" replaced with \"" + settings.ReplaceString + "\").");
  60:   else if (!wasCheckedOut && item.File != null)
  61:    item.File.UndoCheckOut();
  62:  
  63:   if (modified && settings.Publish && item.File != null)
  64:    item.File.Publish("Publishing changes to list item due to automated search and replace (\"" + settings.SearchString + "\" replaced with \"" + settings.ReplaceString + "\").");
  65:  
  66:   if (modified && settings.Publish && item.ModerationInformation != null && item.File != null)
  67:    item.File.Approve("Approving changes to list item due to automated search and replace (\"" + settings.SearchString + "\" replaced with \"" + settings.ReplaceString + "\").");
  68:  
  69:  }
  70:  Log(settings, "Finished Processing List: " + list.DefaultViewUrl + "\r\n");
  71: }

The syntax of the command I created can be seen below.

C:\>stsadm -help gl-replacefieldvalues

stsadm -o gl-replacefieldvalues

Replaces all occurrences of the search string with the replacement string.  Supports use of regular expressions.  Use -test to verify your replacements before executing.

Parameters:
        [-url <url to search>]
        {-inputfile <input file> |
         -searchstring <regular expression string to search for>
         -replacestring <replacement string>}
        -scope <Farm | WebApplication | Site | Web | List>
        [-field <field name>]
        [-useinternalfieldname (if not present then the display name will be used)]
        [-inputfiledelimiter <delimiter character to use in the input file (default is "|")>]
        [-inputfileisxml (input is XML in the following format: <Replacements><Replacement><SearchString>string</SearchString><ReplaceString>string</ReplaceString></Replacement><Replacements>)
        [-quiet]
        [-test]
        [-logfile <log file>]
        [-publish]

Here’s an example of how to replace all occurrences of "/Topics/Divisions/HumanResources/" with "/hr/" in all lists within a web application using a case insensitive match:

stsadm -o gl-replacefieldvalues -url "http://intranet/" -scope webapplication -searchstring "(?i:/Topics/Divisions/HumanResources/)" -replacestring "/hr/" -logfile "c:\replace.log" -publish

If you wish to filter by field name you can specify either the display name or the internal name. Note that if you specify the internal name you must also add the "useinternalfieldname" parameter – if you don’t use the internal field name then be aware that you may be updating more than the field you intended as the display name is not always unique (but in most cases it is).

If you don’t want your changes published then don’t specify the publish parameter. However, specifying the publish parameter will not publish items that were previously checked out. If an item can be published and it requires approval then specifying the publish parameter will also cause the item to be approved. Use of regular expressions can be extremely powerful and dangerous especially when updating at a large scope so again, I strongly encourage the use of the -test parameter to verify the changes to be made before you actually apply your changes.

Update 12/13/2007: I’ve updated this command to support the passing in of an input file containing search and replace strings. The input file can be either a flat file where each replacement is on a separate line with a delimiter separating the search and replace strings or an XML file. The syntax details have been updated above. Thanks to Glenn Hickman for helping with this.

19 thoughts on “Replace Field Values

  1. Thanks also Gary, this came in very handy. I have extended your ‘replacefieldvalues’ somewhat by allowing to pass an input file containing the replacements. If specified, the command iterates over an input file containing a pipe separated set of source and replace strings.

    Let me know if you are interested in getting a patch for this by posting back here.

  2. I’m definitely interested in seeing what you did – if I can integrate it into mine I’ll certainly try. You can email to me at gary at thelapointes dot com (if you zip it up just change the zip extension or my company’s spam filter will block it).

  3. Hi, thanks for all your work on this gary,

    I am however experiencing mixed results with running this command on a web application in order to update the evil telerik formats for angle brackets (~GT~ and ~LT). some of the content is updated while other content is not and the formatting remains…have you or anyone else encountered this?

    Thanks,
    Mike

  4. It’s tough to say what the issue without having a test environment to simulate the problem (and I’ve not used the Telerik controls). Have you checked that your regular expressions are sufficient? For example: -searchstring “(?i:~GT~)” -replacestring “>”
    Also – if you could replicate in a virtual environment you could step through the code and see what’s happening when it hits a value that you expect to get modified. If you find a bug please let me know and I’ll try to incorporate your changes.

  5. Hi All,

    We have tried this to run in our virtual box and recieved some errors while compiling it in .net framework..

    This is a good work. Please provide the intructions to compile or run the application.

    thanks in advance.

  6. Hi Gary,

    I appreciate your prompt reply to this post. Thank you.
    Could you please provide details for bellow dumb quires ?

    1.How to run/download the WCF which intern replaces the odd characters ?

    2.As seen in code; which has Sharepoint list and settings as a input parameters ! How to input those values ?

    3.where to place the Code ?

    4.What is the logic behind the replace mechanism ? does it searches in each node for this odd character?

    5.Initially we had tried to replace these odd characters from DB end ! ; some part of it has been resolved however some parts are missing or some parts are not changed! .
    How exactly the MOSS/MCMS store the data?

    Thanks for your help.

  7. Not sure what you mean by WCF (assuming you mean WSP?). If you go to my downloads page you can download the appropriate WSP file – there’s install instructions on the page. Once installed you can run the stsadm command as shown in the example – make sure you are running with a farm admin account. The replace loops through all list items in every list and does a regex.replace using the provided parameters. You should never edit the database data directly as this will violate your support agreement with Microsoft.

  8. Hi Gary, using the search and replace input file sounds great, can we highlight say the ID field number and replace content in another field, say the created by or possibily the created date?

  9. Hi Gary… Any chance you can give me a pointer into how I replace the GUID of an Audience once they have been imported?? I have audiences on the Navigation of my site and they are now out of Sync with the SSP.

    Thanks

  10. Hi Gary,

    i have the following problem:
    i have to update list item fields (i.e. “Worked Hours” (Number)) regularly by an Identifier (i.e. “ProjectName” (String)).

    So how can i update a field by defining an identifier? “Import”-Structure is to be defined by me, so i could get data-to-be-imported right into powershell or dos-batch-format containing whole command….
    Can you help me please?

  11. This is exactly what I was looking for as I needed to fix that infamous MCMS->MOSS Telerik ~LT~/~GT~ issue. However, when I ran the command, instead of converting the ~LT~/~GT~ to characters that get parsed as HTML tags, it creates *actual* characters, which it then escapes to < and >, which don’t get parsed, but merely displayed. I hope that made sense!

    I can’t seem to figure out how to get it to work otherwise. Any ideas?

  12. Gary, Wow! Thank you so much for all the reasons that I’m sure you know.

    I’m trying to repair the icon URL for each web in a site that was moved. I’ve run both gl-replacewebpartcontent and gl-replacefieldvalues (which worked great). However, the icon URL for each web is still pointing to the old location. Any advice regarding this?

  13. Hi Gary,
    can you share the code for calling the input file? this would be handy, as I like to integrate it to your other script as well (replacewebpartcontent).

Leave a Reply

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

*