SharePoint vs Powershell – Moving List Items between folders

Hi,

Welcome to my first post…

I’ve been wanting to do this for some time now… but as with most people in the industry, I’ve spent years honing the art of procrastination…

In all seriousness, I always saw my first post introducing myself and giving my 5c on the whole SharePoint revolution. I’ve drafted that post, and perhaps one day I’ll release… In the interim, find below a quick blog on a PowerShell and how to use it to perform a bulk administrative task in SharePoint. This was written by myself for my partner (who I’ve managed to convert to a SharePoint guru god bless her soul)…

Now, let me say – I am no PowerShell guru – and could have quite possibly broken every rule and/or best practice in the book… I’m certainly open for constructive criticism – so please, if I could have done something efficiently and/or elegantly (I’m a huge fan of elegance) please advise!

Requirements

So – In a nutshell, the requirement was to move all items contained in the root folder of a SharePoint list to a folder…

Apparently, this requirement was driven by a list with tens of thousands of items existing in the root folder. These items had accumulated from on-going automated scripts and processes. The customer required a solution that could be run as a scheduled task every night to clear out the items from the root folder keeping the item count per container within specified Microsoft guidelines (Max of 2000 Items per container where a container is at its lowest form, a folder. See here for more info)

Having played a bit with powershell, I thought this a prime opportunity to put my new found skills (limited as they are) to actual use. (Nothing like a bit of incentive right?)

A bit of pseudo code to help illustrate my general approach below:

1. Get a reference to the root folder of the list


2. Check to see if the root folder contains the archive folder for current day




1. If Not: Create the folder and return the path


2. If So: Return the Path


3. Query the list and return a list of items in the root folder
   
4. Iterate through the returned collection and move items to the archive folder

Simple huh?

To help ensure we don’t run into the same problem, I decided to have the script create a new archive folder to move the items into based on the date it was being run. To ensure we don’t clog up the root folder with a whole heap of archive folders I also made the call to put the daily generated folders into a single archive folder.

So the pseudo code evolves to:

1. Get a reference to the root folder of the list


2. Check to see if the root folder contains the archive root folder




1. If Not: Create the folder and return the path


2. If So: Return the Path


3. Check to see if the root archive folder contains the daily archive folder




1. If Not: Create the folder and return the path


2. If So: Return the Path


4. Query the list and return a list of items in the root folder


5. Iterate through the returned collection and move items to the archive folder

Now the rules have been established, let’s dive into it.

Loading the SharePoint Assembly

One of the most powerful features PowerShell has to offer is the integration with the .NET Framework. The PowerShell environment allows you to directly instantiate and manipulate .NET Objects from the command line by accessing methods, properties and members without the need of a complier.

In much the same way as you need to add a reference to your Visual Studio Project, In order to access the WSS .NET API you need to load the assembly. This can be done as below.

function LoadWSSAssembly
{
write-host "Loading WSS Assembly..."  
[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") 
write-host "Done..."  
}

Note I will be utilizing a couple of basic concepts of PowerShell (functions, piping etc) that I’m not going to go into any detail about. If you need help on these, try here.

Getting Reference to the List

As we all know, in order to get a reference to an SPList object, we first need to reference to the web it lives in and the site that hosts said web.

Given we are not working in the IIS / App Pool Context, we don’t have access to any sort of SPContext. As a result, we need to instantiate our own references. Once these have been established, we can get reference to the list.

[Microsoft.SharePoint.SPSite] $Site = New-Object Microsoft.SharePoint.SPSite($WebURL);
[Microsoft.SharePoint.SPWeb] $Web = $Site.OpenWeb();
[Microsoft.SharePoint.SPList] $List = $Web.Lists[$ListDisplayName];

Throughout the examples I’ve made reference to variables such as $WebURL in the above code snippet. I've hoped to modulerize functionality as much as possible in an attempt to abstract the components of this script to a point where they are interpretable and hopefully, can be re-used. Where I have failed in this objective, the full script located at the end of the article should help you understand where and how they are used.

Now, couple of things to quickly mention here. I’ve typed my PowerShell variables. While this is not mandatory, coming from a strongly typed language, I consider it good practice. Secondly, the method of retrieving reference to the list on the third line is not best practice. The SPWeb.Lists[ListDisplayName] will load all lists including metadata into the collection before searching through them for the one containing the display name you’ve passed. I’ll blog about this another time.

Checking for the Archive Folders

To gain reference to the archive folder, I’ve implemented a couple of functions. The first entry point looks like this:

[Microsoft.SharePoint.SPFolder] $FolderToMoveTo = getArchiveFolder $List;

That one’s pretty self-explanatory. The ‘getArchiveFolder’ function is outlined below.

function getArchiveFolder([Microsoft.SharePoint.SPList] $List)        
{        
    #Get Reference to the Archive Root Folder:        
    [Microsoft.SharePoint.SPFolder] $archiveRoot = getFolder $List.RootFolder $ArchiveFolderName;        
            
    #Get Reference to the daily archive folder:        
    $nowTimeInString = [System.DateTime]::Now.ToString("yyyyMMdd");        
    [Microsoft.SharePoint.SPFolder] $todaysArchiveFolder =  getFolder $archiveRoot $nowTimeInString;        
    return $todaysArchiveFolder;        
}

As detailed, the function makes two calls to the ‘getFolder’ function. The first one gets reference to the archive root folder, the second to the daily archive folder.
The ‘getFolder’ function looks like:

function getFolder([Microsoft.SharePoint.SPFolder] $Folder, [System.String] $folderName) 
{
    write-host "Searching for folder '" $folderName "' in SPFolder located at " $Folder.Url "...";
    if (!(folderExists $Folder $folderName))
        {
            write-host "Folder Not Found - Creating a new one...";
            $folderItem = $list.Items.Add($Folder.ServerRelativeUrl, [Microsoft.SharePoint.SPFileSystemObjectType]::Folder,$folderName);
            $folderItem.SystemUpdate();
            $list.Update();
            #Need to get a fresh reference of the folder to ensure we can see the new one just created:
            $Folder = $Folder.ParentWeb.GetFolder($Folder); 
        }
    return $Folder.SubFolders  where {$_.Name -eq $FolderName};
}

So as per the pseudo code, the function first checks to see if the folder exists and where it doesn’t it will create it. The ‘folderExists’ function is a simple one liner:

function folderExists([Microsoft.SharePoint.SPFolder] $Folder, [string]$FolderName)
{
    return !(($Folder.SubFolders  where {$_.Name -eq $FolderName}) -eq $null);
}

There are two things worth nothing here:

1. The acquisition of a fresh reference to the root folder variable ($Folder) after the sub folder has been created. This is required (SharePoint API Quirk)


2. The return statements showcasing some of the funky piping and filtering offered by PowerShell.
   
   (really cool stuff!! But way outside the scope of this article … maybe another time? … if your familiar with linq/lamba statements you may find the syntax somewhat familiar )

The Guts

We’re nearly there… After gaining reference to the folder we want to move to (and creating them if necessary) we can hit the code snippet where we do the actual work:

$ItemMoveCount=0;
    
    $Query = New-Object Microsoft.SharePoint.SPQuery;
    $Query.Folder = $list.RootFolder;
    $List.GetItems($Query)   
    Where {$_.ContentType.Name  -ne "Folder"}   
    foreach-object { 
        #Line below will simply output to console and demonstrates another .NET call
        [System.String]::format("Moving Item {0} with ID {1}...",$_.Title, $_.ID.ToString());
        $Web.GetFile($_.Url).MoveTo([System.String]::format("{0}/{1}_.000",$FolderToMoveTo.Url,$_.ID.ToString()));
        $_.SystemUpdate($false);
        write-host "Success...";
        $ItemMoveCount++;
    };

    write-host "===============================================";
    write-host "Complete!  -> Moved " $ItemMoveCount " Items...";
    write-host "===============================================";

Worth noting here:

1. The $ItemMoveCount is simply a variable to track how many items were moved



2. The $Query is an SPQuery object used to get an SPListItemCollection from the root folder of the list



3. The guts of the collection iteration is done so via the PowerShell foreach-object operation on a result set obtained from a PowerShell Filtering operation on an SPListItemCollection returned from the said SPQuery object



4. The first line of code in the foreach-object block ([System...]) is simply another example of over utilizing the .NET Framework integration to output a string



5. The syntax and structure of the actual .MoveTo operation may seem a little unintuitive and/or strange – rest assured it is how you do it for list items
   
   (it’s a lot easier and elegant when trying to move documents in an SPDocumentLibrary to a different folder)

And finally:

#dispose:
    $Web.Dispose();
    $Site.Dispose();

This should not require explanation if:

You have spent more than 2 weeks playing with WSS .NET API
- or -
You are familiar with .NET (a quick check will reveal both these items implement IDisposable)

We All know when we instansiate an SPSite or SPWeb Object (or any .NET object implementing the IDisposable interface, it must be disposed of)

Complete Script

In all its glory, the script looks something like:

So you can copy/paste this into your own evironment, modify the first three variables and run it. By publishing this, I of course, assume nor take absoultely no responsibility to the complete destruction of your environment in the event of catastrophic failure.

---

#############################################################################################
# MOSSMove.ps1
#############################################################################################
# Author: Chris Rumel
# Date: 16 July, 2010. 
#
# Description: Moves all items existing in the root directory of an SPList to an archive folder.
#              The archive folder format is: 
#              \Archive\YYYYMMDD where YYYY = Year, MM = Month, DD = Day
#
# Usage: Simply execute with administrative rights - 
#
# Security: credentials used to run the script must have the ability to create/delete items in the list
#
# Options: Modify the first three variables to suit your environment
#
#############################################################################################

$WebURL = "http://XXX/sites/projects/";
$ListDisplayName = "Test List";
$ArchiveFolderName = "Archive";

function LoadWSSAssembly
{
write-host "Loading WSS Assembly..."  
[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") 
write-host "Done..."  
}

function folderExists([Microsoft.SharePoint.SPFolder] $Folder, [string]$FolderName)
{
    return !(($Folder.SubFolders   where {$_.Name -eq $FolderName}) -eq $null);
}

function getFolder([Microsoft.SharePoint.SPFolder] $Folder, [System.String] $folderName) 
{
    write-host "Searching for folder '" $folderName "' in SPFolder located at " $Folder.Url "...";
    if (!(folderExists $Folder $folderName))
        {
            write-host "Folder Not Found - Creating a new one...";
            $folderItem = $list.Items.Add($Folder.ServerRelativeUrl, [Microsoft.SharePoint.SPFileSystemObjectType]::Folder,$folderName);
            $folderItem.SystemUpdate();
            $list.Update();
            #Need to get a fresh reference of the folder to ensure we can see the new one just created:
            $Folder = $Folder.ParentWeb.GetFolder($Folder); 
        }
    return $Folder.SubFolders  where {$_.Name -eq $FolderName};
}

function getArchiveFolder([Microsoft.SharePoint.SPList] $List)
{
    #Get Reference to the Archive Root Folder:
    [Microsoft.SharePoint.SPFolder] $archiveRoot = getFolder $List.RootFolder $ArchiveFolderName;
    
    #Get Reference to the daily archive folder:
    $nowTimeInString = [System.DateTime]::Now.ToString("yyyyMMdd");
    [Microsoft.SharePoint.SPFolder] $todaysArchiveFolder =  getFolder $archiveRoot $nowTimeInString;
    return $todaysArchiveFolder;
}

function moveItems
{
    trap
    {
        #make sure we dispose of these in the event of an error to avoid memory leaks:
        write-host "Error - disposing of objects...";
        $Web.Dispose();
        $Site.Dispose();
    }
    
    [Microsoft.SharePoint.SPSite] $Site = New-Object Microsoft.SharePoint.SPSite($WebURL);
    [Microsoft.SharePoint.SPWeb] $Web = $Site.OpenWeb();
    [Microsoft.SharePoint.SPList] $List = $Web.Lists[$ListDisplayName]; 
     
    [Microsoft.SharePoint.SPFolder] $FolderToMoveTo = getArchiveFolder $List;
    
    $ItemMoveCount=0;
    
     $Query = New-Object Microsoft.SharePoint.SPQuery;
     $Query.Folder = $list.RootFolder;
     $List.GetItems($Query)   
     Where {$_.ContentType.Name -ne "Folder"}   
    foreach-object { 
        #Line below will simply output to console and demonstrates another .NET call
        [System.String]::format("Moving Item {0} with ID {1}...",$_.Title, $_.ID.ToString());
        $Web.GetFile($_.Url).MoveTo([System.String]::format("{0}/{1}_.000",$FolderToMoveTo.Url,$_.ID.ToString()));
        $_.SystemUpdate($false);
        write-host "Success...";
        $ItemMoveCount++;
    };

    write-host "===============================================";
    write-host "Complete!  -> Moved " $ItemMoveCount " Items...";
    write-host "===============================================";
    
    #dispose:
    $Web.Dispose();
    $Site.Dispose();
}

function go
{
loadWSSAssembly;
moveItems;
}

go;

---

Enjoy!

(PowerShell code formated via the PowerShellPack 'copy-ColoredAsHTML' PowerShell ISE SnapIn. Click here for more details)