Extract InfoPath form contents from SharePoint library 2010 to Excel
Learn which options are available if you want to extract the contents from one or multiple InfoPath forms that are stored in a SharePoint form library to an Excel spreadsheet.
If you have one or more InfoPath forms stored in a SharePoint 2010 form library and want to extract data from them, you can use the Export to Excel command on the SharePoint form library to do so.
Note however, that the form template used to create the InfoPath forms should be published with promoted fields and those fields filled out in the InfoPath forms for this technique to work.
The Export to Excel command allows you to download a Microsoft Excel Web Query File (.iqy), which you can then open in Excel 2010.
Once opened, the Excel workbook should have a live connection to the SharePoint form library, so whenever new InfoPath forms are added to the SharePoint form library, you can click the Refresh All button under Data > Connections or the Refresh button under Design > External Table Data to see those new forms appear in Excel.
However, the technique of using the Export to Excel command along with promoted fields has its limitations, because you cannot promote all types of fields as you would like to. For example, if you want to promote a field from a repeating table, you'll wind up with only the field in the first row or an aggregate of that field from all of the rows of the repeating table. The technique described above works well for simple scenarios where you have promoted for example single text fields to a SharePoint form library.
The entire technique is quite simple and easy to implement, but if you are having issues envisioning it, it has been laid out step-by-step in recipe 104 of InfoPath 2013 Cookbook 2. The sample solution includes exporting data from a repeating table on InfoPath forms to an Excel spreadsheet.
If you want to extract more complex data from InfoPath forms and transfer that data to Excel, you will either have to write code or make use of Excel Services. The latter may also have its limitations, which you would have to find workarounds for.
But where repeating table data is concerned, you can transfer such data from an InfoPath form that is stored in a SharePoint form library to an Excel spreadsheet automatically without having to write code, so by making use of Excel Services in SharePoint.
To learn more about writing code in either Excel or InfoPath to transfer data between an Excel spreadsheet and an InfoPath form, see Chapter 3 of InfoPath 2010 Cookbook 5.
However, keep in mind that you won't be able to "bulk export" data contained in multiple InfoPath forms (as you would when you use the Export to Excel command) unless you write code.
In summary: If you want to extract data from multiple simple InfoPath forms stored in a SharePoint form library, use the Export to Excel command. If you have slightly more complex forms, try Excel Services in SharePoint for the job. And for very complex forms or custom data extraction, write code.
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.