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:
