Batch loading InfoPath Forms in SharePoint 2007
I have recently been asked to batch load a bunch of InfoPath Forms into a SharePoint Server for access via Forms Server. The customer had a database extract, in the form of a CSV file, and they wanted to convert this data into pre-filled forms, and then load the forms into SharePoint Server. They also wanted to apply some specific ACLs on the forms, because they contained some confidential content.
I looked a little bit around for a ready-made solution, but couldn't find an easy way to use CSV data to pre-generate InfoPath Forms & upload them to SharePoint, so I decided to write a little C# program to solve the problem.
Basically, I divided the problem in 3 chunks:
- Convert the CSV file into generic XML
- Convert the generic XML into proper InfoPath XML using an XSLT
- Upload the generated XML file into SharePoint and modify the ACLs programmatically
1. Converting the CSV into XML
I could probably just use Excel's XML capabilities to do this, but I wanted to automate all the steps in a single program. After digging a bit around the Web, I found this simple solution: XmlCsvReader on MSDN (and a slightly newer version on CodePlex). It's an implementation of an XmlReader that reads CSV input and generates an XML stream. It's really nifty and allows you to convert from CSV to XML very simply:
XmlDocument doc = new XmlDocument();
XmlCsvReader reader = new XmlCsvReader(new Uri("file:///C:/temp/StatusReportData.csv"), Encoding.UTF8, doc.NameTable);
reader.FirstRowHasColumnNames = true;
doc.Load(reader);
I started with this data:
Date,Project,Prepared By,Manager,E-Mail,Department
1-1-2007,1,Thomas Conté,Harry Cover,tconte@microsoft.com,Finance
2-1-2007,2,Bernard Barnier,Harry Cover,bbarnier@microsoft.com,Finance
3-1-2007,3,Charles Duchemin,Harry Cover,cduchemin@microsoft.com,Finance
4-1-2007,1,Ludovic Cruchot,Harry Cover,lcruchot@microsoft.com,HR
5-1-2007,2,Claude Ratinier,Harry Cover,cratinier@microsoft.com,HR
6-1-2007,3,Jean Durand,Paul Dugenou,jdurand@microsoft.com,HR
7-1-2007,1,Paul Martin,Paul Dugenou,pmartin@microsoft.com,HR
8-1-2007,2,Gaston Lagaffe,Paul Dugenou,glagaffe@microsoft.com,IT
9-1-2007,3,Paul Marcel,Paul Dugenou,pmarcel@microsoft.com,IT
10-1-2007,1,Marcel Paul,Paul Dugenou,mpaul@microsoft.com,IT
And XmlCsvReader generates an XML document that looks like this:
<root>
<row>
<Date>1-1-2007</Date>
<Project>1</Project>
<Prepared_x0020_By>Thomas Conté</Prepared_x0020_By>
<Manager>Harry Cover</Manager>
<E-Mail>tconte@microsoft.com</E-Mail>
<Department>Finance</Department>
</row>
<row>
<Date>2-1-2007</Date>
<Project>2</Project>
<Prepared_x0020_By>Bernard Barnier</Prepared_x0020_By>
<Manager>Harry Cover</Manager>
<E-Mail>bbarnier@microsoft.com</E-Mail>
<Department>Finance</Department>
</row>
</root>
The complete document contains one row element for each line in the original CSV file. Now I need to break up this document in chunks, and convert each row element into a properly formatted InfoPath XML document.
2. Converting the XML into an InfoPath document
An InfoPath document, or form, is just a regular XML file, with a couple of XML Processing Instructions that indicate that the XML file is indeed an InfoPath form. These two expressions, that must be present at the top of the XML file, are:
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2">
and the mso-infoPathSolution instruction that indicates, among other things, the location of the for template.
Now, our base data being XML and the target InfoPath document being XML as well, the easiest way to convert from one to the other is of course to use an XSL Transformation (XSLT). This XSLT will simply output the InfoPath XML document, while inserting the data from our generic XML file in the right places. The easiest way to create the XSLT is to start from the target InfoPath XML: use the InfoPath client (or SharePoint Forms Server) to create an empty form. Then open the resulting XML file using your favorite text editor, and copy the whole XML skeleton into the body of your XSLT. The result, including the processing instructions, will look like this:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
<xsl:template match="row">
<xsl:processing-instruction name="mso-infoPathSolution">
<xsl:text>name="urn:schemas-microsoft-com:office:infopath:Status-Reports:-myXSD-2005-09-22T20-42-56" solutionVersion="1.0.0.3" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="https://w2k3sp2-spdev/SiteDirectory/infopath/Status%20Reports/Forms/template.xsn"</xsl:text>
</xsl:processing-instruction>
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="InfoPath.Document" versionProgid="InfoPath.Document.2"</xsl:text>
</xsl:processing-instruction>
<my:statusReport xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="https://www.w3.org/1999/xhtml" xmlns:my="https://schemas.microsoft.com/office/infopath/2003/myXSD/2005-09-22T20:42:56" xmlns:xd="https://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
<my:reportDate>
<xsl:value-of select="Date"/>
</my:reportDate>
<my:projectName>
<xsl:value-of select="Project"/>
</my:projectName>
<my:preparedBy>
<xsl:value-of select="Prepared_x0020_By"/>
</my:preparedBy>
<my:emailAddress>
<xsl:value-of select="E-Mail"/>
</my:emailAddress>
<my:managerName>
<xsl:value-of select="Manager"/>
</my:managerName>
<my:departmentName>
<xsl:value-of select="Department"/>
</my:departmentName>
<my:summary></my:summary>
Note that we insert our original XML file element values into the XSLT output, using xsl:value-of in the right places. Now, all we need to do is to actually apply this XSLT to our XML document:
XslCompiledTransform transform = new XslCompiledTransform();
transform.Load("CSV2InfoPath.xslt");
foreach (XmlNode n in doc.DocumentElement.ChildNodes)
{
string fileName = n.ChildNodes[2].InnerText + ".xml";
string filePath = "C:\\temp\\" + fileName;
StreamWriter writer = new StreamWriter(filePath);
transform.Transform(n, null, writer);
writer.Close();
The key here is the XslCompiledTransform.Transform() method, that actually executes the transformation. Note that we iterate over our document's child nodes (i.e. row elements) and apply the XSLT to each chunk. In this example, the result is written to a temporary file on the disk, so that you can actually test the result by opening it with InfoPath.
Congratulations, you just converted a CSV data export into a bunch of InfoPath forms!
3. Upload the forms to SharePoint
The last part of our little operation is to actually publish the forms to a SharePoint Server, so that employees can open and update the forms using Forms Server (allowing them to complete these tasks using a Web browser, instead of having to edit the form on their desktop with the InfoPath client).
Since my batch program was intended to run on the SharePoint server itself, I was able to use the SharePoint API directly to upload the form. The code is fairly straightforward:
SPWeb web = new SPSite("https://w2k3sp2-spdev/SiteDirectory/infopath").OpenWeb();
FileStream fStream = File.OpenRead(filePath);
byte[] contents = new byte[fStream.Length];
fStream.Read(contents, 0, (int)fStream.Length);
fStream.Close();
web.Files.Add("Status%20Reports/" + fileName, contents);
The last part is a bit more tricky: using SharePoint 2007 new Role Assignment API to automatically apply security settings on the uploaded forms; the idea being, for example, to make sure each form can only be edited by the corresponding employee. The code will look like this:
SPFile newFile = web.GetFile("Status%20Reports/" + fileName);
newFile.Item.BreakRoleInheritance(false);
SPRoleDefinitionCollection roleDefs = web.RoleDefinitions;
SPRoleAssignmentCollection roleAssignments = newFile.Item.RoleAssignments;
SPRoleAssignment newAssignment = new SPRoleAssignment("W2K3SP2-SPDEV\\SPUser", "email@toto.com", "SP User", "");
newAssignment.RoleDefinitionBindings.Add(roleDefs["Read"]);
roleAssignments.Add(newAssignment);
In this code, we first use BreakRoleInheritance() to stop inheriting the ACLs from the library. We then retrieve the current site Role Definitions (i.e. "Read", "Contribute", "Full Control", etc.) We then create a new Role Assignment (for a test user called SPUser), apply the "Read" role to it, and add it to the RoleAssignments collection for the form we just uploaded.
Comments
Anonymous
November 06, 2007
Great post Thomas. I've taken the more programmatic approach using xsd and working with the object model but starting to think XSL is probably the better way to generate forms on the fly. I found the form urn by accident in the SP object model but cannot remember where I saw it. The version changes when the form is updated which cries out for reading from the template. Do you recall how that is found? Thanks. Timothy TurtleAnonymous
April 24, 2008
Hello Thomas, Thank you for the greate post. Because I don’t understand where I must place the code in step 2 and 3 in the XmlCsvReader source files. Can you post that with a little bit more details please? Thank you MichaelAnonymous
February 25, 2009
Hello Thomas How can we check if XML is not converted or havinh any problem in XML which we are converting into infopath form. Please advise