Node Red: How to Export Database Data to CSV

hello and welcome back to a new exciting video on this channel in this video we will create an extension to the last video where we created a simple crot application and this time we will add this small button here which allows the user to download the data from the database as a CSV file to his local machine so the first step is to import the project from the video where we created the cro application but before we can do that we should install the modules that we used there so we won't have any problems with these noes so we use the dashboard Noe the table note and the MySQL Noe now that that's installed we can import import the project from last time for that we go to this menu up here to import and here you can select a Json file to import after selecting your file you'll get a Json preview you can import it to the current flow or to a new flow I will use a new flow here and now that it's important we get a new flow and the first thing I'll do is rename it to dashboard and I will deploy it to check if it works and as you can see the SQL node gets an error so the problem here is that when you export this node it won't export the username and the password which is really important but it also means that we have to uh always add it again if we import something so if I enter my credentials here and deoy again we can see that it's now properly connected so now I would like to check the dashboard and as you can see here there should be a link button here but sometimes it doesn't show up for that you can just refresh your entire n red and then you can see that you have the button right here so this is the dashboard that we did last time if we refresh we get the values in the database we can add some new ones let's add for example and you can also edit those values as well and the thing I would like to add now is a button down here which is called download data which allows the user of the dashboard to download the data in this table as a CSV file to his local drive so we'll go to the first flow and I will call this one download data first thing we need is a button we make sure that it's in the database sensor values group because I would like to have it under the sensor values then we use the download icon and we call the label download data then we will check the layout I would like to make sure that the download button is underneath the refresh button we can deploy and check it out and as you can see the download button is right here all right so if we want to download all the data from our table we need to select all the values in the database and since we already did that here in the previous example we can just copy paste this part and use it right here so here just select all function just has an SQL request selecting all the values from the table sensor values and we will check with a debug if we get the right result so we go right here and download data we just get fre entries with 4215 and as you can see that's exactly what we get now we would like to convert this data to CSV format for that we will use the CSV Noe this one will be in here in parser and there we have the CSV node that's a note that's standard in the node environment so here we need to specify The Columns of our CSV and those are the values of the uh object keys so we would like to export all the stuff so we will use ID time stamp and value so ID time stamp and value and we'll separate with a comma but you can also chose some other things here and the rest is all right like it is this so the next part would be to save the file for that we will use a function to predefine the path where we would like to save the data because the thing is if you would like to download the data we need to first store it on the device where the node R has access to so we can then export it to another device I will specify puff in a function node because then you have the the option to change the puff for different files for example okay so here I will use data/ static SL files slash sensor values. CSV you will see a bit what that paff actually means and then we have to store the file so we use the right file node and here we specify that the file name should be msgp so that will take the Puffs that we specified before we would like to overwrite the file and we want to make sure that it gets created if the directory doesn't exist already and now we can test what happens here so if we use a debug and we deploy and test it out we should get an answer here and now we can go to our node red directory and check out if our file is there so as mentioned in the previous example I'm running no red in a Docker environment because I have multiple instances of no R that's not really important the only thing here is that I have to access the files in a different way than you guys have so here you can see that I have to access data and then I want used SL static SL files and in here you can see that I have my sensor values file and I can also open it the inbuilt file editor and we can see that the values are in here next we have to specify the HTTP static folder so that our node R dashboard has access to this data we can do that in the settings file if it's installed locally on in your machine you will just go to this file on your hard drive and here I will search for HTTP static and just make sure that you select the right one because there are a few ones so those are the wrong ones that's wrong as well all right and the one we would like to change is this one here so the single static source so we can uncomment this one and here I will have to specify my puff so it's going to be data/ static and then we have to save it and now you will have to restart your Nord red to make sure that the settings get applied like you want it and now we can go on and make sure that we can actually export this file so for that we will need a few things so firstly we will need a trigger so the reason we need a trigger here is that the export would otherwise only work once because the MSG doesn't change actually so here I will say I would like to send open when an MSG arrives then I want to wait for 250 milliseconds for example and then I will send a reset you will see where we use that in a moment so this open and reset will get sent in the payload but I would like to have it in MSG do command so I'll use a change here so we will just set MSG do command to MSG payload and now the last and most important thing is where we actually Define that we want to download the file for that we will use a dashboard template because we will have to write some C JavaScript all right so we connect that here then I would like to make sure that it's a widget in a group and it should be in the sensor values group specifically and in here we will just need a script tag cuz you don't want to actually show anything now in here we need to call a function that has access to the MSG topics you can see how this works in the official n documentation and here I will have scope dot dollar watch and then I would like to watch the MSG and pause that to a function and now I will say if the MSG do payload equals open I will let open a window which will then download the file specified so window open http double do/ SL now you have to put the address of your here no Ron so it should be Local Host 1880 and then the path to the file so slash files so we can use SL files here because we specified that the HTTP static is data/ static so we are in here already and now we will specify slash files slash the name of the file so SL files SL sensor values. CSV and the last thing is that we would like to open this in a new tab so underscore blank that makes sure that we actually open it in a new tab and now the last thing is that we want to call this function here with the scope so that just make sure that we have the information required to actually make the save statement right here then we can deploy that and check if it works so we will once again refresh and click on download data what you have to keep in mind here is that it will actually create a popup which may get blocked so we would like to allow this one for now and open and I actually forgot to set the files here and now you can see that we have a sensor values downloaded if you open the download a file with any text editor you'll see the exact values shown in the dashboard so that's it for this video I hope you enjoyed it and you learned something see you next time

Channel: RuWindustries

Views: 1,414

Keywords: Node-Red, Industry 4.0, IOT, JavaScript, Databases, SQL, MySQL

Id: ueuJOSKJo6Q

Length: 12min 41sec (761 seconds)

Published: Sat Jan 13 2024