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

#1

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"}
0 Likes

Can CSV being used as a profile in a graph
#2

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":[]}]
0 Likes

#3

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?

0 Likes

#4

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

0 Likes

#5

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.

0 Likes

#6

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.

0 Likes

#7

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 (https://github.com/node-red/node-red-dashboard/blob/master/Charts.md)

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

0 Likes

#8

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.....

0 Likes

#9

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.

0 Likes

#10

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.. ?

0 Likes

#11

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

0 Likes

#12

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 ?

0 Likes

#13

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"}
0 Likes

#14

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

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 ?

0 Likes

#15

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 ?

0 Likes

#16

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

0 Likes

#17

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)

  1. 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"}

  2. 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

0 Likes

#18

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

0 Likes

#19

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).

0 Likes

#20

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

0 Likes