MQTT Messages to *.CSV?

General
1

I want to write MQTT messages in a * .CSV file to open them with Excel.
The MQTT messages contain only words ...

Problem 1: The * .CSV contains {"col1": "A-Word"} or {"col1": "B-Word"}. Can you tell me how I can achieve that only "A-Word" is written without "col1"?

Problem 2: How can I insert a timestamp into the * .CSV for every MQTT message? Example: "12021-06-17T17:54:28.941Z"
I like the "Date / Time Formatter Node" a lot, but I don't know how to add it to the * .CSV.

Thank you for the reading

[{"id":"13f95055.87187","type":"file","z":"9fa9bd74.48214","name":"","filename":"test.csv","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":900,"y":380,"wires":[[]]},{"id":"c60e2e94.f6fd","type":"inject","z":"9fa9bd74.48214","name":"Dummy MQTT - A Word","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"A-Word","payloadType":"str","x":520,"y":340,"wires":[["c2929e2f.0881a"]]},{"id":"c2929e2f.0881a","type":"csv","z":"9fa9bd74.48214","name":"","sep":",","hdrin":false,"hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":false,"include_null_values":true,"x":730,"y":380,"wires":[["13f95055.87187"]]},{"id":"c889df98.6250d","type":"moment","z":"9fa9bd74.48214","name":"","topic":"","input":"","inputType":"msg","inTz":"Europe/Berlin","adjAmount":0,"adjType":"days","adjDir":"add","format":"","locale":"C","output":"payload","outputType":"msg","outTz":"Europe/Berlin","x":520,"y":560,"wires":[["2d694b86.7bc9d4"]]},{"id":"27347a5b.374926","type":"inject","z":"9fa9bd74.48214","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":280,"y":560,"wires":[["c889df98.6250d"]]},{"id":"2d694b86.7bc9d4","type":"debug","z":"9fa9bd74.48214","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":770,"y":560,"wires":[]},{"id":"28130445.744e6c","type":"inject","z":"9fa9bd74.48214","name":"Dummy MQTT - B Word","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"B-Word","payloadType":"str","x":520,"y":420,"wires":[["c2929e2f.0881a"]]}]

698×330 40.4 KB

2

You are passing a string, when you pass that through a cvs node, it sees it as a csv with one item, so it creates an object called col1 key.

You really just need to concatenate a string. You can do this in a change node,, and use a JSONata expression to join "A word" with a comma and a date, which you can create format and select timezone using $monent().

here is an example.

[{"id":"c60e2e94.f6fd","type":"inject","z":"c74669a0.6a34f8","name":"Dummy MQTT - A Word","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"A-Word","payloadType":"str","x":310,"y":3660,"wires":[["b8253568.5dbfa"]]},{"id":"b8253568.5dbfa","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload & \",\" & $moment().format(\"DD-MM-YYYYThh:mm:ss.sssZ\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":3700,"wires":[["2d694b86.7bc9d4","13f95055.87187"]]},{"id":"28130445.744e6c","type":"inject","z":"c74669a0.6a34f8","name":"Dummy MQTT - B Word","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"B-Word","payloadType":"str","x":310,"y":3740,"wires":[["b8253568.5dbfa"]]},{"id":"2d694b86.7bc9d4","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":790,"y":3660,"wires":[]},{"id":"13f95055.87187","type":"file","z":"c74669a0.6a34f8","name":"","filename":"test.csv","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":850,"y":3700,"wires":[[]]}]
3

Hello,
Thanks, it's perfect! :wink:
I adjusted the code a bit so everything is as I need it.

payload & "," & $moment().format("DD-MM-YYYY") & "," & $moment().format("kk:mm:ss")

A similar little question: if the MQTT message consists of two words, can you split it into two words with the change node?

Thanks for reading ... and have a nice weekend

4

there is
$split()
$stringBefore()
$stringAfter()
$substring()
$replace()
etc

$join($split($$.payload," "),",") would split the string at space, then join the array back with comma's between.

5

I thank you for your help. Now it is really perfect! :slight_smile:

6

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