Data Import & Export - CSV

Modeling Actions with WebRatio Platform
259 views Published on Sep 24, 2014 | Time 35 min
Applies to: 7.2 or higher

Data management is a crucial aspect for data-centric Web applications. So far, you have learned how to create, read, update and delete data through basic patterns. However, information often needs to be displayed and dealt with in different ways. Displaying data through View Components is not the only option you have in WebRatio Platform. Imagine that you have to create a warehouse of your products and you need to generate a report (as an offline document) for the sales department. Creating a report is the solution to this problem. One of the most common ways of managing and presenting a set of data is to use the comma-separated value (CSV) format. In this lesson you will learn how to read, write and simulate the update of a CSV file using the "CSV" component.

Transcript

Table of contents

The CSV Component Overview

The "CSV" component is designed to read and write CSV files. This component can be downloaded for free from the WebRatio Platform Add-ons section (note that it is not available within the set of preinstalled "Utility Components" of the WebRatio Platform). Once the Component is installed, it is placed under the "Store Components" section of items of the toolbar (just below the "Utility Components" section).

The CSV Component Properties

This component requires the configuration of a set of properties.

  • The "Name" is simply the label given to the "CSV Component".
  • The "Mode" identifies the working mode of the Component and can be "Read" or "Write" as you need.
  • The "Separator" is the character used to separate columns; when the component is used for writing, the separator is put between one column and the next; when used for reading, the row is split upon the occurrences of that separator character.
  • The "Column Order" property reflects the order of the "CSV Columns" added to the "CSV Component" both when reading and writing.

The "CSV" operation cannot work without the specification of the "CSV Columns". A "CSV Column" tells the "CSV Component" where it must read from or write into the CSV file. A "CSV Column" requires two properties to be configured.

  • The "Name" property represents the name of the "CSV Column".
  • The "Index" property represents the zero-based position of the current column.

If this property is not set or cannot be set, the order is the one of the "Column Order" property of the "CSV Component".

This lesson explains how to import and export data using CSV files as the source and target.

Importing Data from an Existing CSV File

The two CSV Files that are going to be used in the example contain, respectively: the collection of all accepted worldwide currencies (code and name); and a list of countries with the code of their main currency. Below you can see a snippet of both CSV files. The goal is to import the data stored in the two CSV files into two different entities, one for the countries and another for the currencies.

The IFML Model

The connection between "Country" and "Currency" is managed with the "currency code" reported on the "Country" object.

In our example, the persistence of the two objects, "Countries" and "Currencies", will be Volatile of Application Scope. This is how the Domain Model looks. The IFML Model provided in the project file already contains the page that shows an alphabetical list of currencies and the details of the currency for each country. It also provides pages with the "Form" Component to import the CSV Files. Let’s take a look at the "Action Definition" required to import the data into the CSV Files.
The two CSV Files that are going to be used in the example contain, respectively: the collection of all accepted worldwide currencies (code and name); and a list of countries with the code of their main currency. Below you can see a snippet of both CSV files. The goal is to import the data stored in the two CSV files into two different entities, one for the countries and another for the currencies.
The connection between "Country" and "Currency" is managed with the "currency code" reported on the "Country" object. In our example, the persistence of the two objects, "Countries" and "Currencies", will be Volatile of Application Scope. This is how the Domain Model looks. The IFML Model provided in the project file already contains the page that shows an alphabetical list of currencies and the details of the currency for each country. It also provides pages with the "Form" component to import the CSV Files. Let’s take a look at the "Action Definition" required to import the data into the CSV Files.

The skeleton of the "Action Definition" receives two BLOB files from the "Form" component, one for each CSV file, as "Input Port Parameters" and displays a message, either when it executes successfully or when it fails. The "Action" implementation uses the BLOB file to extract cell values from both the CSV files and then inserts the data as instances of the "Currency" and "Country" objects.
Let’s inspect the "Action Definition", starting from the "CSV" operations and understanding the structure of the CSV files. Both "CSV" operations will work in "Read" Mode since the content of the file will be the source of the information, and the "Source Type" must be set to BLOB since the user will directly pass the reference to the runtime files to be processed. To make this work correctly you must also define the inner structure of the CSV Files, telling the "CSV" operation where to find data defining a set of "CSV Columns". Since data inside CSV Files is separated with a special character, you need to explicitly set the "Separator" property with the character used to separate data. For both files, the separator is the comma.

The "Currency File" has two columns, one for the "Currency Code" in position 0, and the other for the "Currency Name" in position 1. Taking a look at the "Currencies" CSV File, you can see that you need to create two "CSV Columns" with their respective settings.

The "Countries File" has also two columns, the "Country Name" in position 0 and the "Country" "Currency" in position 1.

After the first "CSV Component", an "OK Flow" leads to the "Create Currencies" create operation passing all the "Currency Info". Once "Currencies" are created, another "CSV Component" is activated, this time reading the "Countries" CSV file, extracting the "Country Name" and "Currency".
Once values are retrieved, the "Create Countries" create operation is activated to create all the "Countries" stored in the CSV File. Both "Create" operations must work in "Bulk" mode since there will be more than one extracted cell.

The whole "Action Definition" core is put inside an "Operation Group" since all the operations must be executed as a single transition; "Countries" cannot be created without "Currencies". If the chain of operations ends successfully, an "OK Flow" leading to the "Success" OK Port is activated and returns a "Feedback Message", otherwise a "KO Flow" collects all the possible failures and activates the "Failure" KO Port, and returns an "Error Message". Outside the "Action Definition" context, both an "OK Flow" and a "KO Flow" lead to a "Message Component" that displays a feedback message.

How to Import Data from an Existing CSV File

Let’s continue working on our "CRM" web project in which we already have the Domain Model containing the "Country", "Currency" and "CSV Report" objects and the "Home" site view. In the "Home" site view, we have already created the "Home" page and the "CSV" area with its subareas "Import" and "Export". These areas contain the IFML model related to the CSV Data Management. Finally the project contains a "Module Definitions View" named "Action Definitions", which includes: "Read & Store [CSV]", "Read [CSV]" and "Write [CSV]" action definitions. As you can see in this project, all the action definitions are empty.

Let’s see how to model the "Read & Store [CSV]" action definition. From our "CRM" web project, open the "Home" site view and select the "Read & Store [CSV]" action from the "Import" area. Move to the Properties View and press the "Go to Action Definition" button, next to the "Action Definition" property. Now we can model the operation chain related to the "Action Definition".

Let’s start by adding the "CSV Component", previously downloaded from the "WebRatio Add-ons", to the "Action Definition". Select the "CSV Component" from the toolbar section related to "Store Components" and click in the work area. Write a meaningful name for it, such as "Currencies CSV". Then, select the "Currencies CSV" component and move to the Properties View. As you can see, by default WebRatio Platform sets the "Mode" property to "Read". In this case, it is correct and you can leave it as it is, and then, you need to set the "," value in the "Separator" property.

Now, let’s define the structure of the CSV file by adding all the necessary "CSV Column" information. Right-click on the "Currencies CSV" component, select the "Add" and then "CSV Column" option. Move to the Properties View and write a meaningful name for the "CSV Column", for example "CurrencyCode". Then let’s set the "Index" property to define the order of the columns that we need for the CSV File. For this column, set this property to "0" to specify that the "CurrencyCode" is the first column of the CSV File.

Now, let’s add the second "CSV Column", right-click on the "Currencies CSV" component, choose the "Add" and then the "CSV Column" option. Write a meaningful name for the "CSV Column", such as "CurrencyName" and move to the Properties View. Then, set the "Index" property to "1".

To state that the "CSV Component" is the first operation of the chain, let’s use an "OK Flow". Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and then bind the "CSV BLOB" parameter provided by the "CSV Component" with the "Currencies BLOB" parameter provided by the "Input Port". Then press the "OK" button to confirm.

To create the database instances corresponding to the "Currency" items read from the CSV file, let’s add a "Create" operation. Since the "Create Currencies" create operation writes many objects at the same time, its "Bulk" property must be enabled.

To state that the "Create Operation" must be executed after the "CSV Component", let’s use an "OK Flow". Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "code" parameter provided by the "Create Operation" with the "CurrencyCode" parameter provided by the "CSV Component" and the "name" parameter provided by the "Create Operation" with the "CurrencyName" parameter provided by the "CSV Component". Then press the "OK" button to confirm.

To create the "Country" items extracted from the other CSV file, let’s add another "CSV Operation". Select the "CSV Component" from the toolbar section related to "Store Components" and click in the work area. Write a meaningful name for it, such as "Countries CSV" and move to the Properties View. Leave set, even in this case, the default "Mode" property to "Read". Then set the "," value in the "Separator" property.

Now, let’s define the structure of the CSV file by adding all the necessary "CSV Column". Then let’s set the "Index" property to define the order of the columns that we need for the CSV File. "CountryName": for this column, set this property to "0" to specify that the "CountryName" is the first column of the CSV File. "CountryCurrency": for this column, set the "Index" property to "1".

To state that the "CSV Component" must be executed after the "Create Operation", let’s use an "OK Flow". To bind the information between the "Input Port" and the "CSV Component", let’s use a "Data Flow". Double-click on the "Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and then bind the "CSV BLOB" parameter provided by the "CSV Component" with the "Countries BLOB" parameter provided by the "Input Port". Then press the "OK" button to confirm.

To create the "Country" items extracted from the CSV file, let’s add another "Create Operation". Since the "Create Countries" create operation writes many objects at the same time, its "Bulk" property must be enabled.

To state that the "Create Countries" operation must be executed after the "CSV Component", let’s use an "OK Flow". Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "currency code" parameter provided by the "Create Operation" with the "CountryCurrency" parameter provided by the "CSV Component", and the "name" Parameter provided by the "Create Operation" with the "CountryName" parameter provided by the "CSV Component". Then press the "OK" button to confirm.

When the execution of these operations is successful, the "Action Definition" must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the "OK Flow" to make the binding. In the opening dialog, write a message to bind to the "Feedback Message" Parameter provided by the "Success" OK Port (for example: "Operation successfully completed."). Then press the "OK" button to confirm.

Before adding the "KO Flow" to handle the failure case, let’s enclose all the operation chains in an "Operation Group". Let’s select the components we want to add to the "Operation Group" from the "Port". You can click on each component while keeping the "CTRL" key pressed (on a PC) or the command key (on a Mac). Then, right-click on the elements and select the "Refactor" and then the "Surround with Operation Group" option. Provide a meaningful name for the "Operation Group", such as "Store Data".

Since these operations are surrounded with an "Operation Group", we can use a single "KO Flow" outgoing from the "Store Data" operation group to the "Failure" KO Port. Then double-click on the "KO Flow" to make the binding. In the opening dialog, write an error message to bind to the "Error Message" Parameter provided by the "Failure" KO Port (for example: "Error in storing the CSV files.") Then press the "OK" button to confirm.

Exporting Data into an Existing CSV File

The second part of this lesson is about how to write and update a CSV file. Let’s assume we want to produce a report listing the "name", "country" and "email" of customers. Below you can find an example of the desired output. The "Reports" will be saved in a dedicated database table, listing the "last date" modify date, the "file name" and the "content".

The IFML Model provided in the project file already contains: a page with the "Master & Details" of the "CSV Reports" giving the possibility to update an existing report, and another page to create a new "CSV Report". Between the two pages the IFML has also two "Actions", one is to read the content of an existing CSV File associated to a "CSV Report", while the other one writes the new or updated CSV File. The scenario is the following: the user creates a new CSV File and associates it to a "CSV Report". Then once the "CSV Report" is created, the user is able to add, change or remove content to the associated CSV File.

The IFML Model

Let’s first take a look at the "New Report" page and more closely analyze the "Write [CSV]" action definition. The "New Report" page contains a "Multiple Form Component" to insert data for the "Customers", and a "Form Component" based on the "CSV Report" object to manage the instance and hold the "Report Name" and "Report Key" properties. This page submits to the "Write [CSV]" Action all the values related to "Customers" and the "Report" properties.

This is the skeleton of the "Action Definition". It receives five input parameters in the "Input Port" and has a "Success" OK Port that returns the key of the "CSV Report" object that has been managed, and a "Failure" KO Port that returns an "Error Message".

The "Core" of the "Action Definition" starts with the "Create CSV File" operation that works in "Write" Mode and has "CSV Columns" describing the desired CSV File structure. It receives the "Customer" related values on the "OK Flow" from the "Input Port", and the "CSV Report Name" to rename the file as desired. Since this operation creates a new CSV File, you need to choose the character to separate each column. The choice is the comma character. When in "Write" mode, the order of "CSV Columns" cannot be defined directly from here.

After adding "CSV Columns", you need to set the specific column order inside the Properties View of the "CSV Component". The "CSV Columns" to be created are "CustomerName", "CustomerEMail" and "CustomerCountry". After creating the CSV File, the operation chain activates the "Now" time operation, used to get the current timestamp and set the last modify date for the "CSV Report" object. Then the next step is to check whether a "CSV Report" associated with the CSV File exists or not. For this purpose an "Is Not Null" operation is activated that uses the "CSV Report Key" as check. The "CSV Report Key" comes through a "Data Flow" from the "Input Port". Here, after the check on the "CSV Report Key", the operation chain forks.

If the "CSV Report" exists, it is updated. Otherwise, it is created from scratch. The "Create CSV Report" operation is activated with a "KO Flow" from the "Is Not Null" operation and receives all the required information in the incoming "Data Flows". The "Update CSV Report" update operation is activated with an "OK Flow" that also propagates the "CSV Report Key" used as input value for the "Is Not Null" operation. The other required information arrives through the incoming "Data Flows". From both "Operations", if the execution is successful an "OK Flow" leads to the "Success" OK Port setting the "CSV Report Key" as return parameter.

Outside the "Action Definition" context, if the execution is successful an "OK Flow" reaches the "CSV Report" page setting the current element of the "Report List". If something fails during the execution of the operation chain, a "KO Flow" leading to the "Failure" KO Port is activated. Outside the "Action Definition" context, if the execution fails a "KO Flow" reaches the "Error Page" setting the "Feedback Message".

How to Export Data into an Existing CSV File

From our "CRM" web project, in the "Home" site view, select the "Write [CSV]" action from the "Export" Area. Move to the Properties View and press the "Go to Action Definition" button, next to the "Action Definition" property. Now we can model the operation chain related to the "Action Definition".

Let’s start by adding the "CSV Component", previously downloaded from the "WebRatio Add-ons", to the "Action Definition". Select the "CSV Component" from the toolbar section related to "Store Components" and click in the work area. Write a meaningful name for it, such as "Create CSV File". Then, select the "Create CSV File" component and move to the Properties View. Click on the dropdown menu next to the "Mode" property and select the "Write" option, and set the "," value in the "Separator" property.

Now, let’s define the structure of the CSV file by adding all the necessary "CSV Columns". Right-click on the "CSV" component, select the "Add" and then "CSV Column" option. Move to the Properties View and write a meaningful name for the "CSV Column", for example "CustomerCountry". Repeat the same steps for adding the other "CSV Columns": "CustomerName", and "CustomerEmail".

In this case the "Index" property is automatically associated, depending on the ordering of the columns. To change the order of the "CSV Column", select the "CSV Component", move to the Properties View, press on the "Sort" button next to the "Column Order" property, and use the "Up" and "Down" buttons to change the order. For example, move the "CustomerName" column into the first place. Then, press the "OK" button to confirm.

To state that the "CSV Component" is the first operation of the chain, let’s use an "OK Flow". Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property. You can use the "Guess Binding" for the "Column CSV" items and the "Output Parameters" that have the same name. It guesses the binding comparing the names of input and output parameters. And finally bind the "CSV Report Name" parameter provided by the "CSV Component" with the "CSV File Name" parameter provided by the "Input Port". Then press the "OK" button to confirm.

To get the current timestamp and set the last modify date for the "CSV Report" object, let’s use a "Time Component".

To state that the "Time Operation" must be executed after the "CSV Component", let’s use an "OK Flow".

To check whether the CSV File exists or not, and then to decide whether to create or update the CSV File , let’s use an "Is Not Null Operation".

To state that the "Is Not Null Operation" must be executed after the "Time Operation", let’s use an "OK Flow". Let’s use a "Data Flow" to bind the information between the "Input Port" and the "Is Not Null Operation". Double-click on the "Data Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "Input" Parameter provided by the "Is Not Null Operation" with the "CSV Report Key" parameter provided by the "Input Port". Then, press the "OK" button to confirm.

To create a new "CSV Report", let’s use a "Create Operation".

To state that the "Create Operation" must be executed after the "Is Not Null Operation" when the "CSV Report" does not exist, let’s use a "KO Flow".

To provide the information to the "Create Operation", we will use three different "Data Flows" connected from the previous Components. Select the "Flow" item from the toolbar section related to "Flows" and click on the "Input Port" and then on the "Create Operation". Double-click on the "Data Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "file name" parameter provided by the "Create Operation" with the "CSV Report Name" parameter provided by the "Input Port". Then, press the "OK" button to confirm.

Now select another "Flow" item from the toolbar section related to "Flows" and click on the "CSV Component" and then on the "Create Operation". Double-click on the "Data Flow" to make the binding. In the corresponding dialog, uncheck the "Enable Default Binding" property and bind the "content" parameter provided by the "Create Operation" with the "CSV BLOB" Parameter provided by the "CSV Component". Then, press the "OK" button to confirm.

Finally, again select the "Flow" item from the toolbar section related to "Flows" and click on the "Time Component" and then on the "Create Operation". Double-click on the "Data Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "last access" parameter provided by the "Create Operation" with the "Current/Input Timestamp" parameter provided by the "Time Component". Press the "OK" button to confirm.

When the execution of the "Create Operation" is successful, the "Action Definition" must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "CSV Report Key" parameter provided by the "Success" OK Port with the "oid" parameter provided by the "Create Operation". Press the "OK" button to confirm.

To update an existing "CSV Report", let’s use an "Update Operation".

To state that the "Update Operation" must be executed after the "Is Not Null Operation" when the "CSV Report" already exists, let’s use an "OK Flow". Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "Key Condition" parameter provided by the "Update Operation" with the "Input Value" parameter provided by the "Is Not Null Operation". Then, press the "OK" button to confirm.

Let’s use two "Data Flows" to provide the information to "Update Operation".

Select the "Flow" item from the toolbar section related to "Flows" and click on the "CSV Component" and then on the "Update Operation". Double-click on the "Data Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "content" parameter provided by the "Update Operation" with the "CSV BLOB" parameter provided by the "CSV component". Then, press the "OK" button to confirm.

Now, select another "Flow" item from the toolbar section related to "Flows" and click on the "Time Component" and then on the "Update Operation". Double-click on the "Data Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "last access" parameter provided by the "Update Operation" with the "Current/Input Timestamp" parameter provided by the "Time Component". Press the "OK" button to confirm.

When the execution of the "Update Operation" is successful, the "Action Definition" must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property and bind the "CSV Report Key" parameter provided by the "Success" OK Port with the "oid" parameter provided by the "Update Operation". Press the "OK" button to confirm. Before adding the "KO Flow" to handle the failure case, let’s enclose all the operation chains in an "Operation Group".

Let’s select the operations we want to add to the "Operation Group" from the "Port". You can click on each component while keeping the "CTRL" key pressed (on a PC) or the command key (on a Mac). Then, right-click on the elements and select the "Refactor" and then the "Surround with Operation Group" option. Provide a meaningful name for the "Operation Group", such as "Write CSV Report".

Since these Operations are surrounded with an "Operation Group", we can use a single "KO Flow" outgoing from the "Write CSV Report" Operation Group to the "Failure" KO Port. Then double-click on the "KO Flow" to make the binding. In the opening dialog, write an error message to bind to the "Error Message" parameter provided by the "Failure KO Port (for example: "Error in writing the CSV files.") Then press the "OK" button to confirm.