How to Use CSV upload control

CSV upload is a custom control that helps to convert CSV data to JSON format. Le’ts see how we can use this in a possible use case.

We will be using CSV uploader control to convert CSV data to JSON format and insert data in our MySQL database.

So we will be

  1. Creating custom queries (READ and INSERT).
  2. Configuring tablegrid
  3. Setting up the INSERT button
  4. Finally bringing our CSV upload control to use

Creating custom queries (READ and INSERT)

In order to get started , we need to set a few things up. We will be creating our queries beforehand. One READ query to get all the data entries from our database and an INSERT query that we will be using to insert data in our database.

  • Navigate to Connectors > + add connector and create a custom database connector using the database of your choice.

We will be using MySQL database. Refer to articles for sql and nosql databases.

  • READ query

  • Read query will be used to get data from our database. Select the custom database created earlier and add a query. Provide READ as the Query Name.

In write your query provide

select * from dummy;

Replace dummy with your table’s name.

Test and save this query.

  • INSERT query

INSERT query will be used for inserting data to our database. Using your custom database connector create another query and name it as INSERT.

In write your query provide

INSERT INTO dummy (id,name,username,email) VALUES {{records}}

Here the records variable refers to all the rows that we will be inserting in the database. Test the query.

Once sucessful save the query.

Configuring tablegrid

From controls, add a tablegrid to the screen. Now select data > connectors and select our custom database connector. Select READ query, also select the columns you want to use and save it.

Setting up the Insert button

The insert button will be used as an actionflow to get the JSON data from our CSV upload control. Transform it in the form of MySQL rows and then save this in a variable and use it in our INSERT query.

  • Place a button control on screen and navigate to its actions > button_click.
  • Insert a JS code action and use the formula given below. What this is going to do is, convert the JSON data from our CSV upload control to MySQL rows.
  • Add a parameter and in its keywords select csvupload.
  • We will be storing the new result in our variable called records.

image4.
Test the script.

  • In the button’s actionflow, go to server side action and select your custom database connector.
  • Select the insert query and save it.

When you click the insert button, rows will be inserted in database, but changes won’t reflect on the tablegrid control, so we need to refresh it.

From on-screen actions, select reset control data , select tablegrid control and save it.

CSV upload control

  • From the left sidebar, go to controls > CSV upload. Drag and drop it on the screen
  • In properties of control, insert link to CSV file.

image2

Preview it and on pressing the button, the CSV data gets converted to JSON and inserted in the database. On refresh action the tablegrid reflects the freshly inserted data.