Use .csv file as the source for a dashboard chart?

nodecentral · 25 October 2018 15:52

Hi

Please could someone let me know how I can use a .csv file as the source for a graph on the dashboard , so that when the .csv is updated, the chart updates too ?

The .csv I have called Room Temp.csv has the following content. (Created via another flow)

{"Timedate":"11:13:30 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.5"}
{"Timedate":"11:15:03 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.6"}
{"Timedate":"11:19:42 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.7"}
{"Timedate":"11:44:30 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":"11:49:09 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.9"}

Andrei · 25 October 2018 16:05

The watch node can be used to watch for a file change. If any change is detected the node will generate a message that could be used to trigger the chart update.

See the message that is generated when the csv file is updated:

r-01

Testing flow:

[{"id":"e7e186e2.a4ee18","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"d3ef2da4.c73b2","type":"watch","z":"e7e186e2.a4ee18","name":"Watch chart1.csv","files":"C:\\Users\\OCM\\.node-red\\static\\nrfiles\\chart1.csv","recursive":"","x":140,"y":200,"wires":[["d5b3c5ad.2326a8"]]},{"id":"d5b3c5ad.2326a8","type":"debug","z":"e7e186e2.a4ee18","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":330,"y":200,"wires":[]}]

nodecentral · 25 October 2018 18:01

Thanks @Andrei, that’s great info.

How do I get a csv linked to a chart and choose the fields it’s uses/plots ?

Also should the date/time be unix rather than the human readable I’m currently collecting?

Andrei · 25 October 2018 18:06

I will prepare an example flow to explain. Just give me a few minutes.

dceejay · 25 October 2018 18:20

If both flows are on the same machine then it would be much simpler to just feed the data to both the file and the chart rather than go via the csv. But it’s a good exercise I guess.

knolleary · 25 October 2018 18:37

Also worth pointing out the file isn't CSV. Each row is a JSON string... easy enough to parse with the file node passing each line to a JSON node. Just not CSV.

Andrei · 25 October 2018 18:57

Here is a draft... missing to check the date formatting.

[{"id":"970fc822.31fce8","type":"tab","label":"Chart - JSON data to Dashboard ","disabled":false,"info":""},{"id":"79849682.8825a8","type":"ui_chart","z":"970fc822.31fce8","name":"","group":"97aaf182.225e2","order":0,"width":0,"height":0,"label":"{{msg.label}}","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"step","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":570,"y":440,"wires":[[],[]]},{"id":"3f2550ef.b58d1","type":"inject","z":"970fc822.31fce8","name":"","topic":"","payload":"[]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":390,"y":440,"wires":[["79849682.8825a8"]]},{"id":"b9ddd55a.1cf7c8","type":"function","z":"970fc822.31fce8","name":"Setup payload","func":"series = [];\ndata = [];\nlabels = [];\n\nseries.push(\"Temp\");\nlabels.push(\"Temp\");\ndata.push(msg.data);\n\nmsg.payload = [{\"series\":series, \"data\":data,\"labels\": labels}];\n\nreturn msg;","outputs":1,"noerr":0,"x":380,"y":380,"wires":[["79849682.8825a8","257eddab.303be2"]]},{"id":"257eddab.303be2","type":"debug","z":"970fc822.31fce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":550,"y":380,"wires":[]},{"id":"c78080b.67f998","type":"watch","z":"970fc822.31fce8","name":"Watch chart1.txt","files":"C:\\Users\\OCM\\.node-red\\static\\nrfiles\\chart1.txt","recursive":"","x":180,"y":160,"wires":[["570b2237.64778c"]]},{"id":"713d8bbc.09f304","type":"debug","z":"970fc822.31fce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":750,"y":260,"wires":[]},{"id":"d1e26603.e7dbe8","type":"file in","z":"970fc822.31fce8","name":"","filename":"","format":"lines","chunk":false,"sendError":false,"x":510,"y":160,"wires":[["43b3d747.2a1428"]]},{"id":"570b2237.64778c","type":"change","z":"970fc822.31fce8","name":"Set filename","rules":[{"t":"set","p":"filename","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":160,"wires":[["d1e26603.e7dbe8"]]},{"id":"43b3d747.2a1428","type":"json","z":"970fc822.31fce8","name":"","property":"payload","action":"obj","pretty":false,"x":270,"y":260,"wires":[["8b709565.39bc08"]]},{"id":"8b709565.39bc08","type":"join","z":"970fc822.31fce8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":410,"y":260,"wires":[["6da9de2b.4ea93"]]},{"id":"6da9de2b.4ea93","type":"function","z":"970fc822.31fce8","name":"Build Data array","func":"function fixData(elem) {\n    return {\"x\" : new Date(elem.Timedate).getTime(), \"y\":parseFloat(elem.Temp)};\n}\n\nlet adata = msg.payload.map(fixData);\nmsg.data = adata;\nreturn msg;","outputs":1,"noerr":0,"x":560,"y":260,"wires":[["713d8bbc.09f304","b9ddd55a.1cf7c8"]]},{"id":"97aaf182.225e2","type":"ui_group","z":"","name":"Group 1","tab":"b5acd512.0b8b98","disp":true,"width":"12","collapse":false},{"id":"b5acd512.0b8b98","type":"ui_tab","z":"","name":"Tab1","icon":"dashboard"}]

First node is the watch node. Each time you update this file it will trigger our flow.
The second node will set msg.filename that is required by the following node to read the file.
As Nick mentioned your file is not a CVS but a JSON file instead, so added a json node to convert the string to a javascript object.
The join node will combine all the file lines as an array to easy the data manipulation.
The function node will build a data array in the proper format for the chart (hopefully). This is the code inside:

function fixData(elem) {
    return {"x" : elem.Timedate, "y":parseFloat(elem.Temp)};
}

let adata = msg.payload.map(fixData);
msg.data = adata;
return msg;

The remaining nodes are a kind of a boilerplate for drawing a chart from stored data (node-red-dashboard/Charts.md at master · node-red/node-red-dashboard · GitHub)

I need to take some extra time to fix the date format. This is preventing the chart from being displayed in the dashboard.

Andrei · 25 October 2018 19:09

Only now I noticed that you are storing only time not dates..

"Timedate":"11:13:30 GMT+0000 (UTC)"

I wonder if the dashboard chart can work in such way.....

Colin · 25 October 2018 20:35

If what you are actually trying to do is store data and then later display it in a chart then I strongly suggest that you look at influxdb for storing the data and grafana for charting it.

nodecentral · 25 October 2018 21:29

Hi @knolleary

Why is that ?

I was expecting the output to the .csv file to just be one header row, and the rest to be the values, but it adds the moustaches and makes it like an object ?

Below are the csv node properties I’m using.. ?

knolleary · 25 October 2018 21:30

Hard to say - will depend what messages you're passing the csv node.

nodecentral · 25 October 2018 22:53

I’m just passing the information as comma separated text via a template node?

[{"id":"e136db5d.dcd88","type":"template","z":"8d45fe98.e59d58","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{payload.timestamp}}, Office Room Temperature, {{payload.serial}}","output":"str","x":480,"y":200,"wires":[["a1828a32.678ca8","2fbf36a5.c89ae2"]]}]

Any reason why that approach would do it ?

knolleary · 25 October 2018 22:58

Your Template node will generate strings like:

11:13:30 GMT+0000 (UTC), Office Room Temperature, 18.5

Which is already a csv string that could be written straight to your file. Passing that csv string to to a csv node will cause it to parse that string into an object of key/value pairs:

{"Timedate":"11:13:30 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.5"}

nodecentral · 26 October 2018 07:03

Ok, that seems counter intuitive, but I’m still learning

So how do you set/assign the column headers if not via the csv node?

Do you have to create a separate template feed for those ?

nodecentral · 26 October 2018 09:07

Thanks @Andrei

When I run the flow, I receive the following error

26/10/2018, 10:02:55[node: 71647d36.1bc024
msg : string[28]

"Unexpected end of JSON input"

The .csv I’m testing against just has the following content in it. Any ideas ?

{"Timedate":"10:02:55 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18"}
{"Timedate":"10:02:56 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":"10:02:57 GMT+0000 (UTC)","Room":" Office Room Temperature","Temp":" 18"}

Based on Nick’s comments, it sounds like comma separated content should not go via the .csv node, as it gets converted. With that in mind it’s seems if I write the template node directly to the file - it would would then simply be

10:02:55 GMT+0000 (UTC),Office Room Temperature, 18
10:02:56 GMT+0000 (UTC),Office Room Temperature, 18.8
10:02:57 GMT+0000 (UTC),Office Room Temperature, 18

And it looks like I should change the time/date value being used, as that is an error, I want date and time, is unix time better ?

hotNipi · 26 October 2018 09:18

Might be non printable ASCII characters at the end of file?

nodecentral · 26 October 2018 09:56

Which format is the best one to used for a NR dashboard chart/graph that is to be built and updated from a .csv file ? (I can create either, and both now use unix time)

option A

{"Timedate":1540551152,"Room":" Office Room Temperature","Temp":" 18"}
{"Timedate":1540551153,"Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":1540551163,"Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":1540551165,"Room":" Office Room Temperature","Temp":" 18"}
{"Timedate":1540551166,"Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":1540551167,"Room":" Office Room Temperature","Temp":" 18"}
{"Timedate":1540551167,"Room":" Office Room Temperature","Temp":" 18.8"}
{"Timedate":1540551168,"Room":" Office Room Temperature","Temp":" 18"}
option B

1540551152, Office Room Temperature, 18
1540551153, Office Room Temperature, 18.8
1540551163, Office Room Temperature, 18.8
1540551165, Office Room Temperature, 18
1540551166, Office Room Temperature, 18.8
1540551167, Office Room Temperature, 18
1540551167, Office Room Temperature, 18.8
1540551168, Office Room Temperature, 18

Andrei · 26 October 2018 12:59

With option B the flow already provided will produce the chart right away (no changes required)

Andrei · 26 October 2018 13:02

Hi @nodecentral,

Answer: I tested the .csv you provided and it worked just fine for me. I wonder if your text editor is saving some special character in the file.

Answer: the flow I suggested would not be simpler. Whatever is the format (.csv or json) it will be necessary to convert the data to javascript object.

Answer: Personally I prefer the timestamp format (Unix epoch).

nodecentral · 26 October 2018 16:29

It looks like this was a new blank line at the end of the file. I removed that and it works !

Topic		Replies	Views
Can CSV being used as a profile in a graph Dashboard	31	2107	12 March 2019
Create a CSV file and from CSV to chart Dashboard	3	4037	3 May 2020
Csv file to chart Dashboard	5	1943	3 March 2021
Can anyone teach me how to display CSV file data into the dashboard ( for example, a line chart ) Dashboard node-red-dashboard	17	2571	27 January 2022
Drawing a chart in node red dashboard using csv file Dashboard	5	837	23 June 2022

Use .csv file as the source for a dashboard chart?

Related topics