node red xls data to chart

[mata xarh]

Hi guys i need your help. On node red i have used successfully the alafile node to pull data from an xls file. I would like those data displayed on a chart (x-Y data) which i keep falling short of doing. Any help would be really appreciated.Here are the xls files as well (https://www.dropbox.com/sh/z59s47b24uxm1ps/AACIBpMHQLgWfSjMX1Obsbhxa?dl=0) [{"id":"adc929dd.74c1c","type":"tab","label":"Flow 1"},{"id":"a293ab3c.c48d08","type":"inject","z":"adc929dd.74c1c","name":"","topic":"","payload":"","payloadType":"date","repeat":"5","crontab":"","once":false,"x":104.5,"y":187,"wires":[["8f3615ea.73b828","6391ad80.5e1324"]]},{"id":"8f3615ea.73b828","type":"alafile in","z":"adc929dd.74c1c","name":"X data","filename":"Desktop/NODE1","format":"xlsx","columns":"*A","headers":true,"x":331.5,"y":94,"wires":[["9ca08631.af42d","d7730091.04504"]]},{"id":"9ca08631.af42d","type":"ui_chart","z":"adc929dd.74c1c","name":"","group":"e50ac821.60ead","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"5","removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"colors":["#1F77B4","#AEC7E8","#FF7F0E","#2CA02C","#98DF8A","#D62728","#FF9896","#9467BD","#C5B0D5"],"x":537.5,"y":196,"wires":[[],[]]},{"id":"d7730091.04504","type":"debug","z":"adc929dd.74c1c","name":"","active":true,"console":"false","complete":"false","x":752.5,"y":127,"wires":[]},{"id":"6391ad80.5e1324","type":"alafile in","z":"adc929dd.74c1c","name":"Y data","filename":"Desktop/NODE2","format":"xlsx","columns":"*A","headers":true,"x":366,"y":329,"wires":[["9ca08631.af42d"]]},{"id":"e50ac821.60ead","type":"ui_group","z":"","name":"Default","tab":"110b80c3.583e0f","disp":true,"width":"6"},{"id":"110b80c3.583e0f","type":"ui_tab","z":"","name":"XXX","icon":"dashboard","order":1}]

[David Caparrós]

Hello Mata

I saw you have only one column data to show on the chart, you can make it much easier just directing this data to the chart and make a text file to backup your data in case of restart or deploy, something like:

[{"id":"2d24edf1.e757b2","type":"file","z":"aa1ac307.b2cbd","name":"","filename":"/home/pi/.node-red/datalog/aactual","appendNewline":true,"createDir":false,"overwriteFile":"true","x":920,"y":80,"wires":[]},{"id":"615aa930.6a91e8","type":"ui_chart","z":"aa1ac307.b2cbd","name":"Amperaje instantaneo","group":"2ebc9347.9cd16c","order":3,"width":0,"height":0,"label":"Amperaje instantaneo","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"604800","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"x":660,"y":80,"wires":[["2d24edf1.e757b2"],[]]},{"id":"e80aaafa.1368d8","type":"json","z":"aa1ac307.b2cbd","name":"","pretty":"false","x":570,"y":140,"wires":[["615aa930.6a91e8"]]},{"id":"f759d290.4d702","type":"file in","z":"aa1ac307.b2cbd","name":"","filename":"/home/pi/.node-red/datalog/aactual","format":"utf8","chunk":false,"x":360,"y":160,"wires":[["e80aaafa.1368d8"]]},{"id":"6bfed25.9ae6d2c","type":"delay","z":"aa1ac307.b2cbd","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"5","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":340,"y":120,"wires":[["615aa930.6a91e8","aebd5b94.823ed8","c84c5453.50dff8"]]},{"id":"7ddf5296.77442c","type":"inject","z":"aa1ac307.b2cbd","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":130,"y":160,"wires":[["f759d290.4d702"]]},{"id":"465d759c.50dbfc","type":"mqtt in","z":"aa1ac307.b2cbd","name":"","topic":"ESP-2/Amp","qos":"2","broker":"32ed8c49.582164","x":110,"y":80,"wires":[["97b6859e.36e688","efe525e4.65c308","6bfed25.9ae6d2c"]]},{"id":"f60be876.d54258","type":"comment","z":"aa1ac307.b2cbd","name":"Consumo instantaneo amperios","info":"","x":190,"y":20,"wires":[]},{"id":"2ebc9347.9cd16c","type":"ui_group","z":"","name":"Consumo","tab":"49f882f0.c51aec","order":1,"disp":true,"width":"12"},{"id":"32ed8c49.582164","type":"mqtt-broker","z":"","broker":"127.0.0.1","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"49f882f0.c51aec","type":"ui_tab","z":"","name":"CONSUMO","icon":"fa-plug","order":7}]

Regards 

[mata xarh]

Hello David! 

i really appreciate your help. To backup my data it is not my problem right now. My real problem is that i can't  display my data on a chart. I will give you my excel file so i hope you will understand my problem and maybe give me a solution (i have to present this project very soon)! My goal is chart axes x-->column A from excel file(frequency) AND chart axes y-->column B from excel file(mixer output).I want to have that chart (2 columns of frequency f and mixer output, by the way the mixer output is referred to time).I upload the data on node red from my excel file by node <<alafile> , but i can't take the chart! Any help would be really appreciated!

thanx a lot!

mata

[Csongor Varga]

I am not at home right now and I cannot upload the file to my pi, so cannot see what comes out of the alafile in node.

But your case if a bit different, because you are not displaying time series data. You have node1 which is the X value and node2 which is the Y data (or the other way around). So it would be better if the source file is like the mixer.xlsx as above, because your need to pass both values to the chart at the same time.

[Nick O'Leary]

Just to avoid needless duplication, I've been answering this in Slack.

I have described how to use ui_chart to inject data, but the fact it isn't time series data is a problem.

Nick

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/f065887b-42fa-43e9-861e-b16213a09545%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[mata xarh]

Thank you for your reply. I would really appreciate any further help when you have the time!

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---

You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/n6iGTJbgLf8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

[Csongor Varga]

Can you create another example for me with the alafile in reading the values from the mixer.xlsx? And please screenshot the output of the alafile in, so I can see how the data is coming out of the node.

[mata xarh]

 if you want to import :[{"id":"36d554fa.b30c74","type":"inject","z":"7d9bcb84.2624ec","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":84.5,"y":172,"wires":[["6a3c1e0a.42c9f8"]]},{"id":"6a3c1e0a.42c9f8","type":"alafile in","z":"7d9bcb84.2624ec","name":"MIXER","filename":"/Users/dr.matinakos/Desktop/MIXER","format":"xlsx","columns":"*","headers":false,"x":364.5,"y":178,"wires":[["a4208d75.2dd28","43c9a5d0.ad0924"]]},{"id":"a4208d75.2dd28","type":"ui_chart","z":"7d9bcb84.2624ec","name":"","group":"9b99d5bc.785c18","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"colors":["#1F77B4","#AEC7E8","#FF7F0E","#2CA02C","#98DF8A","#D62728","#FF9896","#9467BD","#C5B0D5"],"x":627.5,"y":299,"wires":[[],[]]},{"id":"43c9a5d0.ad0924","type":"debug","z":"7d9bcb84.2624ec","name":"","active":true,"console":"false","complete":"false","x":702.5,"y":168,"wires":[]},{"id":"9b99d5bc.785c18","type":"ui_group","z":"","name":"Default","tab":"af7323a5.3e69b8","disp":true,"width":"6"},{"id":"af7323a5.3e69b8","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

-- 
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
--- 
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/n6iGTJbgLf8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/ff8e4b5b-5d03-48ed-be53-366a83eff145%40googlegroups.com.

[Csongor Varga]

Hi,

I was able to transform your data, so it is showing the sine wave in the chart, but as Nick pointed out, this chart is designed to display time series data. It always expects the x axis data to be a date value. I can always pass your numbers, but I cannot format the X axis label to show the same numbers as in the input structure.

Here is my code: just connect the output of your alafile to my function node and it should work:

[{"id":"a38e30a2.9ad86","type":"debug","z":"36407d0d.74dd82","name":"","active":false,"console":"false","complete":"false","x":289,"y":59,"wires":[]},{"id":"66c7ed96.8a3444","type":"function","z":"36407d0d.74dd82","name":"Transform","func":"var msg2 = [];\nvar output = [];\n\n    // this is the logic when there are multiple data sets are received\n    for (var i=0; i<msg.payload.length; i++) {\n            output.push([msg.payload[i].A, msg.payload[i].B]);\n    }\n    msg2.push({ key: \"Mixer\", values : output});\n    //msg2.push({ key: \"test\", values : output});\n\nmsg.payload=msg2;\nreturn msg;","outputs":1,"noerr":0,"x":295,"y":150,"wires":[["72baf3d6.67565c","5c110b4c.d62d34"]]},{"id":"72baf3d6.67565c","type":"debug","z":"36407d0d.74dd82","name":"","active":true,"console":"false","complete":"false","x":538,"y":196,"wires":[]},{"id":"5c110b4c.d62d34","type":"ui_chart","z":"36407d0d.74dd82","name":"","group":"c494ec5.4d1e51","order":0,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"ss","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"x":494,"y":275,"wires":[[],[]]},{"id":"e9a45f0e.a29c7","type":"inject","z":"36407d0d.74dd82","name":"","topic":"","payload":"[{\"A\":13220,\"B\":-0.6203},{\"A\":13240,\"B\":-0.5108},{\"A\":13250,\"B\":-0.454},{\"A\":13260,\"B\":-0.382},{\"A\":13270,\"B\":-0.3175},{\"A\":13280,\"B\":-0.2071},{\"A\":13290,\"B\":-0.0813},{\"A\":13300,\"B\":0.0445},{\"A\":13310,\"B\":0.1675},{\"A\":13320,\"B\":0.2754},{\"A\":13330,\"B\":0.364},{\"A\":13340,\"B\":0.4344},{\"A\":13350,\"B\":0.486},{\"A\":13360,\"B\":0.5268},{\"A\":13370,\"B\":0.5605},{\"A\":13380,\"B\":0.5892},{\"A\":13390,\"B\":0.6135},{\"A\":13400,\"B\":0.6321},{\"A\":13410,\"B\":0.642},{\"A\":13420,\"B\":0.6399},{\"A\":13430,\"B\":0.6255},{\"A\":13440,\"B\":0.6007},{\"A\":13450,\"B\":0.5685},{\"A\":13460,\"B\":0.529},{\"A\":13470,\"B\":0.4811},{\"A\":13480,\"B\":0.4226},{\"A\":13490,\"B\":0.3523},{\"A\":13500,\"B\":0.2694},{\"A\":13510,\"B\":0.174},{\"A\":13520,\"B\":0.0679},{\"A\":13530,\"B\":-0.0438},{\"A\":13540,\"B\":-0.1558},{\"A\":13550,\"B\":-0.2584},{\"A\":13560,\"B\":-0.3496},{\"A\":13570,\"B\":-0.4241},{\"A\":13580,\"B\":-0.4839},{\"A\":13590,\"B\":-0.5314},{\"A\":13600,\"B\":-0.5701},{\"A\":13610,\"B\":-0.6015},{\"A\":13620,\"B\":-0.6265},{\"A\":13630,\"B\":-0.644},{\"A\":13640,\"B\":-0.6518},{\"A\":13650,\"B\":-0.6484},{\"A\":13660,\"B\":-0.6344},{\"A\":13670,\"B\":-0.6119},{\"A\":13680,\"B\":-0.5817},{\"A\":13690,\"B\":-0.5433},{\"A\":13700,\"B\":-0.4964},{\"A\":13710,\"B\":-0.4376},{\"A\":13720,\"B\":-0.3656},{\"A\":13730,\"B\":-0.2788},{\"A\":13740,\"B\":-0.1768},{\"A\":13750,\"B\":-0.0638},{\"A\":13760,\"B\":0.0512},{\"A\":13770,\"B\":0.1602},{\"A\":13780,\"B\":0.2562},{\"A\":13790,\"B\":0.337},{\"A\":13800,\"B\":0.4051}]","payloadType":"json","repeat":"","crontab":"","once":false,"x":100,"y":102,"wires":[["66c7ed96.8a3444","a38e30a2.9ad86"]]},{"id":"c494ec5.4d1e51","type":"ui_group","z":"","name":"Counter","tab":"156af96f.f8fd27","disp":true,"width":"6"},{"id":"156af96f.f8fd27","type":"ui_tab","z":"","name":"Home","icon":"home","order":1}]

[Julian Knight]

Mata, you might want to check out Peter Scargill's blog as I think he has used some other plot tools with Dashboard and they should be able to do what you want.

[David Caparrós]

Check this out:

https://flows.nodered.org/flow/c3dc75c47323a2754f5285225bce64b5

[mata xarh]

Τhank you so much!!! I appreciate it! it really works but my supervisor ask me persistently to show the same numbers in x axis as in the excel file. What can i do? maybe i have to do it with a different way instead of the node Chart, but i don’t know what i can do.

thanx a lot.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c87456e9-4077-4296-a51c-2933a99e2023%40googlegroups.com.

[mata xarh]

he ask me to show only the Fstart and Fstop , if i can’t show all the frequencies.