String Format for Influxdb

General
1

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

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

2

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

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

I didn't understand what to do, sorry

4

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.

5

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

6

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

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

7

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.

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

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

9

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

10

ok thanks in the night post the debug

11

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.

12

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
......
......

13

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

14

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?

15

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.

16

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

17

Something like this possibly

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

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

19

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).

20

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