# MQTT Messages to \*.CSV?

**URL:** <https://discourse.nodered.org/t/mqtt-messages-to-csv/47313>\
**Category:** General\
**Created:** [17 June 2021 18:49 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313 "2021-06-17T18:49:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Steinspiel](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steinspiel/32/30095_2.png) [@Steinspiel](https://discourse.nodered.org/u/Steinspiel)\
**Post date:** [17 June 2021 18:49 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/1 "2021-06-17T18:49:44Z")

</div>

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

```auto
[{"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"]]}]

```

 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/4/14ea96fc6caa1ee114b2bea485037b6e43f74f3c.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [17 June 2021 19:29 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/2 "2021-06-17T19:29:35Z")

</div>

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.

```auto
[{"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":[[]]}]

```

---

<div class="post-metadata">

**Author:** ![Steinspiel](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steinspiel/32/30095_2.png) [@Steinspiel](https://discourse.nodered.org/u/Steinspiel)\
**Post date:** [18 June 2021 18:55 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/3 "2021-06-18T18:55:20Z")

</div>

> [@E1cid](#):
>
> You are passing a string,  
> [...]

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

```auto
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

![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9181ee7c8510fee7ef844e9bbe081f96dc00507.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [18 June 2021 19:25 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/4 "2021-06-18T19:25:42Z")

</div>

> **[String functions · JSONata](https://docs.jsonata.org/string-functions.html)**
>
> \## \`$string()\`

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.

---

<div class="post-metadata">

**Author:** ![Steinspiel](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steinspiel/32/30095_2.png) [@Steinspiel](https://discourse.nodered.org/u/Steinspiel)\
**Post date:** [18 June 2021 20:13 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/5 "2021-06-18T20:13:27Z")

</div>

> [@E1cid](#):
>
> $join($split($$.payload," "),",")

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [17 August 2021 20:14 UTC](https://discourse.nodered.org/t/mqtt-messages-to-csv/47313/6 "2021-08-17T20:14:25Z")

</div>

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