Import data from Excel 2010 into InfoPath 2010
Learn how you can import Excel data into InfoPath 2010 by creating an XML mapping in Excel 2010 to be able to save the spreadsheet as an XML file and then use it in InfoPath 2010.
There are two ways to import Excel data into InfoPath 2010:
- You could save an Excel file as an XML file and then create an XML data connection in InfoPath to that XML file; or
- You could export the Excel file to a SharePoint list and then create a data connection in InfoPath to that SharePoint list.
This article uses the first method to import Excel data into InfoPath 2010 through an XML file.
The first step to be able to import Excel 2010 data into InfoPath 2010 is to save the Excel file as an XML file. But before you can do this, you must create an XML mapping in Excel by mapping the data in Excel to XML elements in an XML schema definition (XSD).
Here you are going to misuse InfoPath to create an XML file that Excel can use to automatically extract the XML data source and use it for the XML mappings in Excel.
Suppose you have an XML spreadsheet with names and colors of Microsoft Office applications, for example:
To be able to import such data into InfoPath, it must have a repeating structure. The quickest way to create an XML file that has a repeating structure without manually having to type in the XML yourself is as follows:
- Open InfoPath 2010 and create a new Blank Form template.
- Add a Repeating Table control with 2 columns to the form template.
- Rename the fields in the repeating table to title and color.
- Rename group2 to application, and then move the application repeating group to a location under the myFields group by right-clicking the application repeating group and selecting Move from the drop-down menu that appears.
- Rename the myFields group to OfficeApplications, and delete the group1 group. The final data source for the InfoPath form should resemble the following figure:
Figure 1. Repeating group in the Main data source of the InfoPath 2010 form template.
- Preview the form.
- When the form opens, click Insert item to add a second row to the repeating table.
- Click Save to save the InfoPath form locally on disk. Rename the file to OfficeApplications.xml when you save it.
- Close InfoPath 2010 without saving the form template.
Once you have an XML file with the structure for a repeating group, you can use this XML file in Excel as follows to create an XML mapping in Excel 2010:
- In Excel 2010, open the Excel spreadsheet you want to import into InfoPath 2010.
- If the Developer tab is not currently visible in Excel 2010, click File > Options.
- On the Excel Options dialog box, click Customize Ribbon on the left side, and then in the Main Tabs list on the right, select Developer, and click OK. The Developer tab should now be visible in Excel 2010.
- Click Developer > XML > Source.
- On the XML Source task pane, click XML Maps.
- On the XML Maps dialog box, click Add, and browse to and select the OfficeApplications.xml file you saved earlier.
- Click OK when Excel prompts to create a schema based on the XML source data.
- On the XML Maps dialog box, click OK.
- On the XML Source task pane, click the ns1:application repeating group. This will automatically also select the ns1:title and ns1:color fields under the repeating group.
Figure 2. XML Source task pane in Excel 2010 to create XML Mappings.
- Right-click the ns1:application repeating group and select Map element from the context menu.
- On the Excel spreadsheet, select all of the rows and columns that make up the data you want to import, and then on the Map XML Elements dialog box, click OK.
- Click File > Save As.
- On the Save As dialog box, browse to a location where you want to save the XML file for the Excel spreadsheet, enter a suitable File name, select XML Data (*.xml) as the Save as type, and click Save.
- Click Continue if Excel displays a warning for losing the worksheet features.
- Close Excel.
Now you can add an InfoPath XML data connection to the XML file containing the Excel data in InfoPath 2010 and then use the secondary InfoPath data source to the Excel data to fill a drop-down list box or a repeating table or whatever you wish.
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.
Related InfoPath Articles:
- Display Excel data in InfoPath via SharePoint or XML connections
- Integrating InfoPath with Excel and Excel Services in SharePoint overview
- Publish Excel 2007 data to SharePoint then display it in InfoPath
- Create an Excel 2010 spreadsheet from InfoPath repeating table data in SharePoint
- Auto-fill InfoPath fields with Excel data in SharePoint 2013