CSV ( Comma Separated Values) is a table storage format in which cell values are separated by the “,” symbol. Instead of “,” separator can be used another character. Although, AppMaster requires a comma.
It is important to ensure that this character does not occur inside the cell. Otherwise, the file data will be read incorrectly.
First, create a data model where information from the CSV file will be saved. For example, let's make a data model for flights. It will contain the following fields:
- flight_no - flight number, in integer format;
- destination in string format;
- classes - flight class available for this flight, array[string] format.
An example of a file that will be imported:
The first column contains flight_no, the second contains destination, and the third contains classes.
In the third column, the values are separated by a semicolon.
Create a business process
Go to the Business Logic tab and click on the Create Business Process button.
This business process needs a CSV file, so add the file field to the Start block.
Next, we need the Read CSV file block.
Similarly, you can use the Read XLS file and Read XLSX file blocks for the corresponding formats.
How the Read CSV file block works:
It has two output connectors: Each Row and Completed. When the block is activated, it will read the file line by line, and each time it will activate the Each Row connector and pass the row number (Row number, starting from 1) and Row Columns a string array of all columns of the current row.
Pass the file from the Start block to the Read CSV file block:
Next, add the For Each Loop block. It will process the string array that sends the Read CSV file block.
For Each Loop, just like the Read CSV file, processes each element of the array and activates the Loop Body connector for each of them.
The index output field in the For Each Loop block is the index number of the array element. It starts from 0.
Connect all the connectors:
The next block is Switch. With its help, we will determine where to write the values from different fields.
Switch can accept any type of input and, depending on it, can activate different output connectors. The Default connector is activated if the input value does not match any of the created connectors.
The CSV file has three columns, which means there are three values stored in each Row columns array, always in the same order:
- number;
- direction;
- classes.
Therefore, we will create three output connectors in the Switch block 0, 1, and 2 and pass the index from the For Each Loop block to the Switch block.
The first column in the file contains the flight number. But the For Each Loop block gets it in the string format. Therefore, we convert it to an integer using the To integer block before saving. After that, save it to a variable using the Set variable block.
The second column in the file contains the direction in string format. It can be immediately saved to a variable.
The third column in the file contains a list of classes separated by semicolons. The For Each Loop block also gets it in the string format. So split it into an array of strings. To do this, we use the Split String block. Let's pass a string with a list of classes into it and set ";" as the default separator.
Save the received value into a variable.
A loop setup through all the column values in each row is finished.
We need to create an entry from each line in the file. We use the completed output connector in the For Each Loop block and the Make flight block to do this. Let's transfer all the variables received during the cycle to it.
Now save the model created in Make flight using the DB Create flight block.
Thus, each row is saved as a record in the database.
Now we need to finish processing the file. We will use the completed output connector in the Read CSV file block and connect it to the End block.
The BP to import from a file is finished.
Endpoint setup
Go to the Endpoints tab and create a new endpoint.
Select the POST method, set the URL address, select the Flight group, and the created BP Import CSV.
Setting up a button on the frontend
Now we need to set up a button on the front end to upload the file.
Drag the File picker element onto the canvas.
And set up the BP for it:
Use the onSelectfiles trigger. This trigger receives files as an array, even if there is only one file. Therefore, we will use the Array Element block to get one element from the array. Let's set the index field to 0 to get the first element from the array.
Now save the uploaded file to the database using the Server request POST /_files/ block.
Expand the resulting file using the Expand file block.
Now pass the ID (which in this case is a file) to the Server request POST /flight/csv/ block.
