MOSS MVP

I've moved my blog to http://blog.falchionconsulting.com!. Please update your links. This blog is no longer in use--you can find all posts and comments at my new blog; I will no longer be posting to this site and comments have been disabled.

Thursday, November 8, 2007

Export, Import, and Update List Fields

One thing that's been hanging over my head for a while is what to do about the Site Directory. The first problem was to get the business users to decide on where it should live (the master site directory that is) and what columns (or meta data) should be part of the directory (either new columns or changes to existing columns). Once I finally got that information I had to solve my second problem which was to find a way to script all the changes. I've got commands for moving lists and list items but I had no ability to add new fields or update existing fields.

After looking at the SPField object some I discovered that the SchemaXml property has a getter and a setter and that I could add new fields via the SPFieldCollection.AddFieldAsXml() method which takes in the same XML that you can get via the SchemaXml property. With this information I concluded that I could very easily create commands to export, import, and update a list field.

The commands I created are: gl-exportlistfield, gl-importlistfield, and gl-updatelistfield. Now I could set up a field in my test environment, export the field out to an XML file and then import or update that field during the upgrade (depending on whether it's a new field or changes to an existing field). Fortunately the code to do all of this was extremely simple and it only took me a few minutes to create and test each one. The import and export are the simplest and least likely to throw errors - the update could cause you issues if you are attempting to make invalid changes (like changing the type from a Text type to a Choice type - I don't do any validation of your XML and rely completely on Microsoft's internal validation). The commands I created are detailed below.

1. gl-exportlistfield

Because I already had helper methods to get a field the code for this became basically just one line of text: File.WriteAllText(Params["outputfile"].Value, field.SchemaXml); The rest of the code is in a utility method which I've previously shown in other posts so won't show again here. The syntax of the command can be seen below:

C:\>stsadm -help gl-exportlistfield

stsadm -o gl-exportlistfield

Exports a list field (column) to a file.

Parameters:
        -url <list view URL>
        -fielddisplayname <field display name> / -fieldinternalname <field internal name>
        -outputfile <file to output field schema to>

Here's an example of how export the DivisionMulti field to a file:

stsadm –o gl-exportlistfield -url "http://intranet/SiteDirectory/SitesList/AllItems.aspx" -fieldinternalname DivisionMulti -outputfile "c:\divisionmulti.xml"

Running the above command will produce results similar to the following :

<Field Name="DivisionMulti" DisplayName="Division" Type="MultiChoice" ColName="ntext3" ID="{A96D82DA-601E-435B-9E8A-C086A853387B}" StaticName="DivisionMulti" SourceID="{93C04BF8-4E28-4194-A584-D6A97FCC87AE}">
 <CHOICES>
  <CHOICE>Information Technology</CHOICE>
  <CHOICE>Research &amp; Development</CHOICE>
  <CHOICE>Sales</CHOICE>
  <CHOICE>Finance</CHOICE>
 </CHOICES>
</Field> 

2. gl-importlistfield

Once we have our list field exported we can then import the field into another list either as is or with whatever manual modifications you may have made (just be careful that you know what you are doing - it's always better to make the modifications using the browser and then export those changes than it is to try and hack the XML directly).

By default when you run the import command the code will attempt to locate the "ID" attribute and replace it with a new GUID value - otherwise you may get errors stating that the field already exists (even if you've changed the "Name" attribute). If you don't want the code to do this then you can pass in the "-retainobjectidentity" parameter. Note that I'm not doing anything with the "SourceID" attribute as I couldn't detect any issues with keeping the value unchanged.

Like the export command there's really not much to the code - I get the SPList object, load up the XML, replace the ID attribute if needed, and then call AddFieldAsXml() and then call the ReorderField method (detailed at the end of this post in the 11/9/2007 update):

   1: public override int Run(string command, StringDictionary keyValues, out string output)
   2: {
   3:  output = string.Empty;
   4:  
   5:  InitParameters(keyValues);
   6:  
   7:  string url = Params["url"].Value;
   8:  string xml = File.ReadAllText(Params["inputfile"].Value);
   9:  SPAddFieldOptions fieldOptions = SPAddFieldOptions.Default;
  10:  if (Params["addfieldoptions"].UserTypedIn)
  11:   fieldOptions = (SPAddFieldOptions) Enum.Parse(typeof (SPAddFieldOptions), Params["addfieldoptions"].Value, true);
  12:  
  13:  XmlDocument xmlDoc = new XmlDocument();
  14:  xmlDoc.LoadXml(xml);
  15:  Guid id = new Guid(xmlDoc.DocumentElement.GetAttribute("ID"));
  16:  if (!Params["retainobjectidentity"].UserTypedIn)
  17:  {
  18:   id = Guid.NewGuid();
  19:   xmlDoc.DocumentElement.SetAttribute("ID", id.ToString());
  20:   xml = xmlDoc.OuterXml;
  21:  }
  22:  
  23:  SPList list = Utilities.GetListFromViewUrl(url);
  24:  list.Fields.AddFieldAsXml(xml, Params["addtodefaultview"].UserTypedIn, fieldOptions);
  25:  
  26:  SPField field = list.Fields[id];
  27:  if (Params["sortindex"].UserTypedIn)
  28:  {
  29:   int sortIndex = int.Parse(Params["sortindex"].Value);
  30:   ReorderField(list, field, sortIndex);
  31:  }
  32:  
  33:  return 1;
  34: }

The syntax of the command can be seen below:

stsadm -o gl-importlistfield

Imports a field (column) into a list.

Parameters:
        -url <list view URL>
        -inputfile <input file containing field schema information>
        [-addfieldoptions <default | addtodefaultcontenttype | addtonocontenttype | addtoallcontenttypes | addfieldinternalnamehint | addfieldtodefaultview | addfieldcheckdisplayname>
        [-addtodefaultview]
        [-retainobjectidentity]
        [-sortindex <field order index>]

Here's an example of how to import the field exported above into a new list (note that it assumes that the DivisionMulti field does not exist in the target list and that there's no field with a display name of "Division")and setting the sort order index to zero thus making it the first item in the list:

stsadm –o gl-importlistfield -url "http://teamsites/sitedirectory/siteslist/allitems.aspx" -inputfile "c:\divisionmulti.xml" -addfieldoptions addfieldcheckdisplayname -addtodefaultview -sortindex 0

3. gl-updatelistfield

When I first set out to create this command I originally thought it was going to be a real pain in the @$$ but then I discovered that the SchemaXml property had a setter and life got a whole lot easier. What I thought would end up being hundreds of lines of code to deal with all the possible changes ended up being one core line plus a few others just to get the data. I had to a bit of additional complexity though to deal with the ability to determine the field to edit via the name parameters and then to deal with the fact that I wanted to be able to edit the sort order without having to change anything else (thus making the inputfile optional):

   1: public override int Run(string command, StringDictionary keyValues, out string output)
   2: {
   3:  output = string.Empty;
   4:  
   5:  InitParameters(keyValues);
   6:  
   7:  string url = Params["url"].Value;
   8:  string fieldTitle = Params["fielddisplayname"].Value;
   9:  string fieldName = Params["fieldinternalname"].Value;
  10:  bool useTitle = Params["fielddisplayname"].UserTypedIn;
  11:  bool useName = Params["fieldinternalname"].UserTypedIn;
  12:  bool inputFileProvided = Params["inputfile"].UserTypedIn;
  13:  
  14:  
  15:  if (!inputFileProvided && !Params["sortindex"].UserTypedIn)
  16:  {
  17:   throw new SPSyntaxException("You must either specify an input file with changes or a sort index.");
  18:  }
  19:  if (!inputFileProvided && !useTitle && !useName)
  20:  {
  21:   throw new SPSyntaxException(
  22:    "You must specify either an input file with changes or the field name to update.");
  23:  }
  24:  
  25:  SPList list;
  26:  SPField field;
  27:  XmlDocument xmlDoc = new XmlDocument();
  28:  string xml = null;
  29:  
  30:  if (inputFileProvided)
  31:  {
  32:   xml = File.ReadAllText(Params["inputfile"].Value);
  33:   xmlDoc.LoadXml(xml);
  34:  }
  35:  
  36:  if (!inputFileProvided || useTitle || useName)
  37:  {
  38:   field = Utilities.GetField(url, fieldName, fieldTitle, useName, useTitle);
  39:   list = field.ParentList;
  40:  }
  41:  else
  42:  {
  43:   list = Utilities.GetListFromViewUrl(url);
  44:   string internalName = xmlDoc.DocumentElement.GetAttribute("Name");
  45:   field = list.Fields.GetFieldByInternalName(internalName);
  46:  }
  47:  
  48:  if (inputFileProvided)
  49:  {
  50:   field.SchemaXml = xml;
  51:   field.Update();
  52:  }
  53:  
  54:  if (Params["sortindex"].UserTypedIn)
  55:  {
  56:   int sortIndex = int.Parse(Params["sortindex"].Value);
  57:   ImportListField.ReorderField(list, field, sortIndex);
  58:  }
  59:  
  60:  return 1;
  61: }

The syntax of the command can be seen below:

C:\>stsadm -help gl-updatelistfield

stsadm -o gl-updatelistfield

Updates a field (column) using the provided input XML.  Use exportlistfield to get the existing schema and then modify the results (note that the 'Name' attribute of the Field node must not change unless the fieldinternalname or fielddisplayname is passed in as this attribute is what is used to determine which field to update).

Parameters:
        -url <list view URL>
        [-inputfile <input file containing the field schema information>]
        [-fielddisplayname <field display name> / -fieldinternalname <field internal name>]
        [-sortindex <field order index>]

Here's an example of how to update the field exported above with changes made to the resultant XML file (such as adding new choice elements) and setting the sort order index to zero thus making it the first item in the list:

stsadm –o gl-updatelistfield -url "http://intranet/sitedirectory/siteslist/allitems.aspx" -inputfile "c:\divisionmulti.xml" -sortindex 0

I thought about using the "ID" attribute within the XML to locate the field to update but in the end I decided that someone may want to use this to change the ID for whatever reason and they're less likely to want to change the internal name.

 

Update 11/9/2007: I've modified the gl-updatelistfield and gl-importlistfield commands so that they now support the passing in of a sortindex parameter which effectively does what it says - it changes the field order. I've updated the content above to reflect the changes. I wish that this were one of those easy things to implement but it turns out that it was a real pain - fortunately I found a post by Michael Ekegren which discusses how to do this using the ProcessBatchData method of the SPWeb object. I've included the code that makes this work below:

   1: /// <summary>
   2: /// Reorders the field.
   3: /// </summary>
   4: /// <param name="list">The list.</param>
   5: /// <param name="field">The field.</param>
   6: /// <param name="sortIndex">The sort index.</param>
   7: internal static void ReorderField(SPList list, SPField field, int sortIndex)
   8: {
   9:  if (field.Reorderable)
  10:  {
  11:   List<SPField> fields = new List<SPField>();
  12:   int count = 0;
  13:   bool added = false;
  14:   // First add the reorderable fields
  15:   for (int i = 0; i < list.Fields.Count; i++)
  16:   {
  17:    if (list.Fields[i].Reorderable)
  18:    {
  19:     if (count == sortIndex)
  20:     {
  21:      added = true;
  22:      fields.Add(field);
  23:      count++;
  24:     }
  25:  
  26:     if (list.Fields[i].Id == field.Id)
  27:      continue;
  28:  
  29:     fields.Add(list.Fields[i]);
  30:     count++;
  31:    }
  32:   }
  33:   if (!added)
  34:    fields.Add(field);
  35:  
  36:   // Now add the non-reorderable fields
  37:   for (int i = 0; i < list.Fields.Count; i++)
  38:   {
  39:    if (!list.Fields[i].Reorderable)
  40:    {
  41:     fields.Add(list.Fields[i]);
  42:    }
  43:   }
  44:  
  45:   StringBuilder sb = new StringBuilder();
  46:  
  47:   XmlTextWriter xmlWriter = new XmlTextWriter(new StringWriter(sb));
  48:   xmlWriter.Formatting = Formatting.Indented;
  49:  
  50:   xmlWriter.WriteStartElement("Fields");
  51:  
  52:   for (int i = 0; i < fields.Count; i++)
  53:   {
  54:    xmlWriter.WriteStartElement("Field");
  55:    xmlWriter.WriteAttributeString("Name", fields[i].InternalName);
  56:    xmlWriter.WriteEndElement();
  57:   }
  58:  
  59:   xmlWriter.WriteEndElement();
  60:   xmlWriter.Flush();
  61:  
  62:   using (SPWeb web = list.ParentWeb)
  63:   {
  64:    ReorderFields(web, list, sb.ToString());
  65:   }
  66:  }
  67: }
  68:  
  69: /// <summary>
  70: /// This function reorders the fields in the specified list programmatically as specified by the xmlFieldsOrdered parameter
  71: /// </summary>
  72: /// <param name="web">The SPWeb object containing the list</param>
  73: /// <param name="list">The SPList object to update</param>
  74: /// <param name="xmlFieldsOrdered">A string in XML-format specifying the field order by the location within a xml-tree</param>
  75: private static void ReorderFields(SPWeb web, SPList list, string xmlFieldsOrdered)
  76: {
  77:  try
  78:  {
  79:   string fpRPCMethod = @"<?xml version=""1.0"" encoding=""UTF-8""?>
  80:    <Method ID=""0,REORDERFIELDS"">
  81:    <SetList Scope=""Request"">{0}</SetList>
  82:    <SetVar Name=""Cmd"">REORDERFIELDS</SetVar>
  83:    <SetVar Name=""ReorderedFields"">{1}</SetVar>
  84:    <SetVar Name=""owshiddenversion"">{2}</SetVar>
  85:    </Method>";
  86:  
  87:   // relookup list version in order to be able to update it
  88:   list = web.Lists[list.ID];
  89:  
  90:   int currentVersion = list.Version;
  91:  
  92:   string version = currentVersion.ToString();
  93:   string RpcCall = string.Format(fpRPCMethod, list.ID, SPHttpUtility.HtmlEncode(xmlFieldsOrdered), version);
  94:  
  95:   web.AllowUnsafeUpdates = true;
  96:  
  97:   web.ProcessBatchData(RpcCall);
  98:  }
  99:  catch (System.Net.WebException err)
 100:  {
 101:   Console.WriteLine("WARNING:" + err.Message);
 102:  }
 103: }

7 comments:

GellMaster said...

I kept receiving an error that the list had changed or updated when I was trying to get it to work. For some reason your code does the trick.

Thank you very much for posting this solution. It worked perfectly.

Kevin Bluer said...

Excellent stuff...worked a treat!

Robi said...

Great code, works very well :)

Thanks.

Paul Turner said...

You need to update all references in lookup fields (fields of type SPFieldType.Lookup) to point to new lists.

You also need to copy those lists across at the same time or the field will be broken. The attributes can be set in the SchemaXml (SourceID, List, WebId).

Other than that... great post.

David said...

This tool just saved me a ton of work. See my posting about my stupid mistake and how I was able to fix it in 5 minutes.

http://tipsforsharepoint.blogspot.com/2009/02/recreating-column-in-sharepoints-user.html

Thanks for your great contribution to the SharePoint community. Keep up the good work.

Emerson said...

Its possible import data to list, from .txt file...???
Emerson - Brasil

Gary Lapointe said...

Not without writing some code (powershell or something).