How to get data from a completed infopath forms

Question

On our portal (Enterprise 2010) there are about 100 subsites (all in a single site collection), and there are form-libraries there where a user can fill out an infopath form and submit the data to a library on that same subsite.

My problem is that now the boss wants a summary of data from all the submitted forms - from one field in particular. I don't need the data to stay in sharepoint - I just need a spreadsheet summary that I can work with.

How can I get that? Powershell? I have admin rights everywhere.

Answer 1

You could expose certain InfoPath fields as columns in the SharePoint form library. (These fields can be configured in the form publishing wizard.) Then you could view the libraries as spreadsheets. However, this would only work for forms that have been submitted after the field were exposed.

To get data that has already been submitted, I think you will need to do some programming or at least XML parsing. You have to first extract the underlying XML files, which you could do programmatically, or by grabbing them from the Explorer view or by mapping the library to a drive letter. Then you will need to parse through the XML, either by writing a custom program or PowerShell script, or by using some tool--possibly a Windows grep tool with a regular expression feature--that can extract the field that you need.

Answer 2

You might consider using something like InfoPath DB (http://infopathdb.codeplex.com/) which is a codeplex project that can be used to query the raw XML as a datasource as JT mentioned, though without you having to write the actual code. This would make it possible to get the data even if the fields/columns were not previously defined in the SharePoint libraries.

Answer 3

If you open your list in windows explorer mode and press the "Show the Preview Pane" button and select (not open) each file and allow it to load in the preview pane, the information will populate in sharepoint in your list view. It's a bit tedious, but it works.