Storing PLC Data in a Database and Excel Sheets Using Node-RED

Community How-to-Tutorials
#1

In today's data-driven world, the ability to collect, store, and analyze data efficiently is critical, even in industrial settings. Programmable Logic Controllers (PLCs) are the backbone of industrial automation, generating a wealth of data that can provide valuable insights. This tutorial explores a practical solution for capturing and utilizing this data using Node-RED, a versatile and user-friendly platform.

This is a companion discussion topic for the original entry at https://www.solisplc.com/tutorials/storing-plc-data-in-a-database-and-excel-sheets-using-node-red
#2

Hi Guys! Nice article. It helped me implement data-logging.

I have one query. Can one we use MQTT/ OPCUA subscribe [not talking about OPCUA read] to log data into SQL database where table output is in same format as above you posted. I am having this doubt because because with Subscription, i only receives value when some changes happens in the PLC/server end/client end.

Thanks in advance

#3

Hi,
yes you can for example you subscribe 3 different values with topics “ns=3;i=1007” ,“ns=3;i=1008” , and “ns=3;i=1009” now when the value change the opc ua client will return the new value of the topic that value change in your opc ua server , so you can make a function to set 3 global values according three topics lets see how will the operation work,
at starting the OPC UA node will subscribe three values connect a function node with the output of OPC UA client node and write this code for example " if (msg.topic == “ns=3;i=1007”){global.set(“var1”,msg.payload)} else if (msg.topic == "ns=3;i=1008){global.set(“var2”,msg.payload)}
else if (msg.topic == “ns=3;i=1009”){global.set(“var3”,msg.payload)}
and return msg in else if condition
so now you will save your first data in three global variables say they are (2,3,4)
now first one change to 5 so they will (5,3,4) after this connect the output of function node to trigger the function of the insert query in your database which will get data from the 3 global variable and we are done.
just this how you can make it :slight_smile:

#4

Hi Ahmed! Thanks for prompt Reply!!!

I was thinking in the same manner but not proceeded further because i thought what if (talking about your example) lets say we have machine running for 3 days. For first two days, ‘var1’ didn’t received any value while ‘var2’, ‘var3’ variables are receiving values. So in that case how we’ll deal with that? Doesn’t your answer only accounts in case where variables already have some kind of value.

#5

Hi Shubham,
so all things is okey ?
about the question of not received value from var1 we will take it’s initial value and just every time three values inserted into the database the var1 will remain as its initial value and others will be updated

#6

Hi Ahmed.

Yeah things worked as you have stated. I deleted my reply because i thought first i should i test it first. And to my surprise it worked in the same manner as you have stated. Whatever may be the initial values of concerned ‘PLC tags’ are subscribed at the time of first subscription and later whatever value gets updated, it also gets updated its corresponding global value while rest global value remains same.

Thanks,
Shubham

1 Like
#7

Hi Ahmed,

I am facing some excel-related question:

  1. In “Node-red programming for MySQL | Step-2”, you have used ‘||’ in your function code. Why is that? I don’t find its particular use here.

  2. In the industry environement, node-red will be running on the linux-based gateway devices such as raspberry-pi or IOT2050. My question is with your method, using file-node can we configured it to store the excel file in the certains windows-computer/ window-based server part of the local-network .

  3. Is there is way to automatically program the node-red to create the date-wise excel file and subsequent data-wise machine’s data is added into it. And after 12AM(New day), new-excel file is created and new day data is started feeding in the excel file.

#8

Hi Ahmed, need your help.

I am able to create nodered dashboard table with the help of ChatGPT as shown in below image. But my table is not like yours.
image

#9

Hi Shubham,
1_ about your first question the “||” is for set initial values if the first value is undefined as example, "var x = global.get(“x”) || global.get(“y”) || 0 ;
so if the first x global variable is undefined he will go to next to get global y variable and if it also undefined value he will set x as zero; but in the example of article you are correct it really not so important here but this is how it work.
2_ about second question file node i didn’t try it before but I think it will not work it just read files from the Raspberry-pi system such as “/home/pi/…”.
3_ about third question , yes you can make this by saving excel sheets in real time by making the name of excel file that created dynamic not static so it will not overwrite on the first excel sheet that created every time you save excel sheet it will create new one with a name of a real time so you can make an enject node interval every 24 hour that create a new excel sheet.
4_ about table there is a lot of styles using html and css if you need that i used in tutorial you will find the flow in the files of the end of article

#10

Hi Ahmed, Thanks for the prompt reply.

  • Regarding pt-3, i will give it a try myself based on how you described and will get back to you when done.

  • Regarding pt-2, i did gave it a try but it didn’t worked. But, then please tell me what are the options available for us to use with linux-based gateway devices to get excel data. How do yourself do it?
    Google-sheet is one of the option which allow us to save file in the google’s drive but it come at a cost of recurring-payment to Google.

#11

Hi Shubham ,
about excel sheet, can’t you have a remote access on the linux system ?, or there is another solution you can save your excel sheet on the linux system and use read file with email node to send the excel sheet as an attachment to your email every day so you can get excel sheet every day on your email and you also can show it in the dashboard . i think this solution is good for not any payment or what is your opinion

#12

HI Ahmed, thanks for the reply.

Since, in industrial environment, Supervisors will have access to dashboard only where they want to access the machine data/print with click of the button; Hence, there goes the option of remote access.

I am beginner in node-red field let alone programming. So, I’ll be using the resources present over internet to program “use read file with email node to send the Excel sheet as an attachment to your email every day”. Though it does not seem difficult as you have already explained to me how to approach it.

But saving MySQL data into excel-file in the Linux-gateway device does not seem a viable option for me. I mean that SQL data can contain more than 1 year of data.

Other option I can think of using.

  1. kepserver x > Highly costly
  2. FlowFuse > Haven’t tested it yet.
  3. Using IPC > ‘such as siemens IPC127e’
  4. Using ‘PCIe slot’ that comes with rasp PI 5 to connect SSD.

But testing using above options requires time & money. So I would like to learn from your experience and ask, How did you do it your industrial projects?

#13

Hi @Ahmed_Tharwat, I hope you’re not busy. Just waiting for your reply

#14

@Shubhamnodered hi bro I’m very sorry for late in rebly, about saving data I see the efficient method is to saving it in the cloud, about a no cost solution I dont know really what is the best no cost one for a big data…

#15

Not a problem Ahmed. I got sick as a result i wasn’t able to reply to this message.

#16

Do you have an alternative means of communication besides this platform? Being new to this field, I often find myself needing hints or guidance. Given your experience, I would appreciate a similar level of assistance from you, like the direction you’ve provided me here. Sometimes talking to someone really clears the mind! Is that something feasible? Can you make it possible?

I can not thank you enough for your help!!!

#17

Hey, @Shubhamnodered, we’re a small team with limited time and 10K+ students, so as you can imagine, we receive hundreds of questions every month, and this format is the best way we can manage.