Importing Excel data into InfoPath

There is always a need to create a InfoPath forms from Excel. Now this can be bit more trickier. Again there are be couple of different approaches:

1) Using workflow activities to copy List data to InfoPath forms: First part of this exercise is to create a SharePoint List from a Spreadsheet. Second part of the process is to generate and update the InfoPath forms in a form library. You can accomplish this by following steps:

a) You will have to prepare the form library to take data. Specifically you will have to promote all the fields in the form that needs to be updated as site columns. This has some serious performance issues.

b) Create a black InfoPath by hitting new button on the form library.

c) Download it on your local machine by choosing “Download a Copy” option in the listitem menu.

d) Make multiple copies of this form (one per record in your spreadsheet).

e) Rename and upload the forms. This is most effectively done with the “Explorer view” of form library. Other options is to use “Upload Multiple Files” from list menu or in SharePoint Designer.

f) Now Write a SPD workflow for the SharePoint list which was created by getting data from spreadsheet and coping it over to the corresponding column in the Form Library (that is the reason to expose the column). You can use “Update List Item” activity to do this.

g) Trigger the update on each row of SP list so it copies the data in the InfoPath form.

Now there can be some serious issue if you can more than 40 columns in the sheet. Limitation occurs with Form Library promoted columns. As you promote more and more columns it will take more time to publish the form template. The web service that publishes the Form Templates starts to timeout and you will not be able to publish the forms.

There is a CodePlex Activity “Set InfoPath field inner text” available to get around this issue. This activity basically allows you to copy text directly into the form.

2) I have not tested this approach but this can be every simple thing to do. I came across Qdabra’s Excel to InfoPath Migration Tool . This tool looks quit promising to me as it uses very simple approach to generate the xml per record in the Spreadsheet. Mapping also looks easy it uses one xml file to map excel sheet columns to Form fields.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: