Loading an Excel File into a Flow

Last Updated: 09/26/2016 Introduced in Verision: 2.0

The Import Excel or CSV flow component loads tabular data from CSV or Excel-formatted files. The function of this step is to import an excel or csv file into a data type. The assumption is that the datatype you are importing into has properties that correspond to the columns of the excel file.

For example, a CSV file has 2 columns, First Name and Last Name. To process this file, it is necessary to first create a data type with two attributes (string type) and then map the columns to these two attributes. This makes it possible to use the output later in a flow. If the CSV has rows, the output will be an array of items.

Another example is a “CorporateLocations” datatype with the following fields:

City (String)
State (String)
NumberOfEmployees (String)

You are given a spreadsheet of houses in the area that you need to process. To do so, use the Import Excel or CSV step to import them into an array in a flow. The spreadsheet looks like:

City	State	# of Employees
San Francisco	CA	90
St. Louis	MO	80
Sacramento	CA	40
Los Angeles	CA	50
Denver	CO	100
New York	NY	800

Configure the Import Excel or CSV step to output datatype of CorporateLocations and would configure the step so it knows there are three columns to import. The data type of the column is determined by the data type of the corresponding target property. In this example you configure:

Column 1 to map to the City property and since City is a String in your data type, the step will try converting the value it finds in column 1 to a String.
Column 2 would map to State so it will try converting the value it finds to an String.
Column 3 would map to the NumberOfEmployees property (Int32), so it will use the Int32 value of the column to populate that property.

To use the Import Excel or CSV component, in the Flow Designer, drag it to the workspace from the Toolbox panel Data > List > Excel and CSV category. In the Properties panel, configure the imported tabular data under the Input Data section, and the data structures to be outputted under the Output Data section.

Example

This example will import a list of corporate office locations from an Excel file and output them as string data.

To correspond with this Excel file, a simple flow structure data type is created: City (string), State (string), and NumberOfEmployees (int32).

Begin in the portal by navigating to our Designer Folder and clicking the Create Flow button. Name the new flow and click OK to open it in the Flow Designer.

In Flow Designer start-up window locate an Import Excel or CSV component from the All Steps[Catalog] > Data > List > Excel and CSV category. Select and click Add to add step to the workspace.

Next, configure this component’s input data in the Properties panel.

The first row of our file contains column names, so we will start our data collection on the second row by typing “2” in the Start On Row field. It is necessary to import the first 3 columns of data from our tabular data file, so type “3” in the Number of Columns field.

Next, configure the structure in which our imported data should be represented in the system. In the Output Data section, click the Output Data Type selector.

For the example, the data of corporate locations will use the CorporateLocations data type. In the resulting Select Entity pop-up, browse to and select CorporateLocations and click OK.

A new sub-section called Mapping appears within the Output Data section. Because we chose to recognize 3 columns of tabular data, three invalid columns appear. Select the first column and click the Edit button.

In the resulting Edit object pop-up, notice this column’s mapping is invalid because we have yet to map it to a property of our output data type – CorporateLocations. To fix this, under Property, select City in the Name drop-down list. Click OK to close the Edit object pop-up.

Configure the remaining two columns, State (string) and NumberOfEmployees (int32) so they show valid.

The Import Excel or CSV step will use a specific file. In the Inputs > File Data section, select Constant in the Mapping type drop-down list, and then click the Value selector.

Browse to the file of corporate locations and click Open to load the file as input.

Finally, connect the outcome paths of our flow. In a production scenario, we would typically connect the exception paths to exception handling steps, but for the sake of our example, connecting all outcome paths to the End Step will complete our flow. Browsing the data from the End step shows the variables available now from the Excel file.

Loading an Excel File into a Flow

Example

Additional Resources