Need help! Get information from payload to put in graphs and files. Excel or Database

I have been working on this for multiple years off and on. I have had other solutions that worked but nothing that satisficed my actual needs/wants. This project uses WEL Server which provides a service of download Excel files or there is an xml link for live data needs. Also he provides a web page for live data viewing on a SVG background. EX: http://www.welserver.com/WEL0827/

I have about 40 devices and have tried to keep it simple while I learn Node-RED but I seem to be missing the big picture. Any help greatly appreciated. attaching my flow with template of XML.

[{"id":"9af9141769558c7a","type":"tab","label":"WEL_Help","disabled":false,"info":""},{"id":"860cc481dac8d6e6","type":"inject","z":"9af9141769558c7a","name":"get","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":220,"wires":[["5baa8593c165b01c"]]},{"id":"692a92076d6f5d93","type":"debug","z":"9af9141769558c7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1250,"y":140,"wires":[]},{"id":"77445a86aef706a8","type":"xml","z":"9af9141769558c7a","name":"","property":"payload","attr":"","chr":"","x":570,"y":220,"wires":[["2739b9ed5855e12f","84e93fd706aaf913","7958b92a9e6da049"]]},{"id":"84e93fd706aaf913","type":"function","z":"9af9141769558c7a","name":"","func":"var temp_top = Object.values(msg.payload.devices.device[2]);\nvar temp_top_N = msg.payload.devices.device[2].name[10];\nmsg.payload = temp_top[1];\nnode.send(msg,null);\nmsg.payload = msg.payload.devices.device[2];\nmsg.payload = temp_top_N[2];\nreturn [null,msg];\n\n/*\nvar temp_hum = Object.values(msg.payload);\nmsg.payload = parseFloat(temp_hum[1]);\nnode.send(msg,null);\nmsg.payload = parseFloat(temp_hum[2]);\nreturn [null,msg];\n*/","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":220,"wires":[["7658fc21e08d24e8"],["7658fc21e08d24e8"]]},{"id":"74b1bf971c03d3a7","type":"change","z":"9af9141769558c7a","name":"","rules":[{"t":"set","p":"payload[0]","pt":"msg","to":"$number(payload[0])\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1050,"y":140,"wires":[["692a92076d6f5d93"]]},{"id":"2739b9ed5855e12f","type":"function","z":"9af9141769558c7a","name":"","func":"msg.payload = msg.payload.devices.device[2].value;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":140,"wires":[["74b1bf971c03d3a7"]]},{"id":"7658fc21e08d24e8","type":"debug","z":"9af9141769558c7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1030,"y":220,"wires":[]},{"id":"7958b92a9e6da049","type":"debug","z":"9af9141769558c7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":770,"y":280,"wires":[]},{"id":"5baa8593c165b01c","type":"template","z":"9af9141769558c7a","name":"XML Data WEL_Server","field":"payload","fieldType":"msg","format":"handlebars","syntax":"plain","template":"\n<devices>\n<device>\n<name>date</name>\n<value>08/01/2021</value>\n</device>\n<device>\n<name>time</name>\n<value>10:59:21</value>\n</device>\n<device>\n<name>server</name>\n<value>76.212501</value>\n</device>\n<device>\n<name>util_room</name>\n<value>88.587501</value>\n</device>\n<device>\n<name>3_supply</name>\n<value>84.199989</value>\n</device>\n<device>\n<name>3_return</name>\n<value>91.624992</value>\n</device>\n<device>\n<name>2_supply</name>\n<value>144.837478</value>\n</device>\n<device>\n<name>2_return</name>\n<value>138.087478</value>\n</device>\n<device>\n<name>1_supply</name>\n<value>143.149978</value>\n</device>\n<device>\n<name>1_return</name>\n<value>82.624992</value>\n</device>\n<device>\n<name>back_return_A4</name>\n<value>92.974998</value>\n</device>\n<device>\n<name>back_supply_A4</name>\n<value>109.737495</value>\n</device>\n<device>\n<name>back_supply_B4</name>\n<value>88.587501</value>\n</device>\n<device>\n<name>back_return_B4</name>\n<value>87.124992</value>\n</device>\n<device>\n<name>dh_boiler_sup</name>\n<value>146.749984</value>\n</device>\n<device>\n<name>dh_code_supply</name>\n<value>123.574989</value>\n</device>\n<device>\n<name>dh_cold_supply</name>\n<value>58.887500</value>\n</device>\n<device>\n<name>dh_recirc</name>\n<value>111.649986</value>\n</device>\n<device>\n<name>dh_boiler_ret</name>\n<value>142.587478</value>\n</device>\n<device>\n<name>dh_tank_out</name>\n<value>138.087478</value>\n</device>\n<device>\n<name>dh_tank_in</name>\n<value>56.524997</value>\n</device>\n<device>\n<name>sys_supply</name>\n<value>85.887489</value>\n</device>\n<device>\n<name>sys_return</name>\n<value>99.162498</value>\n</device>\n<device>\n<name>apt_7_zone_1</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_2</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_3</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_4</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_5</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_6</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_7</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_8</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>ac_drain_water</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>basement_supply</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>basement_return</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>ac_air_return</name>\n<value>74.525001</value>\n</device>\n<device>\n<name>ac_air_supply</name>\n<value>76.212501</value>\n</device>\n<device>\n<name>apt_frt_hw</name>\n<value>81.499992</value>\n</device>\n<device>\n<name>apt_frt_ac_cold</name>\n<value>64.849998</value>\n</device>\n<device>\n<name>ht-mani-pos-7</name>\n<value>82.512489</value>\n</device>\n<device>\n<name>ht-mani-pos-6</name>\n<value>85.887489</value>\n</device>\n<device>\n<name>ht-mani-pos-5</name>\n<value>89.037498</value>\n</device>\n<device>\n<name>ht-mani-pos-4</name>\n<value>93.199989</value>\n</device>\n<device>\n<name>ht-mani-pos-3</name>\n<value>93.199989</value>\n</device>\n<device>\n<name>ht-mani-pos-2</name>\n<value>97.024986</value>\n</device>\n<device>\n<name>ht-mani-pos-1</name>\n<value>112.324989</value>\n</device>\n</devices>","output":"str","x":370,"y":220,"wires":[["77445a86aef706a8"]]},{"id":"0614bbcb927a22fe","type":"file","z":"9af9141769558c7a","name":"","filename":"/data/data/test.csv","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":590,"y":440,"wires":[[]]},{"id":"db6d5d46f7f0879c","type":"function","z":"9af9141769558c7a","name":"","func":"msg.payload.map(function(item){ return {payload:item} });\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":440,"wires":[[]]},{"id":"2f497606dcd4f80b","type":"csv","z":"9af9141769558c7a","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":150,"y":440,"wires":[[]]}]

So you flow has some XML and you seem to grab a couple of values an send them to a debug.

What are you actually trying to get out of this flow?

e.g. your data ...

<devices>
 <device>
  <name>date</name> 
  <value>08/01/2021</value> 
 </device>
 <device>
  <name>time</name>
  <value>10:59:21</value>
 </device>
 <!-- and so on -->
</device1>

what do you want it to end up being?

Thanks Steve-Mcl for the reply. I want to emulate what is currently being done off site eventually. I'm getting lost looking at to many things so I broke it down to one grab and could not get that either. I download an xml file every 5mins then need to take the information and store it then display it.
Time, name, temperature of each device.
It is the array that seems to be screwing my thought process, also what would be the best storage method. Influx, SQL or Excel broken into daily, weekly, monthly files....

Here is something to get you started.

I did the conversion from the messy XML format to help you get up and running...

KQ7zMfYfor

The flow to import (use CTRL-I)...

[{"id":"860cc481dac8d6e6","type":"inject","z":"9af9141769558c7a","name":"get","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":1830,"y":120,"wires":[["5baa8593c165b01c"]]},{"id":"77445a86aef706a8","type":"xml","z":"9af9141769558c7a","name":"","property":"payload","attr":"","chr":"","x":2090,"y":180,"wires":[["627d8ce34e3928e3"]]},{"id":"7958b92a9e6da049","type":"debug","z":"9af9141769558c7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":2050,"y":240,"wires":[]},{"id":"5baa8593c165b01c","type":"template","z":"9af9141769558c7a","name":"XML Data WEL_Server","field":"payload","fieldType":"msg","format":"handlebars","syntax":"plain","template":"\n<devices>\n<device>\n<name>date</name>\n<value>08/01/2021</value>\n</device>\n<device>\n<name>time</name>\n<value>10:59:21</value>\n</device>\n<device>\n<name>server</name>\n<value>76.212501</value>\n</device>\n<device>\n<name>util_room</name>\n<value>88.587501</value>\n</device>\n<device>\n<name>3_supply</name>\n<value>84.199989</value>\n</device>\n<device>\n<name>3_return</name>\n<value>91.624992</value>\n</device>\n<device>\n<name>2_supply</name>\n<value>144.837478</value>\n</device>\n<device>\n<name>2_return</name>\n<value>138.087478</value>\n</device>\n<device>\n<name>1_supply</name>\n<value>143.149978</value>\n</device>\n<device>\n<name>1_return</name>\n<value>82.624992</value>\n</device>\n<device>\n<name>back_return_A4</name>\n<value>92.974998</value>\n</device>\n<device>\n<name>back_supply_A4</name>\n<value>109.737495</value>\n</device>\n<device>\n<name>back_supply_B4</name>\n<value>88.587501</value>\n</device>\n<device>\n<name>back_return_B4</name>\n<value>87.124992</value>\n</device>\n<device>\n<name>dh_boiler_sup</name>\n<value>146.749984</value>\n</device>\n<device>\n<name>dh_code_supply</name>\n<value>123.574989</value>\n</device>\n<device>\n<name>dh_cold_supply</name>\n<value>58.887500</value>\n</device>\n<device>\n<name>dh_recirc</name>\n<value>111.649986</value>\n</device>\n<device>\n<name>dh_boiler_ret</name>\n<value>142.587478</value>\n</device>\n<device>\n<name>dh_tank_out</name>\n<value>138.087478</value>\n</device>\n<device>\n<name>dh_tank_in</name>\n<value>56.524997</value>\n</device>\n<device>\n<name>sys_supply</name>\n<value>85.887489</value>\n</device>\n<device>\n<name>sys_return</name>\n<value>99.162498</value>\n</device>\n<device>\n<name>apt_7_zone_1</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_2</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_3</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>apt_7_zone_4</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_5</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_6</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_7</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>zone_control_8</name>\n<value>0.000000</value>\n</device>\n<device>\n<name>ac_drain_water</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>basement_supply</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>basement_return</name>\n<value>75.424995</value>\n</device>\n<device>\n<name>ac_air_return</name>\n<value>74.525001</value>\n</device>\n<device>\n<name>ac_air_supply</name>\n<value>76.212501</value>\n</device>\n<device>\n<name>apt_frt_hw</name>\n<value>81.499992</value>\n</device>\n<device>\n<name>apt_frt_ac_cold</name>\n<value>64.849998</value>\n</device>\n<device>\n<name>ht-mani-pos-7</name>\n<value>82.512489</value>\n</device>\n<device>\n<name>ht-mani-pos-6</name>\n<value>85.887489</value>\n</device>\n<device>\n<name>ht-mani-pos-5</name>\n<value>89.037498</value>\n</device>\n<device>\n<name>ht-mani-pos-4</name>\n<value>93.199989</value>\n</device>\n<device>\n<name>ht-mani-pos-3</name>\n<value>93.199989</value>\n</device>\n<device>\n<name>ht-mani-pos-2</name>\n<value>97.024986</value>\n</device>\n<device>\n<name>ht-mani-pos-1</name>\n<value>112.324989</value>\n</device>\n</devices>","output":"str","x":1890,"y":180,"wires":[["77445a86aef706a8"]]},{"id":"627d8ce34e3928e3","type":"function","z":"9af9141769558c7a","name":"Array to Object","func":"\n/** @type {array} */ \nconst data = msg.payload.devices.device\nconst flatter = data.map(el => {\n    let val = el.value[0];\n    if (isNumeric(val)) {\n        val = +val;\n    }\n    return [el.name[0], val ]\n})\nconst obj = Object.fromEntries(flatter);\n\nfunction isNumeric(str) {\n    if (typeof str != \"string\") return false // we only process strings!  \n    return !isNaN(str) && // use type coercion to parse the _entirety_ of the string (`parseFloat` alone does not do this)...\n        !isNaN(parseFloat(str)) // ...and ensure strings of whitespace fail\n}\n\nmsg.payload = obj;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1860,"y":240,"wires":[["7958b92a9e6da049","cb8d9731056bbfb1"]]},{"id":"cb8d9731056bbfb1","type":"ui_gauge","z":"9af9141769558c7a","name":"","group":"f15febe0.9de5c8","order":6,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{payload.server}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","className":"","x":2030,"y":300,"wires":[]},{"id":"f15febe0.9de5c8","type":"ui_group","name":"Solar","tab":"53ba0b2c.da2874","order":2,"disp":true,"width":"6","collapse":false},{"id":"53ba0b2c.da2874","type":"ui_tab","name":"Solar","icon":"dashboard","order":9,"disabled":false,"hidden":false}]

Thank you very much Steve, very much appreciated now I will try to proceed further. I would have never came up with that Array to Object on my own. And you even went as far as the first gauge for an example. As for the XML I have no control of the format that I receive it in. There is another version I will try running against but as far as I can tell it is Capital's as the difference.

I was wrong there is a difference between the two xml files. data.xml does not work with solution because it is array.object where wel.xml does work as array.array. Other than that they provide the same information.

In your Array to Object can the return values be rounded to 1 or 2 decimal places. These are temperatures and six decimals is not needed in the data file.

I tried including my flow (disabled needs to be enabled) But too many characters/too large just for the sake if anybody else needs it as an example especially if any WEL Server users find it by Google. It does everything that the third party server did for me almost, need charts yet. I have daily monthly csv files, need to complete flow to delete daily files as the monthly changes month yet.

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