Csv value to mqtt

General
,
1

HI,

i read a csv file from a ftp server and want to sent the values to a mqtt broker. My point know is, how can i create for each value in a line separate topic.

The csv have this fields:
sn; volume 1; volume 2; volume 3
22; 99; 33; 44; 77
11; 99; 33; 44; 77

At the end the topics should look like this:
/foo/bar/22/volume1/
/foo/bar/22/volume2/

/foo/bar/22/volume1/
/foo/bar/11/volume2/

At the moment i need this for something like 10-15 rows. So i can us copy and paste.

But if everything works fine, i will need a script or function where i can handle 400-450 lines. And my knowledge with functions are pretty basicly :frowning:

Thanks for any support

2

Start, I think, by sending the file into a CSV node to convert it to an array of objects with named properties. Then you can feed it into a Split node to convert into a stream of individual messages. Then you can massage each one of those to generate the topics and values that you want.

3

Here is an example for you to study.

Note
Topics should not start or end with a /, if you want to folow convention and not have blank topic paths.

[{"id":"2b1db6cff0ff0516","type":"inject","z":"613df62afc8a16bf","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"foo/bar/22/","payload":"","payloadType":"date","x":140,"y":100,"wires":[["8b3f8de947600fc5"]]},{"id":"8b3f8de947600fc5","type":"template","z":"613df62afc8a16bf","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"sn;volume 1;volume 2;volume 3\n22;99;33;44;77\n11;99;33;44;77","output":"str","x":340,"y":100,"wires":[["ca46d07b493daf73"]]},{"id":"ca46d07b493daf73","type":"csv","z":"613df62afc8a16bf","name":"split csv per line","spec":"rfc","sep":";","hdrin":true,"hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":520,"y":100,"wires":[["73e884455bf7ed90"]]},{"id":"73e884455bf7ed90","type":"split","z":"613df62afc8a16bf","name":"Split line","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"key","property":"payload","x":100,"y":160,"wires":[["16cdc214b575df73"]]},{"id":"16cdc214b575df73","type":"delay","z":"613df62afc8a16bf","name":"Limit rate","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":240,"y":160,"wires":[["fa3896cec22f101b"]]},{"id":"fa3896cec22f101b","type":"change","z":"613df62afc8a16bf","name":"remove spaces","rules":[{"t":"change","p":"key","pt":"msg","from":" ","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":160,"wires":[["32b8895ed1daabac"]]},{"id":"32b8895ed1daabac","type":"template","z":"613df62afc8a16bf","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{{topic}}}{{{key}}}","output":"str","x":700,"y":160,"wires":[["e3d2e85561651a6a"]]},{"id":"e3d2e85561651a6a","type":"debug","z":"613df62afc8a16bf","name":"humidity","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":600,"y":220,"wires":[]}
4

Thanks for your help.

The example from E1cid is basicly this what i need and it also fit for me. It´s basicly the same like colin discript.

At the moment in only struggeling with the topic. I want that the SN is part of the topic to get a better overview in the broker.

The basic topic looks like
/foo/baar/xxxx/
The topic for the second row should be
/foo/baar/xxxx/22/22
/foo/baar/xxxx/22/99
/foo/baar/xxxx/22/33

The topic for the second row should be
/foo/baar/xxxx/11/11
/foo/baar/xxxx/11/99
/foo/baar/xxxx/11/33

How can i put the value from sn in to the topic? I guess it works if i use the msg.payload.sn as a variable but my JSON knowledge isn´t so good :frowning:

5

move payload.sn prior to the split, then add it to the template.
e.g

[{"id":"2b1db6cff0ff0516","type":"inject","z":"613df62afc8a16bf","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"foo/bar","payload":"","payloadType":"date","x":130,"y":100,"wires":[["8b3f8de947600fc5"]]},{"id":"8b3f8de947600fc5","type":"template","z":"613df62afc8a16bf","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"sn;volume 1;volume 2;volume 3\n22;99;33;44;77\n11;99;33;44;77","output":"str","x":340,"y":100,"wires":[["ca46d07b493daf73"]]},{"id":"ca46d07b493daf73","type":"csv","z":"613df62afc8a16bf","name":"split csv per line","spec":"rfc","sep":";","hdrin":true,"hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":520,"y":100,"wires":[["58617d5cd23ddf5a"]]},{"id":"58617d5cd23ddf5a","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"move","p":"payload.sn","pt":"msg","to":"sn","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":100,"wires":[["73e884455bf7ed90"]]},{"id":"73e884455bf7ed90","type":"split","z":"613df62afc8a16bf","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"key","property":"payload","x":90,"y":160,"wires":[["16cdc214b575df73"]]},{"id":"16cdc214b575df73","type":"delay","z":"613df62afc8a16bf","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":250,"y":160,"wires":[["fa3896cec22f101b"]]},{"id":"fa3896cec22f101b","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"change","p":"key","pt":"msg","from":" ","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":160,"wires":[["32b8895ed1daabac"]]},{"id":"32b8895ed1daabac","type":"template","z":"613df62afc8a16bf","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{{topic}}}/{{{sn}}}/{{{key}}}","output":"str","x":700,"y":160,"wires":[["e3d2e85561651a6a"]]},{"id":"e3d2e85561651a6a","type":"debug","z":"613df62afc8a16bf","name":"humidity","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":600,"y":220,"wires":[]}]

I note again topics should not start or end with a /.

6

Thanks for the hint again - i now :innocent:

The point is, in most our other tools we need to specifi a topic in this way [1] - so im used to this. Lately after the first deploy in nodered i remeber to remove the first and last / :rofl:

[1]
/foo/baar/xxxx/11/33

...and thanks for the help