Skip to content

Processing Results from InfoPath Forms

February 20, 2011

I was asked to help a business team process data from an online survey that they wrote in InfoPath.  The survey contained a few dozen numerical values, strings, dates, etc.  They have tried property promotion to get the data directly in SharePoint, but there were too many fields and they were having trouble keeping track.  So, just promoting the properties to SharePoint library and getting it from there was not a good solution for them.

Another constraint was that the team did not have access to the Central Admin of the SharePoint server.  As a result, the could not add code to the form (which requires a Central Admin installation) and could not deploy event handlers or other pieces of server code to parse the form at the server.  Despite these limitations our team was asked to present an automated process to process survey forms

Problem

To demonstrate the problem, I created a small InfoPath form with some demographic data and a couple of marketing questions.  The form also has a repeating table with 3 data columns: a string, a date, and a number.

Publish the form to a SharePoint 2010 and create a new Forms library.  Fill out a few forms to get some data in there and we are now ready to process the data.

Our client-side code should be able to read and parse the data for processing without the help of custom code on the server.  Further, we are looking for a solution that does not require a manual initiation.

We are looking for a 3-part solution:

  1. Enumerate the items in the Forms library.
  2. Download the form’s data
  3. Process the data

Solution

The approach this solution takes is to access the XML content of the form directly. SharePoint Forms library is simply a document library that stores XML documents.  I use two out-of-the-box web services:  Lists and Copy.  The Lists web service contains functions to let you discover documents and their attributes in the Forms library.

The Copy Web Service contains a function to access the actual content of the form and download it to your computer.

The solution communicates with SharePoint using only web services and, therefore, does not need any server privileges.

The code that is presented here is PowerShell 2.0 script.  PowerShell 2.0 is available for free in this site PowerShell download and scripts can be easily modified in the provided IDE or any text editor

Enumerating the Forms

To call Lists web services in PowerShell  and authenticate to the site use the following lines:

 1: $credentials = New-Object System.Net.NetworkCredential ($userName, $password, $domain)  
 2: $wsLists = New-WebServiceProxy -Uri $webUrl'/_vti_bin/Lists.asmx?wsdl' -UseDefaultCredential
 3: $wsLists.Credentials = $credentials

The $webUrl is a parameter to the script, and should include the absolute path to your web site.

Next we call the GetAllItems function to get a list of the submissions:

$items = $wsLists.GetListItems($formsLibrary, '', $query, $fields, 0, $options, '')

Here we can define a custom CAML query, bring additional fields (or all fields by specifying “null”) and set various options.  There is plenty of documentation of these parameters here and elsewhere on the web.

Downloading the Data

We start by setting up a proxy to the Copy web service:

$wsCopy = new-WebServiceProxy -Uri $webUrl'/_vti_bin/Copy.asmx?wsdl'

we also set up the credentials as before and then call the GetItem function to download the content:

$fieldInfo =  @() # prepare object array
$content = @() # new byte array

 

$wsCopy.GetItem($item, [ref]$fieldInfo, [ref]$content) > $null

where $item is the absolute path to the Form’s XML.  At this point the XML content of the file is placed into $content variable in a byte array.  To see the XML data we convert it to XML Document:

$mem = new-Object System.IO.MemoryStream(,$content)
$reader = new-Object System.IO.StreamReader($mem)

 

$data = [xml]$reader.ReadToEnd()

The $data variable contains the XML content of the document.  From here we can save the document locally, or process it in memory.  In the example code I combine all survey data into a single XML file that can then be processed using your favorite method: Excel, XSLT, or code.

Here is the complete listing for the code:

#################     arguments     ###########################

param ([string] $userName =$(Read-Host -prompt "User"),

[string] $password = $(Read-Host -prompt "Password"),

[string] $domain = $(Read-Host -prompt "Domain"),

[string] $webUrl = "http://www.codejos.com", # replace with web URL

[string] $formsLibrary = ‘DemoSurvey’ # replace with forms library name

)

$credentials = New-Object System.Net.NetworkCredential ($userName, $password, $domain)

##########     web services #######################################

$wsLists = New-WebServiceProxy -Uri $webUrl’/_vti_bin/Lists.asmx?wsdl’ -UseDefaultCredential

$wsLists.Credentials = $credentials

$options = [xml]"<QueryOptions><IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls></QueryOptions>"

$fields = [xml]"<ViewFields />"

$query = $null # CAML query of items to retrieve

$wsCopy = new-WebServiceProxy -Uri $webUrl’/_vti_bin/Copy.asmx?wsdl’ -UseDefaultCredential

$wsCopy.Credentials = $credentials

$fieldInfo =  @() # prepare object array for field definition

############## get alll items in the library  ##################

# you can restrict which items are processed by using a CAML query

# for example only items modified after a date

# or have an additional field and mark each item as processed

# in the loop below using UpdateListItems method of Lists ws

$items = $wsLists.GetListItems($formsLibrary, ”, $query, $fields, 0, $options, ”)

#create XML data structure and seed with root element

$xml = [xml]"<$formsLibrary />"

# process each item in the list

$items.data.row | foreach-object {

$item = $webUrl+’/’+$_.ows_FileRef.split(‘#’)[1]

$content = @() # new byte array

$wsCopy.GetItem($item, [ref]$fieldInfo, [ref]$content) > $null

$mem = new-Object System.IO.MemoryStream(,$content)

$reader = new-Object System.IO.StreamReader($mem)

$data = [xml]$reader.ReadToEnd() # data has the content of the survey

# create a new XML element to hold each response

$node = $xml.CreateElement("SurveyResponse")

# add the user and date as attributes

$attr = $xml.CreateAttribute("user")

$attr.set_Value($_.ows_Created_x0020_By)

$node.SetAttributeNode($attr) > $null

$attr = $xml.CreateAttribute("modified")

$attr.set_Value($_.ows_Modified)

$node.SetAttributeNode($attr) > $null

# add the actual response XML to the node

$node.set_InnerXml($data.MyFields.get_OuterXml())

$xml.FirstChild.AppendChild($node) > $null

}

# save results tp a local file in the current directory

$fileName = "SurveyResults.xml"

$xml.Save($fileName)

#tell the user about the file

Write-host "see results in $pwd$filename"

Technorati Tags: ,,
Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: