Using Split Excel Column StepLast Updated: 08/11/2016 Introduced in Verision: 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 Wizard, 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.
Our Flow Input Data definition should look like following… We press Ok to save and close this window.
Back in the Flow Designer we use Step Information Wizard to Add Step to the Start Step in our Flow…
In the Flow Designer Wizard, we expand the category All Steps [Catalog] > Data > Lists > Excel and CSV, then select a Split Excel Column component. Click Add to add it to the workspace.
Then, we use Flow Designer Wizard to add Update File With Data step. We expand the category All Steps [Catalog] > Data > Lists > 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 Flow Designer Wizard, 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 File Handling 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.
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 Show Mapping Editor 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 Show Mapping Editor 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 Show Mapping Editor 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.
Note that in Decisions version 3.5 and higher, you’ll need to click Test Flow to access the Debugger.
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 Without Save 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.
Our Form opens in the Debugger. We click Download File button to download our Updated Excel file.
Confirm that we want to Save our file…
Next, we choose a location for the new Excel file, Name it and Save it…
In the Debugger, Decisions informs us that our file was saved… We click Ok to close this pop-up…
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…
We locate our Updated File and open it to confirm that our Data is now divided into 3 columns as we expected…
