Thursday, 27 January 2011

Export and import/create site columns in SharePoint using PowerShell

Even though we now have the new Content Type Hub feature introduced in SharePoint Server 2010 for propagating columns and content types across all site collections in the farm, it can still be useful to export site columns from one site collection and import them into another. For example:

  • When using SharePoint Foundation, where the Content Type Hub feature doesn’t exist
  • Deploying a production environment with site columns created on a separate development or staging farm
  • Keeping a record of the site columns created in a site collection for documentation or governance purposes

With PowerShell, we can use the column schema from a source site collection in SharePoint to generate an XML file, which can then be used to import the site columns into another site collection.

For this example I have created the following site columns under the “Custom Columns” group in my development site collection:

image

The PowerShell script below will export these site columns to an XML file called Script-SiteColumns.xml in the C:\Install folder. Note that the script looks at the group called “Custom Columns” to ensure columns created by a standard SharePoint installation are not included in the export. You will need to ensure that appropriate custom group name(s) are specified in your script when exporting:

$sourceWeb = Get-SPWeb http://portal
$xmlFilePath = "C:\Install\Script-SiteColumns.xml"

#Create Export Files
New-Item $xmlFilePath -type file -force

#Export Site Columns to XML file
Add-Content $xmlFilePath "<?xml version=`"1.0`" encoding=`"utf-8`"?>"
Add-Content $xmlFilePath "`n<Fields>"
$sourceWeb.Fields | ForEach-Object {
    if ($_.Group -eq "Custom Columns") {
        Add-Content $xmlFilePath $_.SchemaXml
    }
}
Add-Content $xmlFilePath "</Fields>"

$sourceWeb.Dispose()

This script will generate an XML file similar to the one shown below.

image

Once you have the XML file, it can be used to import columns into another site collection by using the script below. The first part of the script gets the destination Web URL and exported XML file:

$destWeb = Get-SPWeb http://portal/sites/migrationtest
$installPath = "C:\Install"

#Get exported XML file
$fieldsXML = [xml](Get-Content($installPath + "\Script-SiteColumns.xml"))

The final part of the script cycles through each field specified in the XML file, the properties associated with each column type, and then creates the column in the destination site. Whilst I have included the majority of properties associated with each type of site column (e.g., MaxLength, EnforceUniqueValues, Sortable, etc.), there may still be some properties you need to add yourself as I haven’t tested the script with every column type available in SharePoint:

$fieldsXML.Fields.Field | ForEach-Object {
   
    #Configure core properties belonging to all column types
    $fieldXML = '<Field Type="' + $_.Type + '"
    Name="' + $_.Name + '"
    ID="' + $_.ID + '"
    Description="' + $_.Description + '"
    DisplayName="' + $_.DisplayName + '"
    StaticName="' + $_.StaticName + '"
    Group="' + $_.Group + '"
    Hidden="' + $_.Hidden + '"
    Required="' + $_.Required + '"
    Sealed="' + $_.Sealed + '"'
   
    #Configure optional properties belonging to specific column types – you may need to add some extra properties here if present in your XML file
    if ($_.ShowInDisplayForm) { $fieldXML = $fieldXML + "`n" + 'ShowInDisplayForm="' + $_.ShowInDisplayForm + '"'}
    if ($_.ShowInEditForm) { $fieldXML = $fieldXML + "`n" + 'ShowInEditForm="' + $_.ShowInEditForm + '"'}
    if ($_.ShowInListSettings) { $fieldXML = $fieldXML + "`n" + 'ShowInListSettings="' + $_.ShowInListSettings + '"'}
    if ($_.ShowInNewForm) { $fieldXML = $fieldXML + "`n" + 'ShowInNewForm="' + $_.ShowInNewForm + '"'}
       
    if ($_.EnforceUniqueValues) { $fieldXML = $fieldXML + "`n" + 'EnforceUniqueValues="' + $_.EnforceUniqueValues + '"'}
    if ($_.Indexed) { $fieldXML = $fieldXML + "`n" + 'Indexed="' + $_.Indexed + '"'}
    if ($_.Format) { $fieldXML = $fieldXML + "`n" + 'Format="' + $_.Format + '"'}
    if ($_.MaxLength) { $fieldXML = $fieldXML + "`n" + 'MaxLength="' + $_.MaxLength + '"' }
    if ($_.FillInChoice) { $fieldXML = $fieldXML + "`n" + 'FillInChoice="' + $_.FillInChoice + '"' }
    if ($_.NumLines) { $fieldXML = $fieldXML + "`n" + 'NumLines="' + $_.NumLines + '"' }
    if ($_.RichText) { $fieldXML = $fieldXML + "`n" + 'RichText="' + $_.RichText + '"' }
    if ($_.RichTextMode) { $fieldXML = $fieldXML + "`n" + 'RichTextMode="' + $_.RichTextMode + '"' }
    if ($_.IsolateStyles) { $fieldXML = $fieldXML + "`n" + 'IsolateStyles="' + $_.IsolateStyles + '"' }
    if ($_.AppendOnly) { $fieldXML = $fieldXML + "`n" + 'AppendOnly="' + $_.AppendOnly + '"' }
    if ($_.Sortable) { $fieldXML = $fieldXML + "`n" + 'Sortable="' + $_.Sortable + '"' }
    if ($_.RestrictedMode) { $fieldXML = $fieldXML + "`n" + 'RestrictedMode="' + $_.RestrictedMode + '"' }
    if ($_.UnlimitedLengthInDocumentLibrary) { $fieldXML = $fieldXML + "`n" + 'UnlimitedLengthInDocumentLibrary="' + $_.UnlimitedLengthInDocumentLibrary + '"' }
    if ($_.CanToggleHidden) { $fieldXML = $fieldXML + "`n" + 'CanToggleHidden="' + $_.CanToggleHidden + '"' }
    if ($_.List) { $fieldXML = $fieldXML + "`n" + 'List="' + $_.List + '"' }
    if ($_.ShowField) { $fieldXML = $fieldXML + "`n" + 'ShowField="' + $_.ShowField + '"' }
    if ($_.UserSelectionMode) { $fieldXML = $fieldXML + "`n" + 'UserSelectionMode="' + $_.UserSelectionMode + '"' }
    if ($_.UserSelectionScope) { $fieldXML = $fieldXML + "`n" + 'UserSelectionScope="' + $_.UserSelectionScope + '"' }
    if ($_.BaseType) { $fieldXML = $fieldXML + "`n" + 'BaseType="' + $_.BaseType + '"' }
    if ($_.Mult) { $fieldXML = $fieldXML + "`n" + 'Mult="' + $_.Mult + '"' }
    if ($_.ReadOnly) { $fieldXML = $fieldXML + "`n" + 'ReadOnly="' + $_.ReadOnly + '"' }
    if ($_.FieldRef) { $fieldXML = $fieldXML + "`n" + 'FieldRef="' + $_.FieldRef + '"' }   

    $fieldXML = $fieldXML + ">"
   
    #Create choices if choice column
    if ($_.Type -eq "Choice") {
        $fieldXML = $fieldXML + "`n<CHOICES>"
        $_.Choices.Choice | ForEach-Object {
           $fieldXML = $fieldXML + "`n<CHOICE>" + $_ + "</CHOICE>"
        }
        $fieldXML = $fieldXML + "`n</CHOICES>"
    }
   
    #Set Default value, if specified 
    if ($_.Default) { $fieldXML = $fieldXML + "`n<Default>" + $_.Default + "</Default>" }
   
    #End XML tag specified for this field
    $fieldXML = $fieldXML + "</Field>"
   
    #Create column on the site
    $destWeb.Fields.AddFieldAsXml($fieldXML.Replace("&","&amp;"))
    write-host "Created site column" $_.DisplayName "on" $destWeb.Url
   
    $destWeb.Dispose()
}

You may get some warning messages during processing of the import script if there are columns contained in the exported schema XML file that are already present in the destination site collection. Unless there are conflicting issues with your imported columns during testing, these warnings should be safe to ignore (or the columns removed from the exported XML if not required) as the script will not overwrite what is present in the site collection already. Once the script has been run, the custom site columns exported from the original site collection will be present in the new site collection:

image

In my next article, I cover exporting and importing content types from one site collection to another using a similar process…

48 comments:

  1. Phil, luv your Powershell posts. I have a brain teaser. I need to update a Site Column in SharePoint 2007. It seems the consultant has left out the "List = UserInfo" property when it was deployed thus users get the dreaded "Datasheet Error 0x80070057" when trying to lookup a username. Is it possible to do this in Powershell? The site column is in the ContentDB it seems.

    Bismark

    ReplyDelete
  2. Hi Phil, upon running your import to re-add the lists I get Field type is not installed properly. Go to the list settings page to delete this field.
    when I browse the site settings->site columns list.. help.

    ReplyDelete
  3. Hi Phil,
    this is the first time in my life, I download a script and it runs without any error AND does the expected thing. By the way I'm 46 years and started to study computer sience about 20 years ago.
    Great Work!
    Michael

    ReplyDelete
  4. Michael - first time in your life! Wow! I'm glad it worked.... :-)

    ReplyDelete
  5. Hello,
    I tried the scripts, the first one went fine, i got all the columns however i'm having the same issue as "Muse" {Field type is not installed properly. Go to the list settings page to delete this field. } can you help please
    Thx

    ReplyDelete
  6. I'll reply to my own question. I had created some workflows before and i had some columns in my site. Thus we generating the XML file, there was a column with <Field Name="Type_x0020.... i deleted it and ran the script again and "Voila" the only problem is that as the script doesn't do any override i had to delete the site recreate it and re-run the scripts

    ReplyDelete
  7. Hello Phil,
    I got same issue with Muse... "Field type is not installed properly. Go to the list settings page to delete this field."
    Is there any proper way to fix it safely?

    ReplyDelete
  8. Phil - We are working with Microsoft's EPM (Enterprise Project Management)which uses Sharepoint as a repository for project documents. EPM has a site collection with a project site template which is instantiated for each new project. Within the site template are lists for tracking Risks, Issues, Change Requests etc. We want to add some columns to the Risks list and have these propagate to all existing project sites, not just new sites created going forward. Do you have any tips you can share on how to achieve this? Thanks.

    ReplyDelete
  9. If the lists work from a site content type then you can add the columns there - if not, you could use PowerShell to walk through each site, connect to the Risks list and add the columns required

    ReplyDelete
  10. Wonderful blog! I found it while searching on Yahoo News. Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Many thanks.sbobet

    ReplyDelete
  11. Phil
    Useful blog thanks, wondering if this script works when site columns are referencing managed metadata?
    Thanks

    ReplyDelete
  12. Thanks Phil! Your blog was easy to follow and the script worked perfectly the 1st time!

    ReplyDelete
    Replies
    1. Great! Thanks for the feedback, Peter

      Delete
  13. Phil, thanks for all the scripts that you put here. They are really useful for someone starting with SP.

    This script works fine, but when I try to add or create a new calculated column using AddFieldAsXML, it never works. I add field properties, formula and fieldrefs in Schema XML, and after running script, field is added but formula is not. Even I tried to do this field.formula = and field.Update()...it doesn't work.

    Do you have any other idea how we can add calculated field using powershell ?

    Thanks a lot.

    Ashish,
    Germany

    ReplyDelete
    Replies
    1. I now could solve this problem. So for calculated field, dont add any fieldrefs or formula in XML Schema. Just use AddFieldAsXml first to create this field and later on after this field is added, use field.formula = "formula here with brackets in column name" and field.update(), and it works.

      Thanks anyways Phil and keep Posting.
      Ashish

      Delete
  14. Hi Phil,

    Thanks for this script. It worked great for me. The only small issue I had is with the assignment of term/term-set for a site column of type Managed Metadata type. The site column of type Managed Metadata type is imported properly. It shows me the whole term store to select any term. But it doesn't pre-assign the particular term which was assigned from the place where I imported the column.

    Do you know if I can achieve this assignment of term automatically while importing.

    Thanks
    David

    ReplyDelete
  15. Hi Phil,
    I need to add list=userinfo in my schema xml..Is it possible to do with powershell.

    ReplyDelete
  16. Great scripts, thanks!

    Small issue you might like to fix; the script doesn't actually force the file to be written in UTF-8, which caused me troubles when choice options used characters with umlauts.

    Easy fix, just add
    " -Encoding UTF8 "
    after each Add-Content statement.

    Regards,
    Jan Steenbeek

    ReplyDelete
  17. Oh, also, the builder for the column doesn't recognice MultiChoice columns.

    > if ($_.Type -eq "Choice") {

    Should be
    > if (($_.Type -eq "Choice") -or ($_.Type -eq "MultiChoice")) {

    Regards, Jan

    ReplyDelete
  18. Excellent script. Just the thing I was looking for. Thanks very much for sharing your knowledge.

    ReplyDelete
  19. Thanks for the script.
    Did you ever encounter the following problem:

    Exception calling "AddFieldAsXml" with "1" argument(s): ""

    Its just this one column which have Problems. It's a simple Text column. Do you have an idea why this error came up?

    regards, nadine

    ReplyDelete
    Replies
    1. Old thread... but I received this error when attempting the operation with not enough privilege

      Delete
  20. Thanks for the script, I modified the second version and managed to get it down to very few lines.

    Since you're dealing with an Xml object anyways, why not grab the OuterXml and use that?

    $fieldsXML.Fields.Field | ForEach-Object {
    $destWeb.Fields.AddFieldAsXml($_.OuterXml.Replace("&","&"))
    write-host "Created site column" $_.DisplayName "on" $destWeb.Url
    $destWeb.Dispose()
    }

    Saved me having to build up my own xml string which was prone to me missing things.

    Cheers,

    Kyle

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Thanks Phil, this was very helpful!

    Doug
    http://www.Codesigned.com

    ReplyDelete
  23. Simply Superb! Thanks a lot

    ReplyDelete
  24. If the column contains Chinese characters, it will show '????'.

    How can I fixed this issue?

    Thanks a lot.

    ReplyDelete
  25. This is cool - unfortunately realized after I tried it that it may only work within a farm? I was hoping to export content types from one farm to another. Am I doing something wrong, or is this farm-specific?

    Thanks!
    Josh

    ReplyDelete
  26. Hi Phil!! Thanks for your blog!! It is amazing and extremely helpful!!
    Just one thing i would like to ask you for: what can i add to this code to include umlauts characters (or any other characters like from french language)? It seems that this code crashes when i got choices with special characters...
    Appreciate your help on this! Thanks!
    Kamil

    ReplyDelete
  27. Please note that there is a potential to break the ability to create site templates after using the import script (only tested on 2013). The script assumes there will always be a hidden attribute value:
    Hidden="' + $_.Hidden + '"
    Hidden will not exist unless you explicitly set it to be true. As a result you could end up with something like hidden="". The column will still happily import however if you try create a site template it will fail reporting a schema validation error as it must have a value of either True or False. To fix it you need to evaluate the existence of hidden.

    ReplyDelete
    Replies
    1. I can confirm this breaks the ability on creating site templates on 2010 as well..

      Delete
    2. also for "sealed"!

      Delete
  28. Thanks Phil ! Your script has saved me hours of boring

    ReplyDelete
  29. You are the Best Phil. It rarely happens with me that script runs successfully in first time attempt. Bless you!

    ReplyDelete
  30. Caution: If you have a choice column with a large number of choice options, this will cause the script to crap out and return . I figured this out through a bit of trial and error. But this worked for 99% of my site columns. Great script and thanks again!

    ReplyDelete
  31. That was supposed to read it will return "".

    Just a warning.

    ReplyDelete
  32. Dang it. empty empty in a set of "<>"

    ReplyDelete
  33. Attractive section of content. I just stumbled upon your site and in accession capital to assert that I get in fact enjoyed account your blog posts.
    sharepoint developer training

    ReplyDelete
  34. This comment has been removed by the author.

    ReplyDelete
  35. Backing up data in advance is really important
    Or you would have to use
    data recovery software for help, which is really troublesome.

    ReplyDelete
  36. شركة تخزين اثاث بالرياض

    الاول شركة تخزين اثاث بالرياض
    توفر افضل المستودعات لتخزين الاثاث والعفش التى تتوفر بها كل عوامل الامن للحفاظ على الاثاث وتقوم بتغليف الاثاث قبل تخزينه للمحافظة عليها فالاول افضل شركات تخزين العفش بالرياض ونوفر سيارات لنقل الاثاث من اى مكان داخل المملكة .
    افضل شركة نقل عفش بينبع -
    شركة نقل عفش بجدة
    بالاضافة الى خدمة نقل اثاث بالرياض فنحن نمتلك اكبر اسطول نقل بالرياض يوفر لك افضل خدمة نقل اثاث بالرياض
    بالاضافة الى ان اسعارها في متناول الجميع فهى فالاول شركة نقل عفش بالرياض رخصية بالمقارنة مع باقي الشركات بالرغم من جودة عملها ودقة المواعيد فهى تقوم بتغليف العفش للحفاظ عليه اثناء النقل وتقوم باستخدام سيارات مغطاه مخصصة لنقل العفش للحفاظ عليه من اضرار الشمس والامطار والاتربة فالاول افضل شركة نقل اثاث بالرياض وتغطى كافة مدن المملكة
    افضل شركة تنظيف منازل بالقطيف

    وان كنت بحاجة الى شركة شراء اثاثك القديم المستعمل فباتصالك بنا تحصل على افضل شركة شراء اثاث مستعمل بالرياض والخرج

    ReplyDelete
  37. I'm glad to see this post. By the way, you may be interested in replica ray bans.

    ReplyDelete