CSV input - publish MQTT topics

This is my first project with Node-Red, and my previous programming was a decade ago with VBA, so apologies in advance.

I am trying to pull in CSV data from an http request (that's pretty simple, I think I have that part sorted), then publish the data items to MQTT as individual topics.

Node-Red 2.2.1 is running in a Docker container on a Synology NAS.
I have the Aedes MQTT broker working and a macvlan setup giving it an external IP. That's working nicely, I can publish and subscribe correctly.
My http request is fairly straightforward. I don't have the actual hardware (Iotawatt power monitor) to respond to it yet, but I've copied known working code from another Node-Red user.

(http://192.168.XXX.XXX/query?select=[Mains,House,Solar]&begin=s-300s&end=s&group=all&format=csv&header=no)

The CSV returned will be in the format:
777,888,999

Using a CSV node I can pull the CSV apart and label the data. eg Mains:777 House:888 Solar:999

What I am really struggling with is what to do with the Topic:Message data next, and how.
Can I somehow send each Topic:Message to the MQTT broker via a single mqtt out node? Or do I need to create a separate node for each Topic.
If I can send them to the one mqtt out node, how do I go about that.

Thanks

You can also get the info returned as a JSON from iotwatt.

What are you trying to achieve sending it on through MQTT ?

Any reason you chose AEDES instead of Mosquitto ? I think most people on here use Mosquitto and hence you will find it easier getting ongoing help if you need it

Craig

Here is my code - you will see i have it come back as JSON - i use it all on the same box do not forward it on anywhere but as above if you explain what you are trying to do with it - we will be able to help more.

[{"id":"683f1f54.2faa9","type":"inject","z":"201953fd.6ac33c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":260,"wires":[["424b052a.d0d92c"]]},{"id":"424b052a.d0d92c","type":"http request","z":"201953fd.6ac33c","name":"Get IOTAWATT 1 Min averages","method":"GET","ret":"txt","paytoqs":"ignore","url":"http://172.16.101.200/query?select=[Solar,Consumption,Grid,Battery]&begin=s-60s&end=s&group=all&format=json&header=no","tls":"","persist":false,"proxy":"","authType":"","credentials":{},"x":350,"y":260,"wires":[["d0f6ddf5.34ae6"]]},{"id":"d0f6ddf5.34ae6","type":"json","z":"201953fd.6ac33c","name":"","property":"payload","action":"","pretty":false,"x":570,"y":260,"wires":[["76b9a7c8.91b348","d24fb8e8.2ed418","a344460f.c0d278","958d8f6d.ad052","89343a78434a00ae"]]},{"id":"76b9a7c8.91b348","type":"ui_gauge","z":"201953fd.6ac33c","name":"Solar Output","group":"86e8d294837f8223","order":1,"width":"2","height":"2","gtype":"gage","title":"Solar","label":"Watts","format":"{{payload[0][0]}}","min":0,"max":"11000","colors":["#b31b00","#e6e600","#60cb3a"],"seg1":"2000","seg2":"5000","x":810,"y":260,"wires":[]},{"id":"d24fb8e8.2ed418","type":"ui_gauge","z":"201953fd.6ac33c","name":"Total House Consumption","group":"86e8d294837f8223","order":2,"width":"2","height":"2","gtype":"gage","title":"Consumption","label":"Watts","format":"{{payload[0][1]}}","min":"1000","max":"10000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"4000","seg2":"6000","x":850,"y":300,"wires":[]},{"id":"a344460f.c0d278","type":"ui_gauge","z":"201953fd.6ac33c","name":"Power Usage","group":"86e8d294837f8223","order":3,"width":"2","height":"2","gtype":"gage","title":"Grid","label":"Watts","format":"{{payload[0][2]}}","min":"-9000","max":"10000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"0","seg2":"3000","x":820,"y":340,"wires":[]},{"id":"958d8f6d.ad052","type":"ui_gauge","z":"201953fd.6ac33c","name":"Battery Use -ve = charging","group":"86e8d294837f8223","order":4,"width":"2","height":"2","gtype":"gage","title":"Battery","label":"Watts","format":"{{payload[0][3]}}","min":"-15000","max":"15000","colors":["#b31b00","#e6e600","#60cb3a"],"seg1":"2000","seg2":"5000","x":860,"y":380,"wires":[]},{"id":"89343a78434a00ae","type":"debug","z":"201953fd.6ac33c","name":"1 Min Iotawatt info","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":840,"y":220,"wires":[]},{"id":"86e8d294837f8223","type":"ui_group","name":"1 Minute Average","tab":"3c82250e.dc28ea","order":2,"disp":true,"width":"8","collapse":false},{"id":"3c82250e.dc28ea","type":"ui_tab","name":"Electricity Prices","icon":"dashboard","disabled":false,"hidden":false}]

Craig

Thanks Craig.
I've been using a very similar flow I found as a starting point, but it's the process of getting things into mqtt that's giving me trouble, I don't understand how to get an mqtt out node to read each of the data points (eg Mains, House & Solar) and send them to the broker, everything I try just picks one data point and posts that each time.

I think I've worked out how I could add an mqtt out node for each topic, but that doesn't seem very elegant really. Maybe that's the best way, but when I look at what people do with NR I can't help but think it's not.

I have other devices that subscribe to mqtt topics, the most immediate one being an OpenEVSE car charge point. It subscribes to a topic (I'm calling it "Mains" for now) and when that goes sufficiently high (6kW threshold) then the charger turns on to consume excess solar. As the solar export increases the charger increases the current available to the car to track that excess.

I started with a JSON query to the Iotawatt but it was more apparent to me how I could get the Topic:Message lined up via the CSV node. I really don't care which one I use, it seems 6 to one, half dozen the other.

I'd like to solve posting multiple mqtt topics now so that I can easily scale things as I go on.

I started with Mosquitto, but then saw Aedes and liked the idea of everything residing within the one Node-Red Docker container. If Aedes proves troublesome it is trivial to reinstall Mosquitto and point Node-Red mqtt to it. During testing Aedes I switched between the two a couple of times and it was absolutely simple transparent.

Down the track I'm also planning on using the Iotawatt data to drive http requests to the charger to alter various parameters, as well as using other external factors. But that's for later.

Cheers
Peter

If you have created the required object using the csv node then just use a split node set as follows
image
Which will create three messages with the required topics to feed direct into an mqtt out node

Awesome, thank you.
It still took me a little while to work out how to then reference the key/value pair downstream but I've now got it. A small step, but one that will transfer forward into other areas I think.

I'd looked at the Split node and thought it sounded like the answer but couldn't quite get my head around the specifics to get it working. So easy with a bit of guidance the right direction.

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.