Share via

Consuming Excel Data using InfoPath Database Data Connections

  • Article

InfoPath data connections can be one of the most powerful features of the product.  However, sometimes the out-of-box support for various data sources just doesn't seem to cut it when you get out into the field.  How in the world can I get data from an Excel spreadsheet  into my InfoPath form?  A typical response might include externally automating Excel via COM or the managed PIA to create InfoPath forms.  Or you might try using the Excel 2003 XML spreadsheet file format with the InfoPath XML data connection.  While both solutions are feasible, they can be a bit clunky to manage multiple files generated from your spreadsheet…
 
Here is your question answered!  Access 2007 provides support for linked tables, virtual tables that pull data from a data source external to an Access database.  Since InfoPath has a built-in database data connection that can query data directly from an Access database, Access 2007 can effectively act as a router for data sources that aren't directly supported by InfoPath.  In this post, we'll step through setting up an InfoPath database data connection that queries data from an Excel spreadsheet.
 
Prepare your Excel spreadsheet
Once you've added all the necessary data to your Excel spreadsheet, save the Excel workbook to a shared network location that will be accessible to all users filling out your InfoPath form.
 
Create the Access database to link the Excel data
Launch Access 2007 and create a new blank database.  When you create a blank database, a default table will be created.  Go ahead and delete the table (just closing the query view should remove the table.)
Switch to the "External Data" tab in the Ribbon

 

Click the "Excel" icon in the "Import" chunk to launch the "Get External Data - Excel Spreadsheet" wizard.
Browse to the Excel workbook that you saved in the "Prepare your Excel spreadsheet" section, above.
Select "Link to the data source by creating a linked table" and then click "OK".

 

Choose the Sheet or Named Range that you want to import, then click "Next >". If the first row of the spreadsheet contains column headers, check the "First Row Contains Column Headings", then click "Next >".

Type a name for the linked table (I'll call it "Spreadsheet" for this post), then click "Finish".
Click "OK" when Access tells you it's done linking the table.  When the dialogs close, you'll see the linked table listed in the "All Tables" task pane

 

Save the Access database to the same directory where the Excel file was saved, then close Access.
 
Design the InfoPath Form Template to Query the Linked Table
Design an InfoPath Form Template based on a "Database" (you could also add the data connection as a secondary database data connection in an existing form template).

 

Once you've clicked "OK", the "Data Connection Wizard" will open.  Click "Select Database…" to open the "Select Data Source" file browser dialog.

Click "New Source…"

NOTE: At this point, it's tempting to just browse to the Access database as you normally would.  However, the default provider used in that case does not support linked tables, so InfoPath will see no tables in the database.  In order to select the appropriate provider, you have to click "New Source…"

 

Select "ODBC DSN", then click "Next >".
Select "MS Access database", then click "Next >".

Browse to the Access database that you created in the "Create the Access database to link the Excel data", above, then click "OK".

NOTE:  This browse dialog can be a bit confusing.  If you get mixed up by the unusual browsing experience, you can just type or paste the full path to the database in the "Database Name" field (boxed in green in the screenshot below).

 

Select the name of the linked table, then click "Next >"
Finish the data connection wizard as you would for any other database data connection.
 
At this point, you're good to go.  The data connection to your Excel data will behave the same as a query-only Access database data connection.


 
- Forrest Dillaway, InfoPath Test

Comments

  • Anonymous
    August 22, 2008
    PingBack from http://informationsfunnywallpaper.cn/?p=1890

  • Anonymous
    February 03, 2009
    http://blogs.msdn.com/infopath/archive/2008/08/22/consuming-excel-data-using-infopath-database-data-connections

  • Anonymous
    April 16, 2009
    One note, make sure that 'Enable browser-compatible features only' is unchecked when creating the Database template, otherwise you get database type errors. Great tutorial, thanks.

  • Anonymous
    April 06, 2011
    Thanks, JUST what I was looking for!

  • Anonymous
    June 09, 2011
    Got error "the data source contains no visible tables" when trying to add a data connection and could go no further. What did I miss?

  • Anonymous
    October 11, 2011
    The comment has been removed

  • Anonymous
    February 17, 2013
    I have an urgent requirement which is as follows: The data entered on an Infopath form is to be stored in an csv file in a predefined format. If possible please let me know at satyakee.chowdhury@bankofbaroda.com