Hello everyone,
I have the following partial flow (original thread Problems with MQTT flow after Excel import and msg modifications - #8 by E1cid ) where I have an array that can contain a lot of characters.
What I would like to do is split after 500 characters.
//EDIT: I tried a second approach of explanation in my next post 
So the first 500 characters should end up having the topic "cmnd/tasmota_45147C/Rule1" and the next 500 should have "cmnd/tasmota_45147C/Rule1 +" and all following batches of 500 characters should also have "cmnd/tasmota_45147C/Rule1 +".
cmnd/tasmota_45147C/Rule1 : msg.topic : string[25]
"cmnd/tasmota_45147C/Rule1"
with (sorry, did not count the characters for this example)
cmnd/tasmota_45147C/Rule1 : msg.payload : string[457]
"ON energy#power[1]>=%mem1% DO IF ((var1!=%mem2%)) powerdelta1 %mem2%; var1 %mem2% ENDIF BREAK ON energy#power[1]>=%mem3% DO IF ((var1!=%mem4%)) powerdelta1 %mem4%; var1 %mem4% ENDIF BREAK ON energy#power[1]>=%mem5% DO IF ((var1!=%mem6%)) powerdelta1 %mem6%; var1 %mem6% ENDIF BREAK ON energy#power[1]>=%mem7% DO IF ((var1!=%mem8%)) powerdelta1 %mem8%; var1 %mem8% ENDIF BREAK ON energy#power[1]<%mem7% DO IF ((var1!=%mem10%)) powerdelta1 %mem10%; var1 %mem10% ENDIF ature<%var16% DO Backlog var16 %value%; status 10; var15 %value%; add15 2; sub16 2 ENDON"
and then
"cmnd/tasmota_45147C/Rule1 +"
with the next 500 charaters of the payload.
So in the end I get multiple sets of topic+payload to publish as one mqtt message
Does anybody know how I can achieve this?
I am not sure why this extract of my big flow stops working after the join node.
The debug after join should output
{"rule1":"ON energy#power[1]>=%mem1% DO IF ((var1!=%mem2%)) powerdelta1 %mem2%; var1 %mem2% ENDIF BREAK ON energy#power[1]>=%mem3% DO IF ((var1!=%mem4%)) powerdelta1 %mem4%; var1 %mem4% ENDIF BREAK ON energy#power[1]>=%mem5% DO IF ((var1!=%mem6%)) powerdelta1 %mem6%; var1 %mem6% ENDIF BREAK ON energy#power[1]>=%mem7% DO IF ((var1!=%mem8%)) powerdelta1 %mem8%; var1 %mem8% ENDIF BREAK ON energy#power[1]<%mem7% DO IF ((var1!=%mem10%)) powerdelta1 %mem10%; var1 %mem10% ENDIF ENDON ON energy#power[2]>=%mem1% DO IF ((var2!=%mem2%)) powerdelta2 %mem2%; var2 %mem2% ENDIF BREAK ON energy#power[2]>=%mem3% DO IF ((var2!=%mem4%)) powerdelta2 %mem4%; var2 %mem4% ENDIF BREAK ON energy#power[2]>=%mem5% DO IF ((var2!=%mem6%)) powerdelta2 %mem6%; var2 %mem6% ENDIF BREAK ON energy#power[2]>=%mem7% DO IF ((var2!=%mem8%)) powerdelta2 %mem8%; var2 %mem8% ENDIF BREAK ON energy#power[2]<%mem7% DO IF ((var2!=%mem10%)) powerdelta2 %mem10%; var2 %mem10% ENDIF ENDON ON energy#power[3]>=%mem1% DO IF ((var3!=%mem2%)) powerdelta3 %mem2%; var3 %mem2% ENDIF BREAK ON energy#power[3]>=%mem3% DO IF ((var3!=%mem4%)) powerdelta3 %mem4%; var3 %mem4% ENDIF BREAK ON energy#power[3]>=%mem5% DO IF ((var3!=%mem6%)) powerdelta3 %mem6%; var3 %mem6% ENDIF BREAK ON energy#power[3]>=%mem7% DO IF ((var3!=%mem8%)) powerdelta3 %mem8%; var3 %mem8% ENDIF BREAK ON energy#power[3]<%mem7% DO IF ((var3!=%mem10%)) powerdelta3 %mem10%; var3 %mem10% ENDIF ENDON
","topic":"cmnd/tasmota_45147C"}
But I hope it is clear what it does right now. Happy to explain further or copy more info from the flow.
[
{
"id": "4876690d41b4e54a",
"type": "file in",
"z": "4917828a0c65f3b4",
"name": "Select Excel.xlsx to read as binary buffer",
"filename": "C:\\00_Home Automation\\IoT_Overview.xlsm",
"filenameType": "str",
"format": "",
"chunk": false,
"sendError": false,
"allProps": false,
"x": 790,
"y": 450,
"wires": [
[
"470afdf1f5ae8bea"
]
]
},
{
"id": "470afdf1f5ae8bea",
"type": "book",
"z": "4917828a0c65f3b4",
"name": "Workbook",
"raw": false,
"x": 1040,
"y": 450,
"wires": [
[
"af19443bacd024a6"
]
]
},
{
"id": "af19443bacd024a6",
"type": "sheet",
"z": "4917828a0c65f3b4",
"name": "Worksheet \"Tasmota\"",
"sheetName": "Tasmota",
"x": 1230,
"y": 450,
"wires": [
[
"1e41d24fa32b08aa"
]
]
},
{
"id": "ef106fcc4fd092ac",
"type": "sheet-to-json",
"z": "4917828a0c65f3b4",
"name": "topic",
"raw": "false",
"range": "",
"header": "A",
"blankrows": true,
"x": 1659,
"y": 450,
"wires": [
[
"2b599f39186e2c56",
"90461ae9f9fb8819"
]
]
},
{
"id": "2b599f39186e2c56",
"type": "debug",
"z": "4917828a0c65f3b4",
"name": "topic",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1839,
"y": 490,
"wires": []
},
{
"id": "90461ae9f9fb8819",
"type": "link out",
"z": "4917828a0c65f3b4",
"name": "link out topic",
"mode": "link",
"links": [
"b3e51ed4ea0fd757"
],
"x": 1859,
"y": 450,
"wires": [],
"l": true
},
{
"id": "37fbca160225293c",
"type": "sheet-to-json",
"z": "4917828a0c65f3b4",
"name": "rule1",
"raw": "false",
"range": "",
"header": "A",
"blankrows": true,
"x": 1680,
"y": 560,
"wires": [
[
"c874e5cc0fd9938c",
"9c3f6a4da5156f6f"
]
]
},
{
"id": "c874e5cc0fd9938c",
"type": "debug",
"z": "4917828a0c65f3b4",
"name": "rule1",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1860,
"y": 600,
"wires": []
},
{
"id": "9c3f6a4da5156f6f",
"type": "link out",
"z": "4917828a0c65f3b4",
"name": "link out rule1",
"mode": "link",
"links": [
"23d2f90904b7b617"
],
"x": 1880,
"y": 560,
"wires": [],
"l": true
},
{
"id": "ed910d310e840287",
"type": "inject",
"z": "4917828a0c65f3b4",
"name": "start rules",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
},
{
"p": "setting",
"v": "rules",
"vt": "str"
},
{
"p": "selectRange",
"v": "Ax:Ay",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": "",
"topic": "",
"payload": "",
"payloadType": "str",
"x": 160,
"y": 630,
"wires": [
[
"49bdb30230ca9d8e"
]
]
},
{
"id": "bf1bddec7d155549",
"type": "file in",
"z": "4917828a0c65f3b4",
"name": "Select Excel.xlsx to read as binary buffer",
"filename": "C:\\00_Home Automation\\IoT_Overview.xlsm",
"filenameType": "str",
"format": "",
"chunk": false,
"sendError": false,
"allProps": false,
"x": 811,
"y": 560,
"wires": [
[
"55492284239c1cf1"
]
]
},
{
"id": "55492284239c1cf1",
"type": "book",
"z": "4917828a0c65f3b4",
"name": "Workbook",
"raw": false,
"x": 1061,
"y": 560,
"wires": [
[
"9a1ca1557968905d"
]
]
},
{
"id": "9a1ca1557968905d",
"type": "sheet",
"z": "4917828a0c65f3b4",
"name": "Worksheet \"Tasmota\"",
"sheetName": "Tasmota",
"x": 1251,
"y": 560,
"wires": [
[
"55fc55c834cf8a65"
]
]
},
{
"id": "1e41d24fa32b08aa",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "Change Column to G",
"rules": [
{
"t": "change",
"p": "selectRange",
"pt": "msg",
"from": "A",
"fromt": "str",
"to": "G",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1470,
"y": 450,
"wires": [
[
"ef106fcc4fd092ac"
]
]
},
{
"id": "55fc55c834cf8a65",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "Change Column to T",
"rules": [
{
"t": "change",
"p": "selectRange",
"pt": "msg",
"from": "A",
"fromt": "str",
"to": "T",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1491,
"y": 560,
"wires": [
[
"37fbca160225293c"
]
]
},
{
"id": "49bdb30230ca9d8e",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "Cell Range",
"rules": [
{
"t": "change",
"p": "selectRange",
"pt": "msg",
"from": "x",
"fromt": "str",
"to": "11",
"tot": "str"
},
{
"t": "change",
"p": "selectRange",
"pt": "msg",
"from": "y",
"fromt": "str",
"to": "11",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 380,
"y": 530,
"wires": [
[
"09f033bbd9876bbf"
]
]
},
{
"id": "09f033bbd9876bbf",
"type": "switch",
"z": "4917828a0c65f3b4",
"name": "Router",
"property": "setting",
"propertyType": "msg",
"rules": [
{
"t": "nempty"
},
{
"t": "eq",
"v": "result",
"vt": "str"
},
{
"t": "eq",
"v": "ip",
"vt": "str"
},
{
"t": "eq",
"v": "template",
"vt": "str"
},
{
"t": "eq",
"v": "settings",
"vt": "str"
},
{
"t": "eq",
"v": "timers",
"vt": "str"
},
{
"t": "eq",
"v": "rules",
"vt": "str"
}
],
"checkall": "true",
"repair": false,
"outputs": 7,
"x": 520,
"y": 530,
"wires": [
[
"4876690d41b4e54a"
],
[],
[],
[],
[],
[],
[
"bf1bddec7d155549"
]
]
},
{
"id": "b3e51ed4ea0fd757",
"type": "link in",
"z": "4917828a0c65f3b4",
"name": "link in topic",
"links": [
"5b7db8364882eb62",
"e843196c4aa6e844",
"74b1cc4a1f7f173e",
"43f249b71d98e440",
"6871bbea69ab6f09",
"90461ae9f9fb8819"
],
"x": 190,
"y": 740,
"wires": [
[
"eb7bb4d7a11d158c"
]
],
"l": true
},
{
"id": "23d2f90904b7b617",
"type": "link in",
"z": "4917828a0c65f3b4",
"name": "link in rule1",
"links": [
"4a97bb7898cf4879",
"9c3f6a4da5156f6f"
],
"x": 100,
"y": 810,
"wires": [
[
"def562e18bfb43a2"
]
],
"l": true
},
{
"id": "600366a0f68799ab",
"type": "debug",
"z": "4917828a0c65f3b4",
"name": "rule1 payload",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1900,
"y": 810,
"wires": []
},
{
"id": "807c9d936a26caeb",
"type": "debug",
"z": "4917828a0c65f3b4",
"name": "rule1 topic",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "topic",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1890,
"y": 850,
"wires": []
},
{
"id": "9665e9441d8dbe60",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "change topic and payload (string array)",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "$keys($$.payload[0])",
"tot": "jsonata"
},
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "$$.payload.*",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 650,
"y": 810,
"wires": [
[
"c6527056c4870d77"
]
]
},
{
"id": "c6527056c4870d77",
"type": "join",
"z": "4917828a0c65f3b4",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 870,
"y": 810,
"wires": [
[
"1ef97b5567699f95"
]
]
},
{
"id": "4b9e9731d9cd6ad6",
"type": "split",
"z": "4917828a0c65f3b4",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 1170,
"y": 810,
"wires": [
[
"fa6f1dbb8b94bb9a"
]
]
},
{
"id": "fa6f1dbb8b94bb9a",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "move payloads to topic and payload",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "payload.topic",
"tot": "msg"
},
{
"t": "move",
"p": "payload.rule1",
"pt": "msg",
"to": "payload",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1380,
"y": 810,
"wires": [
[
"5875817a2b85eea9"
]
]
},
{
"id": "5875817a2b85eea9",
"type": "delay",
"z": "4917828a0c65f3b4",
"name": "rate limit (prevent flooding)",
"pauseType": "rate",
"timeout": "1",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"outputs": 1,
"x": 1660,
"y": 810,
"wires": [
[
"600366a0f68799ab",
"807c9d936a26caeb"
]
]
},
{
"id": "1ef97b5567699f95",
"type": "change",
"z": "4917828a0c65f3b4",
"name": "add \"/Rule1\"",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "payload.topic#$i.{\"topic\": $ & \"/Rule1\", \"rule1\": $$.payload.rule1[$i]}[]",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1010,
"y": 810,
"wires": [
[
"4b9e9731d9cd6ad6"
]
]
},
{
"id": "def562e18bfb43a2",
"type": "function",
"z": "4917828a0c65f3b4",
"name": "Change key to rule1",
"func": "msg.payload = msg.payload.map((obj) => {\n const o = Object.values(obj);\n return { \"rule1\": o[0] };\n});\nreturn msg;\n\n\n\n//alternative:\n // loop over the array\n//msg.payload = msg.payload.map((obj) => {\n // split the object in an array of key/value pairs\n// const o = Object.entries(obj);\n // assign the value to \"IP\" key\n// return { \"IP\": o[0][1] };\n//});\n\n//return msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 810,
"wires": [
[
"9665e9441d8dbe60"
]
]
},
{
"id": "eb7bb4d7a11d158c",
"type": "function",
"z": "4917828a0c65f3b4",
"name": "Change key to topic",
"func": "msg.payload = msg.payload.map((obj) => {\n const o = Object.values(obj);\n return { \"topic\": o[0] };\n});\nreturn msg;\n\n//in this output it's an array that contains two indexes and each index contains an object with the key \"IPAddress1 192.168.0.201; savedata; restart 1\"\n\n//alternative:\n // loop over the array\n//msg.payload = msg.payload.map((obj) => {\n // split the object in an array of key/value pairs\n// const o = Object.entries(obj);\n // assign the value to \"IP\" key\n// return { \"IP\": o[0][1] };\n//});\n\n//return msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 740,
"wires": [
[
"9665e9441d8dbe60"
]
]
}
]
