Skip Ribbon Commands
Skip to main content
SharePoint

Automatically Populate Data Item Values with Excel Worksheet Column Values

To automatically populate data item values with Excel worksheet column values, complete the following steps:
  1. Check out and open the process you want to edit.

  2. In the Toolbox pane, click Microsoft Office. A list of activities is displayed.

  3. Double-click or drag the Read Excel Workbook activity into the process.

  4. Link the activity appropriately with other activities in the process. 

  5. Right-click the Read Excel Workbook activity. A shortcut menu is displayed.

  6. Select View Properties. The Read Excel Workbook window is displayed.

  7. Click the Input File tab. The corresponding screen is displayed.

  8. In the Input File Location field, select the appropriate input field editor and set the field to a value that is a location where the workbook to be read is stored.

    Note:
    If your input template is referenced by a data item, click Configuration values and enter a valid connection string for design-time, configuration purposes.


  9. In the Authentication section, select Run as BPM Server or Specify credentials.

    Note:
    If you select Run as BPM Server, access will be granted or rejected based on the BPM Server credentials.
    If you select Specify credentials, complete the Username, Password, and Include domain fields using the appropriate input field editors. The Include domain check box is optional and requires you to select the domain name, if selected.


  10. Click the Data Mappings tab.  The corresponding screen is displayed.

  11. Select the Automatically Map Workbook Contents to Data Item Values check box.

    Note:
    If the Manually Map Form Control Values to Data Items check box is also selected, then those mappings will override any automatic mappings that the Process Engine finds.


  12. In the Worksheet name field, select the appropriate input field editor and set the field to a value that is the name of the worksheet to be read.

  13. From the Direction to read values options, select Read by column.

    Note:
    To store multiple column values to a collection data item, select the Select Collection Data Item Member for Mapping check box.


  14. Complete the following:

    a. In the Data Item name column field, select the appropriate input field editor and set the field to a value that is the name of the column that will contain the data item name.
    b. In the Value column field, select the appropriate input field editor and set the field to a value that is column name that will contain the values to copy into the data item.
    c. In the Start row field, select the appropriate input field editor and set the field to a value that is the row name from where the values will be copied.

    Note:
    Calculation data items are read-only. You cannot edit this type of data item.

     
    Note:
    Selecting the End row check box is optional. If selected you must set the field to a value that is the number of the row where the copying of values ends.


  15. Click OK. You configured the activity to automatically populate data item values with Excel worksheet column values.
Related Topics
Automatically Populate Data Item Values with Excel Worksheet Row Values
Manually Determine which Data Item Values to Populate with Excel Worksheet Column Values
Manually Determine which Data Item Values to Populate with Excel Worksheet Row Values
Populate Data Items with Excel Workbook Properties
Read Values from a Password-protected Workbook and Worksheet

Source
BPM Designer Topics > Overview of Activities and Activity Topics > Microsoft Office Activities > Read Excel Workbook
Last modified at 7/31/2020 11:16 AM