Automated Data Acquisition Through Microsoft Office SharePoint Server 2007, Facilitated Using PowerShell

Introduction

In this article, we will describe how information generated on data acquisition devices (such as OCR devices or imagers) connected to servers running Windows Server 2003 can be efficiently integrated into Microsoft Office SharePoint Server 2007. Integration helps expose the data distributed across several devices through one centralized vehicle accessible from any workstation equipped with a web browser and a network connection.

We used Windows PowerShell, Microsoft’s extensible command-line interface shell, and a scripting language which integrates with the .NET framework, to easily insert information into various .NET accessible infrastructures, such as SQL Server, Exchange Server, and, in this case, the Office 2007 system. The type of data we will deal with is in the form of binary images, along with XML files providing annotation data for such images.

Background

Microsoft Office SharePoint Server 2007, along with Windows SharePoint Services 3.0, helps organizations provide intelligence capabilities to every employee, allowing them to share, manage, and reuse information in order to make better decisions as a team. In most organizations, this information takes the form of business documents, in the form of Word files or Excel spreadsheets. However, organizational data can also take the form of images, XML files, and other such documents not found in routine businesses.

The Office SharePoint Server 2007 platform provides Web and programmatic access to published Microsoft Office data files, such as Word documents, Excel spreadsheets, etc., enabling programmatic reuse of data, and easy development of Web-based dashboards that can incorporate key performance indicators, widgets, and published Office data files.

Using the code

To use the following code, you must have PowerShell 1.0 or 2.0 (available from Microsoft's website) installed on the same machine on which Microsoft Office SharePoint Server 2007 is installed.

Further, the following is necessary to touch base with the SharePoint object model via PowerShell:

[System.Reflection.Assembly]::Load("Microsoft.SharePoint,Version=12.0.0.0,
       Culture=neutral, PublicKeyToken=71e9bce111e9429c");

Having mapped several file servers as network drives to our SharePoint server, we obtained access to image files along with the XML files associated with each image file. For the sake of this article, the image files are large maps of information.

The code snippets below (each line ending in a semicolon) will be used within a foreach loop to achieve the following:

1. Extract data from an XML tree within an XML file.
2. Insert the same information into a "custom list" for bookkeeping purposes.
3. Create individual teamsites branded with information from each XML file.
4. Insert the image files into document libraries residing on individual teamsites.

We used the following to parse the XML files (and later import the content into a Microsoft Office SharePoint Services teamsite):

[xml] $myxmldoc = get-content image123-header.xml ;
$sitetitle = $myxmldoc.tree.imagecategory.title ;
$maptype = $myxmldoc.tree.imagecategory.maptype ;

We used the following function to insert the XML data into a custom list that will serve us for bookkeeping purposes:

$site = new-object microsoft.sharepoint.spsite("http://site-to-upload-to.org");
$web = $site.Openweb() ;
$entitychange = $web.lists["MAPS"].Items.Add() ;
$entitychange["Title"] = $sitetitle ;
$entitychange["Location"] ="http://site-to-upload-to.org/sites/"+ $sitetitle ;
$entitychange["Map Type"]  = $maptype ;
$entitychange.Update(); 
$site.Dispose();

To create a new teamsite under the “sites” hierarchy, all that’s needed is the following:

$site = new-object microsoft.sharepoint.spsite( "http://site-to-upload-to.org/sites/" )  ;
$site.Allwebs.Add("sites/"+$sitetitle, $sitetitle, $sitetitle, [int]1033, "STS", "", "") ;
$site.Dispose();

To create a new document library (a form of web folder), also a type of list, under the recently created teamsite, proceed as follows:

$site = new-object 
  microsoft.sharepoint.spsite("http://site-to-upload-to.org/sites/" +$sitetitle) ;
$website = $site.OpenWeb() ;
$listtemplate = $website.ListTemplates["Document Library"];
$listID = $website.Lists.Add("Map", "", $listtemplate) ;
$list = $website.Lists.GetList($listID, $true) ;
$list.Title = "Map" ;
$list.OnQuickLaunch = $true;
$list.Update() ;

To add a special custom field within a default value to a custom list, do as below (note we are using XML to create a custom field; custom fields cannot be introduced without difficulty otherwise):

$list.Fields.AddFieldAsXml("<Field Name=’Annotation Type’ 
                            DisplayName=’Annotation Type’ Type=’Text’ />");
$list.Update();
$list.Fields["Annotation Type"].DefaultValue = "Annotated" ;
$list.Fields["Annotation Type"].Update();

Finally, we will add files from a local mapped network drive (or a local folder) to the document library we recently generated:

$webclient = new-object System.Net.WebClient ;
$webclient.Credentials = [System.Net.CredentialCache]::DefaultCredentials ; 
$webclient.UploadFile($uristring, "PUT", $filepath ) ;
$webclient.Dispose();

Consequently, one can browse to the Microsoft Office SharePoint Portal via a regular web browser to view the listing of images previously uploaded using the custom list created for bookkeeping purposes. From there, end-users can access, from a central location, data that would otherwise require physical access to the file servers or many manual steps necessary to map multiple network drives under a variety of restriction conditions.

Points of interest

PowerShell helped considerably in shrinking the development time. Microsoft Office SharePoint Server 2007, along with PowerShell, enabled the centralized sharing of data residing on physically disparate data stores.