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:
This is something I would like to do to maintain a sharepoint List importing from SQL Server. What I need is to schedule a batch update of all our corporate active projects from our accounting database to sharepoint list. The script should update and import new items. Later I would like to use this list as a lookup list [be able to select multiple values, somehow unavailable using BDC!!] I'm very new to powershell and would highly appreciate your help!!
ReplyDeleteBCS (BDC) would be ideal for this (if you can persist with it to get it working). Otherwise you could achieve it with a PowerShell script.
DeleteYou would need to maintain some sort of key that links the projects in the accounts database to the SharePoint list, and you might want to consider which columns you set indexes on (e.g. on the column used as the key) to ensure lookups are fast when you you're updating existing lists.
You'll also need to maintain an import date somewhere, so that you can retrieve all the items (new or modified) since the last import job ran. Consider how you will lookup existing items (to update them) if the list is very large, as looping through the list for each item returned from the database (new and updated items) looking for a corresponding item in the SharePoint list would be a time consuming task. In that case, you would be better to use SPQuery to retrieve an item based on the key.
Tried external content type but since it does not allow multiple selection for lookups, I'm not able to use it. thanks
DeleteCan you post/sned me a screenshot of the sharepoint list created/updated?
ReplyDeleteThanks.
Hi Share Merchant,
DeleteThe list in the above example was pre-existing, and the beauty of the using a PowerShell script to import data, is that you import data from (almost) any source into SharePoint, mapping the source fields to the destination fields as appropriate, and it's very quick (writing it, testing it and using it). There is nothing special about the list.
I'd be happy to give you some tips if you had a specific example.
Matt
I tried external content type, but unfortunately it does not allow multiple value lookup which is something I need. Thanks.
Delete