InfoPath – Getting data from a spreadsheet into InfoPath
Data in an Excel sheet is unstructured and often split across multiple tabs. The users I was dealing with were flexible enough to use a couple of cut-and-pastes from Excel into spreadsheet to get this option working. This is what I did to allow the users to import the data:
- Create a separate folder in the Excel spreadsheet for importing the data, called ‘SPImport’.
- In row 1, add column headings for each cell that you wish to import. This is so you can easily identify which property is in which cell
- In row 2, add a regular Excel expression to point to the cell that contains the data (examples: =Data!A16, =Data!D24 etc.). You can add some additional functions in the row to strip of unneeded characters that could cause a problem in SharePoint loads, including hidden spaces, linefeeds, forward and backward slashes, quotation marks etc.
- You will probably need to make a couple of the fields ‘key fields’, meaning that they will contain values that uniquely identify the import
- In the end you will have a single row of data that will auto populate with the referenced cells values. This spreadsheet should then become the master spreadsheet for the data
- Now, create a SharePoint list, say called ‘SPExcelImport’ that contains the same columns as your spreadsheet and the default Datasheet view in exactly the same format. You may choose to auto populate the Title field or use it to hold one of the values you intend to import.
- In your InfoPath form, create a Data Connection to the ‘SPExcelImport’ SharePoint list, provisioning all the fields
- In my form I added an ‘Import Button’ that was only enabled when I can find an entry in the ‘SPExcelImport’ list with matching key fields
- When the user clicks on the Import button, set the values in the form to the values in the import file (I was dealing with over 100 fields in my example, so I split the Action on the Import button into multiple rules)
- The user completes the spreadsheet
- The user opens the ‘SPImport’ folder, highlight the cells in Row 2 that are needed and selects Ctrl-C to copy the data (They should not highlight the entire row as this will attempt to copy all the columns in the row into the spreadsheet and the user will receive an error)
- The user opens the ‘SPExcelImport’ table with the cursor on the first cell and presses Ctrl-V to paste the copied values into the SharePoint list. This will paste the cells from the spreadsheet into the SharePoint cells (Sometime, you may find that it copies all the data into a single cell. When this happens, tab out of the cell, shift-tab back in and do the copy again). Stop editing the list
- The user then opens the InfoPath form and enters the key data in the header. InfoPath queries the ‘SPExcelImport’ list with the key data and if an entry is available, enables the import button
- The user then clicks on the Import button to import the data