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:

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…