# String Format for Influxdb

**URL:** <https://discourse.nodered.org/t/string-format-for-influxdb/92483>\
**Category:** General\
**Created:** [18 October 2024 08:24 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483 "2024-10-18T08:24:35Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [18 October 2024 08:24 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/1 "2024-10-18T08:24:35Z")

</div>

data downloaded to the table:

_DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12_  
_2024/10/18 04:00:00,20,10,30,40,60,60,70,80,90,30,21,23_  
_2024/10/18 04:01:00,25,15,32,41,20,90,50,80,95,35,11,20_  
_2024/10/18 04:02:00,22,12,37,43,60,45,75,22,10,40,28,25_

```auto
let measurement = "Panel" // set this to your measurement
// convert to array by splitting on newline
let values = msg.payload.split("\n")
// discard the first one which is not required
values.shift()
// and the last which is empty
values.pop()
// map the array elements to the format required by influx batch node
values = values.map(function(value) {
    let splits = value.split(",")
    //node.send({payload: value})
    return {
        measurement: measurement, 
        fields: { Panel_01: Number(splits[1]), Panel_02: Number(splits[2]), Panel_03: Number(splits[3]), Panel_04: Number(splits[4]), Panel_05: Number(splits[5]), Panel_06: Number(splits[6]), Panel_07: Number(splits[7]), Panel_08: Number(splits[8]), Panel_09: Number(splits[9]), Panel_10: Number(splits[10]), Panel_11: Number(splits[11]), Panel_12: Number(splits[12])},
        tags: { W: 'panel' },
        timestamp: Date.parse(splits[0])
    }
})

msg.payload = values
return msg;

```

At night the colors are null, there is only the date with this formatting without any value

data downloaded to the table in the night

_DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12_  
_2024/10/18 04:00:00_  
_2024/10/18 04:01:00_  
_2024/10/18 04:02:00_

how to format the string, if there is no detected data present, but only the date

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 October 2024 08:42 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/2 "2024-10-18T08:42:44Z")

</div>

Use `node.warn()` statements inside your function node at various points to work out what is going on in the function. For example add a line in your map function

```auto
values = values.map(function(value) {
    node.warn(`value: ${value}`)
    let splits = value.split(",")

```

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [18 October 2024 08:47 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/3 "2024-10-18T08:47:03Z")

</div>

I didn't understand what to do, sorry

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 October 2024 09:04 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/4 "2024-10-18T09:04:16Z")

</div>

I showed you what to do. Modify the function as I suggested and see what appears in the debug pane. That will let you check what is happening in the function node.

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [18 October 2024 09:14 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/5 "2024-10-18T09:14:20Z")

</div>

the problem occurs when the database table is not populated with all the values, only the date is present

the database table becomes like this

_DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12_  
2024/10/01 00:19:00  
2024/10/01 00:20:00

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 October 2024 09:45 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/6 "2024-10-18T09:45:08Z")

</div>

Are you saying the the function is not doing what you expect?

Feed it into a debug node and show us what you see.

---

<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 October 2024 12:07 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/7 "2024-10-18T12:07:09Z")

</div>

Use the csv node and it can add null vales or empty strings. Add more field names in the csv node for full output, as example only shows 4 fields.

e.g.

```auto
[{"id":"a20fb67d23d2b2f3","type":"inject","z":"d1395164b4eec73e","name":"all values","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":180,"y":7880,"wires":[["49bfd0529226187c"]]},{"id":"49bfd0529226187c","type":"template","z":"d1395164b4eec73e","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12\n2024/10/18 04:00:00,20,10,30,40,60,60,70,80,90,30,21,23\n2024/10/18 04:01:00,25,15,32,41,20,90,50,80,95,35,11,20\n2024/10/18 04:02:00,22,12,37,43,60,45,75,22,10,40,28,25\n","output":"str","x":320,"y":7880,"wires":[["56901cd80a6984d4"]]},{"id":"56901cd80a6984d4","type":"csv","z":"d1395164b4eec73e","name":"","spec":"rfc","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\r\\n","temp":"date,Panel_01,Panel_02,Panel_03,Panel_04","skip":"1","strings":true,"include_empty_strings":false,"include_null_values":true,"x":490,"y":7880,"wires":[["f7deb17c7f7648f1"]]},{"id":"83cf93847411d717","type":"template","z":"d1395164b4eec73e","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12\n2024/10/18 04:00:00\n2024/10/18 04:01:00\n2024/10/18 04:02:00\n","output":"str","x":320,"y":7940,"wires":[["56901cd80a6984d4"]]},{"id":"f7deb17c7f7648f1","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"payload.fields","tot":"msg"},{"t":"set","p":"payload.tags","pt":"msg","to":"{\"w\": \"panel\"}","tot":"json"},{"t":"move","p":"payload.fields.date","pt":"msg","to":"payload.timestamp","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":7880,"wires":[["e668869dcc6456fb"]]},{"id":"5ba3e31523fd7887","type":"inject","z":"d1395164b4eec73e","name":"null values","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":7940,"wires":[["83cf93847411d717"]]},{"id":"e668869dcc6456fb","type":"join","z":"d1395164b4eec73e","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":"false","timeout":"","count":"","reduceRight":false,"x":810,"y":7880,"wires":[["ee01ad94d918e47a"]]},{"id":"ee01ad94d918e47a","type":"debug","z":"d1395164b4eec73e","name":"debug 2579","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":930,"y":7940,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [18 October 2024 13:07 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/8 "2024-10-18T13:07:49Z")

</div>

In the database table there is the date field and 12 fields

splits[0] contains the timestamp and splits[1] contains the value after the timestamp. If there are multiple values after the timestamp they will be in splits[1], splits[2] and so on

timestamp,pannel1,pannel2,pannel3,pannel4,pannel5,pannel6,pannel7,pannel8,pannel9,pannel10,pannel11,pannel12  
2024/10/09 16:52:00,34,33,32,32,33,34,32,33,34,32,32,33  
2024/10/09 16:53:00,33,32,32,32,32,33,32,32,33,32,32,33  
2024/10/09 16:54:00,33,32,32,32,32,33,33,32,33,32,32,33  
2024/10/09 16:55:00,33,33,32,32,32,33,33,32,33,32,33,33  
2024/10/09 16:56:00,33,32,32,32,32,33,32,32,32,32,32,32  
2024/10/09 16:57:00,33,32,32,32,32,33,33,33,33,32,33,33  
2024/10/09 16:58:00,33,33,32,32,32,33,32,32,33,32,32,33  
2024/10/09 16:59:00,33,33,32,32,32,33,33,32,34,32,32,33  
2024/10/09 17:00:00,34,34,33,32,33,34,32,33,34,32,33,34

_ **during the night the database table remains updated like this, without other populated values** _

timestamp,pannel1,pannel2,pannel3,pannel4,pannel5,pannel6,pannel7,pannel8,pannel9,pannel10,pannel11,pannel12  
2024/10/09 00:01:00  
2024/10/09 00:02:00  
2024/10/09 00:03:00  
2024/10/09 00:04:00  
2024/10/09 00:05:00  
2024/10/09 00:06:00  
2024/10/09 00:07:00  
2024/10/09 00:08:00  
2024/10/09 00:09:00

splits[1], splits[2], ecc,, ERROR

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 October 2024 13:27 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/9 "2024-10-18T13:27:07Z")

</div>

> [@muligamma](#):
>
> _ **during the night the database table remains updated like this, without other populated values** _
> 
> timestamp,pannel1,pannel2,pannel3,pannel4,pannel5,pannel6,pannel7,pannel8,pannel9,pannel10,pannel11,pannel12  
> 2024/10/09 00:01:00  
> 2024/10/09 00:02:00  
> 2024/10/09 00:03:00  
> 2024/10/09 00:04:00  
> 2024/10/09 00:05:00  
> 2024/10/09 00:06:00  
> 2024/10/09 00:07:00  
> 2024/10/09 00:08:00  
> 2024/10/09 00:09:00
> 
> splits[1], splits[2], ecc,, ERROR

Add a debug node showing what is going into your function so you can see what the faulty messages contain.

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [18 October 2024 13:33 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/10 "2024-10-18T13:33:29Z")

</div>

ok thanks in the night post the debug

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 October 2024 13:43 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/11 "2024-10-18T13:43:38Z")

</div>

If you set the debug node to output to the console then they will go to the node red log as well as the debug pane in the browser. This can make it easier to see things that happen when there is no-one watching.

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [19 October 2024 03:02 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/12 "2024-10-19T03:02:30Z")

</div>

`Error: A 400 Bad Request error occurred: {"error":"unable to parse 'Panels_Current,A=panel Panel_01=NaN,Panel_02=NaN,Panel_03=NaN,Panel_04=NaN,Panel_05=NaN,Panel_06=NaN,Panel_07=NaN,Panel_08=NaN,Panel_09=NaN,Panel_10=NaN,Panel_11=NaN,Panel_12=NaN 1729288800000': invalid number\nunable to parse 'Panels_Current,A=panel Panel_01=NaN,Panel_02=NaN,Panel_03=NaN,Panel_04=NaN,Panel_05=NaN,Panel_06=NaN,Panel_07=NaN,Panel_08=NaN,Panel_09=NaN,Panel_10=NaN,Panel_11=NaN,Panel_12=NaN 1729288860000': invalid number\nunable to parse 'Panels_Current,A=panel Panel_01=NaN,Panel_02=NaN,Panel_03=NaN,Panel_04=NaN,Panel_05=NaN,Panel_06=NaN,Panel_07=NaN,Panel_08=NaN,Panel_09=NaN,Panel_10=NaN,Panel_11=NaN,Panel_12=NaN`

**and the database contains only this**

DATETIME  
2024/10/19 00:00:00  
2024/10/19 00:01:00  
......  
......

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 October 2024 06:37 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/13 "2024-10-19T06:37:01Z")

</div>

Add a Switch node that prevents error payloads from reaching your function.

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [19 October 2024 07:03 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/14 "2024-10-19T07:03:38Z")

</div>

thank you you were really very kind and helpful

sometimes there is null data, not present

_DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12_  
_2024/10/18 04:00:00,,,,,,,,,,,,_

How can I set null values ​​to default to 0?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 October 2024 08:55 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/15 "2024-10-19T08:55:51Z")

</div>

> [@muligamma](#):
>
> How can I set null values ​​to default to 0

Do you really want to write 0 values to the database, when the value is not actually zero but is unknown?

If you do want to do that then in the function where the fields object is defined use  
`fields: { Panel_01: Number(splits[1] || 0), ...`  
so if splits[1] is undefined or null, it will instead put 0.

If I were doing this I would prefer to not write anything to the database for those timestamps where there is no data rather than put incorrect data in.

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [19 October 2024 09:27 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/16 "2024-10-19T09:27:45Z")

</div>

> [@Colin](#):
>
> If I were doing this I would prefer to not write anything to the database for those timestamps where there is no data rather than put incorrect data in.

you are right and how can I do in this case, but exclude only when all fields are empty

thanks so much for the generosity

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 October 2024 15:03 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/17 "2024-10-19T15:03:44Z")

</div>

Something like this possibly

```auto
[{"id":"a2ff9fd8b74f13cc","type":"function","z":"bdd7be38.d3b55","name":"Extract","func":"let measurement = \"Panel\" // set this to your measurement\nconst fieldNames = [\"Panel_01\", \"Panel_02\", \"Panel_03\", \"Panel_04\", \"Panel_05\", \"Panel_06\", \"Panel_07\", \"Panel_08\", \"Panel_09\", \"Panel_10\", \"Panel_11\", \"Panel_12\"]\n// convert to array by splitting on newline\nlet lines = msg.payload.split(\"\\n\")\n//node.warn(`lines: ${JSON.stringify(lines)}`)\n// discard the first one which is not required\nlines.shift()\n// and the last which is empty\nlines.pop()\nlet records = []\n// map the array elements to the format required by influx batch node\nlines.forEach((line) => {\n let values = line.split(\",\")\n let fields = {}\n let record = {\n measurement: measurement, \n tags: { W: 'panel' },\n timestamp: Date.parse(values[0])\n }\n let fieldsFound = false\n fieldNames.forEach( (fieldName, index) => {\n const value = values[index+1]\n //node.warn(`fieldName: ${fieldName}, value: ${value}`)\n // ignore empty fields\n if (typeof value === \"string\" && value.length > 0 ) {\n fields[fieldName] = Number(value)\n fieldsFound = true\n }\n })\n // ignore this line if no fields have been added\n if (fieldsFound) {\n // otherwise add the fields into the record object and add it into the array\n record.fields = fields\n records.push(record)\n }\n})\nmsg.payload = records\n// only pass on anything if some records have been added\n\nif (records.length === 0) {\n msg = null\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":500,"y":4460,"wires":[["8fef5419ad19edbb"]]},{"id":"cb9e5c01b2bc87eb","type":"inject","z":"bdd7be38.d3b55","name":"Some data","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"\"DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12 \\n2024/10/18 04:00:00,1,2,3,4,5,,,,,,,9,10,11,12\\n2024/10/18 04:01:00,,,,,,,,,,,,\\n2024/10/18 04:01:00,,,,,,,,,,,,7\\n\"","payloadType":"json","x":260,"y":4460,"wires":[["a2ff9fd8b74f13cc"]]},{"id":"c941e1afab95ddd1","type":"inject","z":"bdd7be38.d3b55","name":"No data","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"DATETIME,PA1,PA2,PA3,PA4,PA5,PA6,PA7,PA8,PA9,PA10,PA11,PA12 \\n2024/10/18 04:00:00,,,,,,,,,,,,\\n2024/10/18 04:00:30,,,,,,,,,,,,\\n","payloadType":"str","x":250,"y":4500,"wires":[["a2ff9fd8b74f13cc"]]},{"id":"8fef5419ad19edbb","type":"debug","z":"bdd7be38.d3b55","name":"debug 87","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":740,"y":4460,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [21 October 2024 12:41 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/18 "2024-10-21T12:41:56Z")

</div>

thank you. how can i create a sensor in hassio, and send the data of each panels

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [21 October 2024 13:35 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/19 "2024-10-21T13:35:59Z")

</div>

I know nothing about Home Assistant, and few here do, generally preferring to use node-red for everything. You will probably need to ask on an HA forum (or drop HA and just use NR).

---

<div class="post-metadata">

**Author:** ![muligamma](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/muligamma/32/95568_2.png) [@muligamma](https://discourse.nodered.org/u/muligamma)\
**Post date:** [30 October 2024 07:56 UTC](https://discourse.nodered.org/t/string-format-for-influxdb/92483/20 "2024-10-30T07:56:02Z")

</div>

thank you

how can i check the connection of the server from where i download the data there are no errors, before importing into the database

[Next page](https://discourse.nodered.org/t/string-format-for-influxdb/92483.md?page=2)