Updating InfoPath form templates and data connections with PowerShell

Occasionally when migrating content between SharePoint sites or URLs you may come across issues with InfoPath forms that have embedded data connections. These data connection can contain URLs that allow the forms to submit or save to SharePoint.

However, what happens if you want to update all of these embedded data connections contained in dozens or hundreds of InfoPath form templates for one reason or another, perhaps during a migration, upgrade or when you copy your production content to a test or dev farm?

The answer, like most things SharePoint 2010, lies with PowerShell. Below is a script I’ve written to perform the following actions:

  • Find all the form libraries in a web application
  • Download the form templates
  • Crack open the form template xsn (cab file)
  • Update any references to the old urls
  • Rebuild the form template xsn (cab file)
  • Upload the updated form templates to the form libraries

The above script generates output similar to that shown below:

image

The script:

Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## the web application to be searched for form libraries
$url = "
http://bc01:2007"

## temp working directory
$dir = "C:\Documents\SharePoint\Upgrade\Forms"

## urls to search for
$fromURL = "
http://bc01:2007/sites/New"
$fromURL2 = "http://xxxxxxxxx.tttttttt.wwwwwwww"

## the url to be used as a replacement
$toURL ="
http://bc01"

function Compress-Directory ($dir, $cabFileFullPathAndFilename)
{
    ## the cablib dll can be downloaded from
http://wspbuilder.codeplex.com
    [void][reflection.assembly]::LoadFile("C:\Documents\SharePoint\Upgrade\Forms\CabLib.dll")
    $c = new-object CabLib.Compress
    $c.CompressFolder($dir, $cabFileFullPathAndFilename, $null, $null, $null, 0)
    ## thanks to
http://www.pseale.com for this function
}

Write-Host "Scanning ‘$Url’ for form templates:"

foreach ($web in (Get-SPWebApplication $toURL | Get-SPSite -Limit All| Get-SPWeb -Limit All))
{
    foreach($list in $web.Lists)
    {
        if ($list.DocumentTemplateUrl -ne $null)
        {
            ## does this list have a form template attached to it
            if ($list.DocumentTemplateUrl.ToString().ToLower().EndsWith(".xsn"))
            {
                $listUrl = $list.ParentWebUrl + ‘/’ + $list.rootFolder
                $templateUrl = $list.ParentWebUrl + ‘/’ + $list.DocumentTemplateUrl
                Write-Host "    found – " -foreground gray -nonewline; Write-Host $listUrl 
               
## get the file
                $file = $web.GetFile($templateUrl)


                ## download the form template
                $filename = $file.Name
                $fileID = $file.UniqueId.Tostring()
                $localfile = $fileID + "\" + $filename
                Write-Host "            downloading – " -foreground gray -nonewline; Write-Host $templateUrl -nonewline; Write-Host " to " -foreground gray -nonewline; Write-Host $localfile -nonewline
                $file = $web.GetFile($templateUrl)
                $bytes = $file.OpenBinary();
                # Download the file to the path
                $localfile = $dir + "\" + $localfile
                New-Item "$dir\$fileID" -type directory -force | Out-Null
                New-Item "$dir\$fileID\Extracted" -type directory -force | Out-Null
                [System.IO.FileStream] $fs = new-object System.IO.FileStream($localfile, "OpenOrCreate")
                $fs.Write($bytes, 0 , $bytes.Length)
                $fs.Close()
                Write-Host " – done." -foreground green
           
                ## crack open the form template
                Write-Host "            extracting – " -foreground gray -nonewline; Write-Host $filename -nonewline; Write-Host " to " -foreground gray -nonewline; Write-Host "$fileID\Extracted" -nonewline
                EXPAND "$localfile" -F:* "$dir\$fileID\Extracted" | Out-Null
                Write-Host " – done." -foreground green
               
                ## update the data connections
                $extractedFiles = Get-ChildItem "$dir\$fileID\Extracted" *.x*
                foreach ($extractedFile in $extractedFiles)
                {
                    $efn = $extractedFile.Name
                    Write-Host "            checking – " -foreground gray -nonewline; Write-Host $efn -nonewline
                    $f = [system.io.file]::Open("$dir\$fileID\Extracted\$efn" ,"open","read","Read")
                    $sr = New-Object system.io.streamreader $f
                    $contentBefore = $sr.ReadToEnd()
                    $sr.close()
                    $f.close()
                    
                    ## now replace the urls
                    $contentAfter = $contentBefore.ToString().Replace("$fromURL","$toURL")
                    $contentAfter = $contentAfter.ToString().Replace("$fromURL2","$toURL")
                   
                    if ($contentBefore -eq $contentAfter)
                    {
                        Write-Host " – skipped." -foreground green
                    }
                    else
                    {                       
                        ## now write the file
                        $fs = [system.io.file]::Open("$dir\$fileID\Extracted\$efn" ,"create","write","Write")
                        $sw = New-Object system.io.streamwriter $fs
                        $sw.Write($contentAfter)
                        $sw.close()
                        $fs.close()

                        Write-Host " – updated." -foreground green
                    }
                }
               
               
                ## rebuild the cabinet
                Write-Host "            building cabinet – " -foreground gray -nonewline; Write-Host $localfile -nonewline
                Move-Item "$localfile" "$dir\$fileID\$filename.old" -force
                $extractedFiles = Get-ChildItem "$dir\$fileID\Extracted" *.*
                Compress-Directory "$dir\$fileID\Extracted" "$dir\$fileID\$filename"
                Write-Host " – done." -foreground green

                
                ## upload the form template
                Write-Host "            saving – " -foreground gray -nonewline; Write-Host $filename -nonewline; Write-Host " to " -foreground gray -nonewline; Write-Host $listUrl -nonewline
                $bytes=get-content -Encoding byte "$dir\$fileID\$filename"
                $bytes=[byte[]]$bytes
                $file.SaveBinary($bytes)
                Write-Host " – done." -foreground green
            }
        }
    }
    $web.Dispose()
}

I hope this helps someone else who is facing the same challenges…

About these ads

3 Responses to Updating InfoPath form templates and data connections with PowerShell

  1. Jeff Breece says:

    Excellent post! We use composite applications in our shop and the data connection issue has been a definite pain point. I need to search your blog for using the Secure Store Service to overcome the double hop issue for SharePoint web services in a multi server Farm sometime.

  2. SharePoint Guy says:

    Brian I have two words for you……you beauty!! Thanks heaps for the script.

  3. The best script I have ever seen or used! Tried using update-spinfopathuserfileurl….. Should have never wasted my time!

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: