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:

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.

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:

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

Fixing blank lookup columns in SharePoint using PowerShell

Here is the scenario: You have a site containing a list used by a lookup column from another list in that same site. When you save both lists as list templates and create them in another site, the lookup column no longer works and just shows blank options.

You can use PowerShell to fix this by first running this function in the SharePoint 2010 Management Shell:

Function Fix-LookupColumn ($webURL, $listName, $columnName, $lookupListName)
{
    #Get web, list and column objects
    $web = Get-SPWeb $webURL
    $list = $web.Lists[$listName]
    $column = $list.Fields[$columnName]
    $lookupList = $web.Lists[$lookupListName]
   
    #Change schema XML on the lookup column
    $column.SchemaXml = $column.SchemaXml.Replace($column.LookupWebId.ToString(), $web.ID.ToString())
    $column.SchemaXml = $column.SchemaXml.Replace($column.LookupList.ToString(), $lookupList.ID.ToString())
    $column.Update()
   
    #Write confirmation to console and dispose of web object
    write-host "Column" $column.Title "in list" $list.Title "updated to lookup list" $lookupList.Title "in site" $web.Url
    $web.Dispose()
}

…followed by this command to call the function for the particular list and column that you wish to fix:

Fix-LookupColumn -webURL http://SiteURL -listName "Name of list containing the lookup column" -columnName "Lookup column name" -lookupListName "List used by the lookup column"

For example, the list used in the image at the top of this article is called “News” and contains two lookup columns called “Countries” and “Departments”. These columns look up values from two lists on the same site called “Country List” and “Department List”. Therefore, to fix the lookup columns in the “News” list on site http://portal, you would type the following commands:

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Countries" -lookupListName "Country List"

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Departments" -lookupListName "Department List"

The lookup columns now successfully wire up to their respective lists in the site:

But wait, there’s more. There could be a scenario where you have used a list on the root site of your site collection and created a lookup column as a site column for all lists to use throughout the site collection. This method allows you to centralise the lists used for lookup columns at the root site so that they can be consumed by all lists in that site collection.

However, the dreaded blank lookup columns issue will also occur if you save a list that uses a lookup site column from one site collection and create it on another site collection using the list template – even though an identical version of the lookup site column with the same internal name and ID has been created on both site collections.

To fix this issue, I simply ran a small script to push changes from the custom lookup columns on the root site to all lists using those columns. The result of this is that the script updates the custom lookup columns used in lists to match the schema published from the root site:

#Get the root site of the site collection
$rootWeb = Get-SPWeb http://portal

#Get all lookup columns from the group Custom Site Columns
[array]$siteColumns = $rootWeb.Fields | where { ($_.Type -eq "Lookup") -and ($_.Group -eq "Custom Site Columns") }

#Get each column and push changes to lists
$siteColumns | ForEach-Object {
    $siteColumn = $rootWeb.Fields[$_]
    $siteColumn.PushChangesToLists = $true
    $siteColumn.Update()
    write-host "Change site column:" $_
}

#Dispose of web object
$rootWeb.Dispose()

Note in the script that I get all lookup columns from a site column group called “Custom Site Columns”. You will need to change this to the group name(s) used for the custom lookup columns in your site.

Delete unused workflow status columns in SharePoint list views using PowerShell

SharePoint 2010 seems to be better than 2007 for removing workflow status columns from list views after a workflow has been removed or updated, but it does still leave them behind every now and again.

I have been unable to trace exactly why this happens, but rather than waste time looking, I decided to write a quick PowerShell script to remove these columns as they cannot be deleted from the UI.

This script works as a function – meaning that there are two parts to running it in the SharePoint 2010 Management Shell. First, you have to run the script shown below:

function Delete-WorkflowColumn ($webURL, $listName, $columnName)
{
    #Setup variables from the user input
    $web = Get-SPWeb $webURL
    $list = $web.Lists[$listName]
    $column = $list.Fields[$columnName]
   
    #Make sure the column is not hidden or read only
    $column.Hidden = $false
    $column.ReadOnlyField = $false
    $column.Update()

    #Delete column and dispose of the web object
    $list.Fields.Delete($column)
    write-host "Deleted column"
    $web.Dispose()
}

Once the function has been run, you can call it to actually remove a column with the following command:

Delete-WorkflowColumn -webURL http://portal -listName "Documents" -columnName "Approval Workflow (Previous Version:1/17/2011 8:45:01 PM)"

The example above deletes the “Approval Workflow (Previous Version:1/17/2011 8:45:01 PM)” column shown in the screenshot above from all list views of a document library called “Documents” in the site http://portal.

Whilst I have focused in this article on removing columns generated by unused workflows, you can use exactly the same script to remove columns from any list or document library.

Running a SharePoint PowerShell script from Task Scheduler

There are often times where you may want to run a PowerShell script for SharePoint on a schedule – for example, a backup routine, report, or script to automate a process – just like the good old days of running a batch file in Task Scheduler. Well, things aren’t really that different these days either – yes, the scripting language may have changed, but Task Scheduler can still be used as before.

In this example, I am going to script the backup of a site collection and run it every night at 2:00AM. First, the script:

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
Backup-SPSite -Identity http://intranet.pacdomain2.local -Path C:\Install\Intranet.bak –Force

Although the script is quite simple, note the Add-PSSnapin command on the first line. We need this because we are going to run the script using the standard Windows PowerShell console application and not the SharePoint 2010 Management Shell that has the snap-in already loaded. The –erroraction switch is there to prevent any errors should the snap-in already be loaded through a profile on the server.

Save your script to a local drive on the server as a PS1 file.

Next, load Task Scheduler from Start > All Programs > Accessories > System Tools. The version I am using here ships with Windows Server 2008 R2, but the concepts should be the same on earlier releases.

To create a new scheduled task, click Create Task from the right-hand “Actions” panel. At a minimum, you should fill out the following information:

General Tab

• Name of the task – e.g., Intranet Backup. A description is also useful, but not mandatory
• Ensure the user account running the task has the following permissions:
• Log on as a batch job rights in the User Rights Assignment section of the server Local Security Policy. These rights could be set on the server itself or by Group Policy if managed from Active Directory – see http://technet.microsoft.com/en-us/library/dd277404.aspx for details
• Permissions to use PowerShell on the SharePoint farm. You can add these for a new user by running Add-SPShellAdmin –UserName domain\username in the SharePoint 2010 Management Shell console as a current administrator – see http://technet.microsoft.com/en-us/library/ff607596.aspx for details
• Permissions to be able to perform the script operation in SharePoint. For example, for the site collection backup in this example, the account used in the scheduled task required:
• dbo access in SQL to the database hosting the site collection
• Membership of the local Administrators group on the server – you may be able to experiment with fewer permissions than this, but this was required on my development server, which is also a domain controller
• Select Run whether the user is logged in or not and tick the Do not store password checkbox
• For certain operations, you may also need to enable the Run with highest privileges option. As this should only be enabled when necessary, I would recommend testing the script from Task Scheduler with the option disabled and only enable if the script does not work without it

Triggers Tab

Click New and add your schedule, as required. In the example below I am going for daily at 2:00AM

Actions Tab

Click New. Ensure the action is set to Start a program and type Powershell.exe in the “Program/Script” box. In the “Add arguments (optional)” box, type &'c:\scripts\script.ps1' replacing the path and name with the details for your script. Wrapping the file path and name in single quotation marks allows you to specify spaces in the text.

When you click OK to confirm task creation, you will be prompted to enter the password for the account you selected to run the script. Enter the password and click OK.

You should now see your script in the Task Scheduler Library (if not, click Refresh in the right-hand panel). To test the script, highlight it in the console and click Run from the right-hand panel.

Check that the changes required are performed (for this example it was checking to ensure the backup file was being created in the folder when the task ran) and leave it to run on the schedule. Don’t forget to perform regular checks in the future to test the script continues to run as expected.