Upload CSV File on Node-RED Dashboard and Insert into PostgreSQL database

in this video I'll demonstrate how to upload a CSV file and record its data into a poster SQL database using the node red dashboard let's dive in and take a look this is an example CSV file the example consists of usernames with various data that will be recorded this is node red dashboard to upload CSV file and modify the data in the dashboard there's a button to upload the file and a play button icon to start recording data from the CSV file into the database the data that has been recorded in the database will be displayed in a table in addition to performing insert queries to record data we can also perform data updates click on the data in the table and the data will populate the form below then you can edit the form and click the update button to update the data additionally we can also delete data here are the required nodes used on the dashboard such as the node red dashboard UI table poster SQL node for database connection and contri loop for creating loops to upload a CSV file in the node red dashboard we need several nodes including the node red contrib UI upload node in the manage pallet section go to the install tab search for the keyword upload and find the node name node red contrib UI upload then click on install and wait for the installation to complete to learn more about how to use this node please visit the node red flows page for detailed instructions and examples the next node that needs to be installed is node red contrib chunks to [Music] lines in the manage pallet section go to the install Tab and search for the node with the name chunks select node red cont trib chunks to lines then click install to proceed with the [Music] installation this is an example CSV file that will be uploaded it consists of columns for username user ID first name and last name at the bottom there are five pre-filled data entries that we will upload to record the data into the database this is the node red dashboard that has been created consisting of three groups upload CSV which contains nodes for uploading CSV files view data which will display data in a table and update data which consists of a form for updating data let's try uploading by clicking the upload button on the dashboard then select the CSV file and click open next click the play icon button to insert data into the database from the CSV file then in the view data group a table will appear displaying the data from the database to edit data click on the data in the table and the form below will populate with the selected data then you can modify the data in the form and click the update button to change the data in the [Music] database click the refresh table button to refresh the data displayed in the table you will be able to see that the data you edited has changed according to what was filled in the form just now next let's try deleting data by selecting the data to be deleted in the table then click the delete data button to delete it the data will be removed from the database and you can view the updated data by clicking the refresh data [Music] button [Music] after seeing the node red dashboard let's take a look at its flow code to create the dashboard we just saw starting from the upload node configuration in this node the accepted file types are CSV and for transfer type select binary next next connect from the upload node to the chunks to line node to read each row in the uploaded CSV file then connect it to the CSV node to convert between a CSV formatted string and its JavaScript object representation in either direction select the semicolon as the separator next connect it to a function node to create a message called data set with its value being the payload generated from the CSV node next use a loop node to iterate over the values from the data set one by one to perform insert queries into the database next add a change node to get the values from each row in the data set message next to avoid recording duplicate data I'll first check if there's already existing data with the username and user ID to be recorded if duplicate data is found the data from the data set won't be processed for the insert query into the database if the data already exists I'll generate a notification indicating that the data is already exist if the data is not found then proceed with the insert query process using the poster SQL node for the data available in the data set [Music] payload [Music] next connect to a node for executing a select query to retrieve data from the database the select query node will also be triggered by a button for manual data refresh from the select query node the payload will be sent to a function node for processing as an array which will then be connected to a table node to display the data in a table when a row in the table is clicked I will save the values for the username and user ID to flow variables these these variables will be utilized later for updating and deleting data Additionally the selected username and user ID from the table will be displayed in a visual text element to indicate the chosen values the selected data from the table will also be displayed in a form containing fields for username user ID first name and last [Music] name when the update button on the form is pressed it will then proceed to a change node to retrieve the values filled in the form and it will also fix the values of username and user ID from the flow variables next it will check if there are any changes in the username and user ID values if there are changes it will conduct a check with a select query if the data is found the data won't be updated if there are no issues with the data it will then proceed to process the update query in the poster SQL node next is the process of deleting data triggered by a button when the button is pressed it will connect to a change node to retrieve the values of username and user ID selected from the table obtained from flow variables then it will connect to a poster SQL node to delete the data based on the selected username and user ID and display a [Applause] notification and then it connects to a node to display data in the table so that the displayed data is [Music] refreshed thank you for watching you can download the flow code used in this video from the link provided in the video description don't forget to like And subscribe see you in the next video

Channel: Yaser Ali Husen

Views: 712

Keywords:

Id: hSCV-N5-a2M

Length: 8min 27sec (507 seconds)

Published: Fri Mar 08 2024