Append CSV File Using List To CSV Mapping StepLast Updated: 07/15/2016 Introduced in Verision: 2.0 |
This tutorial shows how to use CSV To List Mapping and List To CSV Mapping steps from Data > List > Excel and CSV category in the ToolBox.
Example:
In this Example we are going to create a Flow that converts a CSV file to an Array of Strings. The list of Rows from the CSV file will be displayed on the Form where user will have an option to add more Rows to this List using another Form. Every time user adds a new Row first Form that displays a List of all Rows will be updated. When User finished adding Rows, he or she will be prompted with a new Form where he or she will be able to download a new Updated CSV file. The Flow should convert a List of coma separated String values into CSV file using List To CSV Mapping step and keeping 3 columns format in the Row.
We begin in the Designer Folder with clicking Create Flow button from Folder Actions panel.
Next, we Name our Flow and click Create to proceed to the Flow Designer.
First, we need to create a Data Definition in our Flow that will hold our original CSV file. We add Create or Copy Data step from All Steps [Catalog] > Data category in the Flow Designer Wizard.
In the resulting pop-up window we click Add New link to add a Data Definition.
In the next Add DataDefinitions window we Name our Data Definition and define its Type as FileData using Type selector. Then, we save and close this pop-up window.
Our Data for the File is defined and we can click Add to continue.
Next, we are going to need to convert our CSV file into an Array of Strings. For these purposes we add CSV To List Mapping step from All Steps [Catalog] > Data > List > Excel and CSV category, and place it next to the Create or Copy Data step on the workspace.
Then, we need to pick a CSV file to our OriginalFile data definition. CSV that we will use in this example looks as following. It has 3 columns: First Name, Last Name, and Email Address.
We select our Create or Copy Data step on the workspace. Using Step Information Layer we locate Inputs section. Then, using Value picker for the OriginalFile we select and add our CSV file.
Next, we need to configure CSV To List Mapping step. We select this step on the workspace and Step Information Layer opens. We expect a List of Strings outcome from this step, therefore we select its Outcome Type as String in Data section.
Next, in the Input Data section we select Csv from Input File Type dropdown.
In Inputs section we use Select Value for InputFile Mapping type. Then we use Path picker to map this field to our OriginalFile.
To finish configuration for this step we select Show Item Mapping option from step’s Options Menu.
In the Item Mapping Editor we select TextMerge.Plain Mapping type for ITEM field.
Then, we click Show Editor button to open Text Merge.Plain Editor.
In the Merge Text Editor we expand INPUT category and using TextValues from Fields we build a coma separated String. Click Ok to save and close Merge Text Editor. We can save and close Item Mapping Editor to return to the Flow Designer.
Next, we are going to implement in our Flow a logic for user interface. First, we add Add Items To List Step from All Steps [Catalog] > Data > List category in the Flow Designer Wizard. This step will use the original List of Rows and update it with all new Rows that the user adds.
Then, we add First Form next to Add Items To List Step. This Form will display the List of Rows in the Array of Strings. We use Show Form step from Favorite Steps in the Flow Designer Wizard for this purposes.
In the resulting pop-up window we Name our Form and click Create to proceed to the Form Designer.
In the Form Designer we add two Button controls from Actions category in the ToolBox. One Button will serve for adding new Row and we name it Add Person. Another will serve for submiting new List and we name it Done. Next, we add Simple List component from List category in the ToolBox. This component will display a current List of Rows.
We configure Simple List component as follows. In the Input Data section we define List Data Items Name and for Type we select String. In the Output Data section we define Selected Item(s) Data Name and check desirable Outcome Scenarios with our Button controls.
Our Form Design should look like following. We can save the Form and close Form Designer.
Back in the Flow Designer we are going to add another Form on Add Person outcome from our First Form. Similarly to the previous step, we use Show Form step from Favorite Steps in the Flow Designer Wizard. This Form will be used as a part of user interface for adding a new Row to the List.
In the resulting pop-up window we Name the Form and click Create to proceed to the Form Designer.
Our Form Design is simple. It has a Title implemented with a Label component from Data category in the ToolBox. The Body of the Form has 3 Labels indicating the required information from the user. There are also 3 Text Box components from Data category in the ToolBox. These Text Boxes are named respectively to the field they represent. Additionally, each Text Box is checked for Output Only, so each time the user calls this Form Text Boxes should be empty. Finally, there are two Button components from Actions category. One Button serves for user to be able to return to the Display Form. Another Button serves for user to be able to Add new Row to the List. The Outcome Scenarios for the Text Boxes are Required for Add button and Optional for Back button (for not raising validation errors if user leaves one of Text Boxes empty and decides to return to the Display Form). When finished design, we can save the Form and close Form Designer.
Back in the Flow Designer we add Add Item To List Step from All Steps [Catalog] > Data > List in the Flow Designer Wizard on the Add outcome from our second Form. This step will record user inputs from the Add Form, create a new coma separated Row, and add this Row to the List of New Rows.
Next, we need to create a List that will hold all New Rows added by the user. For these purposes we add Create or Copy Data step from All Steps [Catalog] > Data category in the Flow Designer Wizard.
In the resulting pop-up window we click Add New link to add Data Definition.
In the Data Definition window we provide our Data with Name, select String for its Type, and check Is List and Can Be Null checkboxes. Then, we save and close this Data Definition window.
Our Data Definition for the List of New Rows completed. We click Add to continue.
Next, we connect steps in our Flow as following. The outcome from the Create or Copy Data step to the Add Items To List Step, and Back outcome from Add Person Form to the Display Data Form.
Now, we need to map all Data in this part of our Flow. We select Add Items To List Step on the workspace and select Show Mapping Editor option from step’s Options Menu.
In the Mapping Editor we map the OUTPUTS (original List) to the Original List field, and CurrentList (List of New Rows) to the New Items field. Additionally, we Rename the Outcome Data Name. Then we save and close Mapping Editor.
Then, we call Mapping Editor for our Display Data Form…
In the Mapping Editor we use the Outcome from Add Items To List Step for our ListOfPersons Simple List component. We can select Ignore Mapping type for SelectedPerson field. Click Ok to save and close Mapping Editor.
Next, we Show Mapping Editor for Add Item To List step…
For the New Item field we are going to use Text Merge.Plain Mapping type because we need to build a coma separated Row from the user inputs in Add Person Form.
We click Show Editor button to open Merge Text Editor for the New Item field.
In the Merge Text Editor we use the outcome Data fields from Add Person Form to build a coma separated Row. Click Ok when finished.
Next, we use our CurrentList (List of All New Rows) for Original List field. Again, we rename Outcome Data Name. Then, we can save and close Mapping Editor.
Next, we call Mapping Editor for the Create or Copy Data step where we defined a List of New Rows.
In the Mapping Editor we map data from the outcome from Add Item To List Step to our CurrentList. Then save and close Mapping Editor.
Now, we are going to build a logic for our Flow to convert a New List Of Items into CSV file.
First, we are going to iterate through the New List Of Items. We add ForEach Step from All Steps [Catalog] > Flow Management category in the Flow Designer Wizard. We connect this step to the Done outcome from our Display Data Form.
We call Mapping Editor for ForEach Step to map the collection to this step.
In the Mapping Editor we connect New List Multiple Items string array to the Collection field. Additionally, we Rename Item Outcome name. Then, click Ok to save and close Mapping Editor.
Back in the Flow Designer on the Next outcome from ForEach Step we add Split String step from All Steps [Catalog] > Data > Text category in the Flow Designer Wizard. With this step we are going to split each coma separated Row in the Collection into three Fields: First Name, Last Name, and Email Address.
We select Show Mapping Editor for Split String step…
In the Mapping Editor we define coma as a Constant Value for Separator. Next, we map a SingleRowForCsv outcome from ForEach Step to the source field. In the Outputs from Split String we change output Type from Rename to Array – Items.
Next, we define that we are going to output three items from the Array. We provide each Item with Data Name. Then, we save and close Mapping Editor.
Back in the Flow Designer we add another Create or Copy Data step next to the Split String step…
Again, we click Add New link to create a new Data Definition…
In this step we are going to create a single CSV Row. We Name our Data Definition and define its Type of CSVRowData. Save and close this Data Definition window.
When our Data Defined we click Add to continue…
Then, we Show Mapping Editor for this Create or Copy Data step…
In the Mapping Editor we use Build Data Mapping type for the CsvRow.
Next, we map the outputs from the Split String step to the Fields in our CsvRow. Click Ok to save and close Mapping Editor.
Back in the Flow Designer we add another Create or Copy Data step. This step will collect and keep all the CsvRows.
In the Data Definition window we click Add New link…
Next, in the resulting pop-up window we Name our Data, select CSVRowData for its Type, and check Is List and Can Be Null checkboxes. Then save and close Data Definition window.
When Data is defined, we click Add to continue.
Next, we connect the outcome from the last Create or Copy Data step to the ForEach Step. We call Mapping Editor for the last Create or Copy Data step.
In the Mapping Editor we use Join Arrays Mapping type for Rows.
First, we connect Rows (outcome from this Create or Copy Data step) to the First Item in Rows. Then, we use Add Item option to add a new Array to Join.
We map a CsvRow outcome from previous Create or Copy Data step to the First Item in Second Array. In this manner this Array of Rows will be collecting each Row created by CsvRow Create or Copy Data step. We save and close Mapping Editor.
We have collected a List of CSV Rows for our new CSV file. Now we can create a new CSV file from the collection of Rows that we have. We drag List To CSV Mapping step from Data > List > Excel and CSV category in the ToolBox. In the Properties for this step we define Input File Type – Csv.
Next, we connect List To CSV Mapping step to the Done outcome from ForEach Step. We select Show Mapping Editor for List To CSV Mapping step.
In the Mapping Editor we connect our Rows to the INPUTS field. Click Ok to save and close Mapping Editor.
Then we select Show Item Mapping Editor from List To CSV Mapping step’s Options Menu.
In the Item Mapping Editor we connect _INPUT single Row from the collection of Rows to the ITEM field. Save and close Item Mapping Editor.
The logic for our Flow is completed. We need to add another Form where user will be able to Download an Updated CSV file. We drag Download File Form from Forms [Interaction] > [Current Folder] category in the ToolBox.
This Form was predesigned for this tutorial. It has only two controls on it.
- First: Button from Actions category. Designed for Form to have at least one Outcome.
- Second: File Download control from File Handling category. Designed for file downloading.
When we finished, we can save the Form and close Form Designer.
Back in the Flow Designer we connect the Outcome from Download File Form to the End Step in our Flow. Then we Show Mapping Editor for our Download Form.
In the Mapping Editor we connect OutcomeFile from List To CSV Mapping Step to the File Download control on our Form. Then, we click Ok to save and close Mapping Editor.
This completes our Flow. To test it we click Debug Flow link on the Top Panel of the Flow Designer.
Note that in Decisions version 3.5 and above, you’ll need to click Test Flow to access the Debugger.
Our Display Data Form opens in the Debugger. Simple List component displays all Rows of data from our original CSV File. We click Add Person on the Form to add new Row.
Our Add Person Form opens. We fill in the fields and click Add…
Display Data Form opens again with the new Row we have just added. We click Add Person to add another Row.
Fill in the fields in Add Person Form again and click Add…
We can observe that our Simple List component on the Display Data Form updates with new Data as expected. We click Add Person again.
This time we can fill in some fields on Add Person but click Back to discard changes and return to the Display Data Form…
We can observe that our Display Data Form behaves as designed. The Simple List component still displays an updated with two persons List of Rows. We click Done to proceed to the Download File Form.
In the Download File Form we click Download File.
Next, we Name the File and Save it at the desirable location…
Debugger informs us that the File was Saved…
We click Done on the Download File Form in the Debugger to close the Form.
Diagram Tab in the Debugger indicates that our Flow ran to the End Step with no errors.
We can locate and open our Updated CSV file. As was designed, our Data in Rows is divided into three columns. Two Rows of Data are added to the original CSV file.
