Friday 26 July 2013

Importing Data Into A SharePoint List From An SQL Database

I often need to import data from various systems into SharePoint as part of the process of deploying a new SharePoint based solution that is intended to replace an existing (a.k.a aging) solution.

I find PowerShell is immensely useful for these types of tasks. PowerShell scripts are quick to write, incredibly flexible and very quick to test, troubleshoot, deploy, and re-deploy!

As an example, I thought I'd demonstrate how to import some data from a SQL database into a SharePoint list. The SQL query joins two lists, and returns a few hundred rows. The script iterates through the rows, adding a new SharePoint list item for each row.

It also performs some other actions as it iterates each row;

1. It adds the Category row column to the list items olCategory field (which is a Managed Metadata Field). As it does this, it checks the Termset for the Term, and if the Term doesn't already exist, it creates it.
2. It trims the rows Description field if it exceeds 255 characters.
3. It checks the rows HyperLink field starts with "http" before updating the list items URL field.
4. It uses Write-Progress to keep the "user" (me) informed about the progress of the import (see image below).

Example: Querying the SQL Server and iterating through the rows:
#Connect to the database
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Library; Initial Catalog=UsefulWebsites; Integrated Security=SSPI")
$conn.Open();
$query = "select lc.CategoryName,il.DisplayName,il.HyperLink,il.Description,il.Shared,lc.Shared as LCShared from incelinks il join linkscategory lc on il.CategoryId = lc.CategoryId where il.Shared = 1 and lc.shared =1 order by lc.CategoryName"
$dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);
$dt = new-object System.Data.DataTable;
$dap.Fill($dt);
foreach($r in $dt.Rows)
{
    Write-Host $r["DisplayName"]
}
$conn.Close();


Example: Update the list items taxonomy field (Setting the value of a taxonmy field using the CategoryName database column)
$w = Get-SPWeb http://corporate/library;
$list = $w.Lists["Useful Websites"];
$categoryField = $list.Fields["olCategory"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];
$tsId = $categoryField.TermSetId;
$termStoreId = $categoryField.SspId;
$tsession = Get-SPTaxonomySession -Site $list.ParentWeb.Site;
$tstore =  $tsession.TermStores[$termStoreId];        
$tset = $tstore.GetTermSet($tsId);

foreach($r in $dt.Rows)
{
    $i = $list.Items.Add();
    $i["Title"] = $r["DisplayName"];
    $category = $r["CategoryName"];
    #Check if the term exists
    $terms = $tset.GetTerms($category,$false);
    $term = $null;
    if($terms.Count -eq 0)
    {
        #Create the term
        Write-Host ([String]::Format("Creating Term, {0}",$category)) -ForegroundColor DarkYellow;
        $term = $tset.CreateTerm($category, $tstore.Languages[0]);
        $tstore.CommitAll();
    }    
    else
    {
        #The term exists. Note we are using the first instance of the Term
        $term = $terms[0];
    }
    #Set the Managed Metadata field
    $categoryField.SetFieldValue($i,$term);
    $i.Update()
}


Example: The full script (for querying a SQL database and inserting the rows from the query as new items in a SharePoint list):
function Get-UsefulWebsitesFromSql
{    
    #Connect to the database
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Library; Initial Catalog=UsefulWebsites; Integrated Security=SSPI")
    Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (1) -Status "Opening Connection to the SQL Server";
    $conn.Open();
    try
    {            
        #Execute the query
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (2) -Status "Querying SQL Server";    
        $query = "select lc.CategoryName,il.DisplayName,il.HyperLink,il.Description from links il join linkscategory lc on il.CategoryId = lc.CategoryId where il.Shared = 1 and lc.shared =1 order by lc.CategoryName"
        $dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);
        $dt = new-object System.Data.DataTable;
        $dap.Fill($dt);                
        $w = Get-SPWeb http://corporate/library;
        $list = $w.Lists["Useful Websites"];    
        $listTitle = $list.Title;
        $pi = $dt.Rows.Count;        
        $pci =1;                
        $categoryField = $list.Fields["olCategory"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];
        $tsId = $categoryField.TermSetId;
        $termStoreId = $categoryField.SspId;
        $tsession = Get-SPTaxonomySession -Site $list.ParentWeb.Site;
        $tstore =  $tsession.TermStores[$termStoreId];        
        $tset = $tstore.GetTermSet($tsId);        
        $itemsAdded = 0;        
        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (25/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";        
        foreach($r in $dt.Rows)
        {
            Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (($pci+25)/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";
            Write-Progress -Id 2 -ParentId 1 -Activity "Adding new items to $listTitle" -PercentComplete ($pci/$pi*100) -Status "Importing item $pci into SharePoint.";
            $pci++;
            $i = $list.Items.Add();
            try
            {
                $itemsAdded++;
                #Set the title
                $i["Title"] = $r["DisplayName"];
                #Set the Description Field. Trim the description if it's longer than 255 characters
                $description = if(($r["Description"]).ToString().Length -gt 255){($r["Description"]).ToString().SubString(0,254)}else{($r["Description"]).ToString()};
                $i["Description"] = $description;
                #Set the URL field (Hyperlink field)
                $hyperLink = $r["HyperLink"];
                if(!$hyperLink.ToLower().StartsWith("http"))
                {
                    $hyperLink = ([String]::Format("http://{0}",$hyperLink));
                }            
                Write-Host $hyperLink;
                $urlFieldValue = New-Object Microsoft.SharePoint.SPFieldUrlValue;
                $urlFieldValue.Description = ($r["DisplayName"]).Replace("&","and");
                $urlFieldValue.Url = $hyperLink;            
                $i["URL"] = $urlFieldValue;
                #Set the category field (Managed Metadata)
                $category = $r["CategoryName"];
                $terms = $tset.GetTerms($category,$false);
                $term = $null;
                if($terms.Count -eq 0)
                {
                    Write-Host ([String]::Format("Creating Term, {0}",$category)) -ForegroundColor DarkYellow;
                    $term = $tset.CreateTerm($category, $tstore.Languages[0]);
                    $tstore.CommitAll();
                }    
                else
                {
                    $term = $terms[0];
                }
                $categoryField.SetFieldValue($i,$term);
                #Save changes to the item
                $i.Update();
                Write-Host ([String]::Format("Added item: '{0}', with URL: {1}",$r["DisplayName"],$hyperLink)) -ForegroundColor Green;
            }
            catch [System.Exception]{
                Write-Host ([String]::Format(" Error adding item. Item {0} has been skipped. Error: {1}. ",$r["DisplayName"],$_)) -ForegroundColor Red -BackgroundColor White;
                continue;    
            }    
        }        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (80) -Status "Closing SQL Connection.";        
        Write-Host ([String]::Format("Finished importing items into the list. Imported {0} items. ",$itemsAdded)) -ForegroundColor Blue -BackgroundColor White;
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (90) -Status "Finished importing ($pi) items into SharePoint.";
        $w.Dispose();
    }
    catch [System.Exception]{
        Write-Host ([String]::Format("Error: {0} ",$_)) -ForegroundColor Red -BackgroundColor White;    
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (100) -Status "An error occured.";        
    }
    finally{
        $conn.Close();    
    }            
}
Get-UsefulWebsitesFromSql;

The script looks like this when it's running:

Thursday 25 July 2013

Using Write-Progress in a Long Running SharePoint PowerShell Script

I wrote an article on the Microsoft TechNet Wiki about using the PowerShell Write-Progress cmdlet within a long running PowerShell script. The wiki article can be seen here: An Example of Using Write-Progress in a Long Running SharePoint PowerShell Script

I thought I'd summarise the article here too.

I uploaded the script that I based my example on to the TechNet Gallery, and it can be downloaded from here: List or Delete excess ListItem Versions in a SharePoint List or Document Library

This article covers a simple scenario of using Write-Progress with a long running script that performs iterations of all the item versions in a SharePoint list, within a web and all of its sub-webs. It highlights how to nest progress bars and dynamically set the percent (%) complete. It also demonstrates using informative progress status messages.

Here are a few screen shots from the article, that demonstrate the use of the Write-Progress in a script.

Example: Enumerate all of the publishing pages in the Pages library (if found) of the sneakpreview web, and all sub-webs of sneakpreview. Store all versions of a page in excess of 5 versions in the $excessVersions variable.
$excessVersions = Get-ExcessListItemVersions -SiteUrl "http://sneakpreview  " -ListTitle "Pages" -MaxVersions 5 -Recurse



Example: Enumerate all of the publishing pages in the Pages library (if found) of the sneakpreview/informationtechnology/teamsite web.
$excessVersions = Get-ExcessListItemVersions -SiteUrl "http://sneakpreview/informationtechnology/teamsite  " -ListTitle "Pages" -MaxVersions 5



Wednesday 10 July 2013

Well that was fun! (Bulk deleting items from a SharePoint list)

Today I made the mistake of adding approximately 32,000 new items into the wrong SharePoint list.

Oops.

So I was quickly on the hunt for some PowerShell examples of using batch operations to delete many items (have you ever tried deleting hundreds of list items using SPListItemCollection.DeleteItemById(id) or SPListItemCollection.Delete(index)? It's slow, so it's never going to work with tens of thousands of items, not to mention the problems with list threshold exceptions!).

With a little help from the internet (see blog acknowledgements below) I knocked up a quick script that uses SPQuery to query the list for the all items created since the time of my import (thankfully this list is used to record data imported on a schedule, so I could safely do this). Using the items returned, I built my batch query.

Phew... problem solved with minimal hair loss.

Firstly, thanks to the following blogs that helped:
Tim Odell - powershell-function-to-delete-all-sharepoint-list-items
Kirk Evens - iterating-large-sharepoint-lists-with-powershell

Now, back to the PowerShell...

Create the query:
$list =  (Get-Spweb http://devmy101).GetList("http://devmy101/Lists/smarEnteredTerritorialWaters")
$query = New-Object Microsoft.SharePoint.SPQuery;
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = 2000;
$query.Query = '<Where><Gt><FieldRef Name="Created"/><Value Type="DateTime" IncludeTimeValue="TRUE">2013-07-10T14:20:00Z</Value></Gt></Where>';

Build the command (note the query is limited to returning 2000 items at a time, and uses the ListItemCollectionPosition property to continue retrieving items in batches of 2000 until all the items have been queried. See this MSDN documentation for more info.)
$itemCount = 0;
$listId = $list.ID;
[System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder";
$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>");
$command = [System.String]::Format( "<Method><SetList>{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>", $listId, "{0}" );

do
{
    $listItems = $list.GetItems($query)
    $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach ($item in $listItems)
    {
        if($item -ne $null){$batchXml.Append([System.String]::Format($command, $item.ID.ToString())) | Out-Null;$itemCount++;}
    }
}
while ($query.ListItemCollectionPosition -ne $null)

$batchXml.Append("</Batch>");
$itemCount;

And lastly (and most importantly!), run the query
$web = Get-Spweb http://inceweb/HKMarineDB;
$web.ProcessBatchData($batchXml.ToString()) | Out-Null;

Thursday 4 July 2013

Viewing, sorting and filtering SharePoint User Profiles using PowerShell

I wrote a post a while back that had a snippet of PowerShell code that enumerated all the SharePoint User Profiles, looking for badly formatted PictureURL properties (here: http://matthewyarlett.blogspot.co.uk/2012/09/profile-photos-not-synchronising-from.html).

Today someone asked a question in the MSDN forums about listing out all the user profiles that don't have the PictureURL property set. I supplied a slightly modified version of this script to list out all the user profiles without the PictureURL as an answer. It made me think of other uses for this, and I thought I'd share them here.

The basic code for getting all the user profiles is:

[void][reflection.assembly]::Loadwithpartialname("Microsoft.Office.Server") | out-null;
$site=new-object Microsoft.SharePoint.SPSite("https://c05470sp10:7443"); #Central Admin site
$servercontext=[Microsoft.Office.Server.ServerContext]::GetContext($site);
$site.Dispose();
$upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($servercontext);
$pc = $upm.GetEnumerator();


Using the collection of user profiles, we can then view, sort and filter user profiles rather easily. Here are some examples:

List all the user profiles:
$pc = $upm.GetEnumerator();
$pc | FT DisplayName 


List all the user profiles, including the account name. The account name is one the user profiles properties, in the Properties collection. We can display this information using FT (Format-Table) by specifying the field as an expression.

UserProfile object (note the Properties Collection property)



Using the Property in an expression. User profile property can be accessed directly from the UserProfile object by name, as userprofileobject["propertyname"]. We can leverage this in Format-Table by using an expression.
$pc = $upm.GetEnumerator();
$pc | FT DisplayName,@{Label="AccountName"; Expression={$_["AccountName"]}}; 


List all the user profiles, including the users department and job title.
$pc = $upm.GetEnumerator();
$pc | FT DisplayName,@{Label="Department"; Expression={$_["Department"]}},@{Label="Job Title"; Expression={$_["SPS-JobTitle"]}};


List all the user profiles including the users department and job title, sorting the values by department.

$pc = $upm.GetEnumerator();
$pc | Sort -property  @{Expression={$_["Department"]}} | FT DisplayName,@{Label="Department"; Expression={$_["Department"]}},@{Label="Job Title"; Expression={$_["SPS-JobTitle"]}};



List all the user profiles including the account name and department, sort the values by department, and filtering the user profiles to users in the IT department.

$pc = $upm.GetEnumerator();
$pc | Sort -property  @{Expression={$_["Department"]}} | ?{$_["Department"] -like "I.T."} | FT DisplayName,@{Label="Department"; Expression={$_["Department"]}},@{Label="Job Title"; Expression={$_["SPS-JobTitle"]}};




List all the user profiles that don't have a PictureURL set:

$pc = $upm.GetEnumerator();
$pc | Sort -property  @{Expression={$_["FirstName"]}} | ?{$_["PictureUrl"].Value -eq $null -or $_["PictureUrl"].Value -eq ""} | FT DisplayName;