Using Split Excel Column Step

Last Updated: 06/07/2018 Introduced in Version: 2.0

Using the Split Excel Column component in a Flow, the data in an Excel spreadsheet’s column that is separated by a delimiter can be split into several columns and then displayed in a data grid or converted into a CSV file. This component can also split the original columns into more columns, or combine them into fewer columns.

Split Excel columns using a custom delimiter in the Flow Designer. In the Toolbox panel, in the category Data > List > Excel and CSV, drag a Split Excel Column component to the workspace. 

Example

This tutorial shows how to create a Flow that uses the Split Excel Column component and set mapping for the component in the Mapping Editor.

To begin, navigate to a Designer Folder and click the Create Flow button.

 

 

Name the flow and click Create to open it in the Flow Designer

 

 

In the Flow Designer start-up window, we select Start Step, and using Step Information dialog, we click Add New link to add new Flow Input Data.

 

 

In the resulting pop-up window we define a Name for our Input Variable. Next, we define its Type as FileData using a search bar in the Type Picker.

 

Back in the Flow Designer we use Step Information dialog to Add Step to the Start Step in our Flow

 In the Steps panel, we expand the category All Steps [Catalog] > Integration> Excel and CSV, then select a Split Excel Column component. Click Add to add it to the workspace. 

 

 

Then, we use Steps panel to add Update File With Data step. We expand the category All Steps [Catalog] > Integration > Excel and CSV, then select Update File With Data component. Click Add to add it to the workspace. 

 

 

Next, we locate Show Form step in the Favorite Steps category in the Steps panel, and add this step next to the Update File With Data step in our Flow. We are going to use this step in this tutorial to create a simple Form for user to be able to download the updated Excel file.

 

 

In the resulting window we can define the name for our Form and click Create to proceed to the Form Designer…

 

 

In the Form Designer, we added Button component from Actions category in the ToolBox in order for our Form to have at least one outcome. We named this Button Done to reflect its purposes. Next, we add File Download component from Data > File category.

 

 

We configure our File Download component as following… We provide its Type as Button, define a Text that will be shown on the Button, and define a Name for the Variable that will hold our Updated Excel File.

 

 

Finally, our simple Form should look like following… When finished, we can save our Form and close Form Designer.

 

2016-12-12_123838

 

Back in the Flow Designer we connect the steps in our Flow. Now, we need to Map all data in the Flow. First, we select Edit Input Mapping option from Split Excel Column configurations Menu…

 

 

In the Mapping Editor we map data as following:

  • For the Input object, we use Select Value Mapping Type, and connect it to the Flow Input Data file from the Start Step.
  • For the fileType object, from the Value drop-down list, select Excel_xlsx.
  • For the column object, specify the number of columns in the file in the Value 
  • Enter the delimiter in the Value field under the delimiter object (in this case it is a spacebar character).
  • Finally, specify the number of columns that should result after the columns have been split or merged.

 

 

 

Click OK to save the mapping and close the Mapping Editor.

 

Next, we use Edit Input Mappings option to configure mapping for Update File With Data step in our Flow

In the Mapping Editor we connect input field to our File, and define its fileType as Excel_xlsx. Click Ok to save and close Mapping Editor…

 

 

Then, we use Edit Input Mappings option for our Show Form step…

In the Mapping Editor for Show Form step we simply connect our File to the File Download control Variable on our Form… Save and close Mapping Editor.

 

 

This completes our Flow Design. We can click Debug Flow link on the Top Panel of the Flow Designer to test our Flow.

 

 

Then we need to provide our Flow with an Excel File. We use Input Data picker to pick an Excel file. Then we click Run button to proceed.

 

 

Our Excel file looks like following. It has all Data (First Name, Last Name and Email Address) in one column delimited with a space character. Our Flow should divide this data into 3 separate columns.

 

2016-12-12_125034

 

Our Form opens in the Debugger. We click Download File button to download our Updated Excel file.

 

 

This initiates your browser’s File Download Process and the resulting file is downloaded.  We locate our Updated File and open it to confirm that our Data is now divided into 3 columns as we expected…

 

2016-12-12_125737

 

Next, in the Debugger we click Done button on our Form to close the Form

And our Flow runs to the End Step with no Errors

 

 

 

Additional Resources