Saving MQTT JSON data to an excel spreadsheet

General
1

Hello guys, I'm new to node-red. I want to save the data I receive from my MQTT in JSON form in exel, identifying the columns. In my case, the first position is the chip ID, which changes depending on the device I am receiving, the second position is temperature, the third position is humidity .. I want to save this data to make graphs and have a history ...

Thanks!

duvida2
duvida21175×542 30.6 KB

2

If you want to save the data, why not use a database instead of excel?

But if you want to use excel, have you looked for any excel nodes in the flows library?

3

I don't have much time to develop this project, I believe that database is more complex than saving in EXEL. I found an exel node but I don't know how to work with it ..

4

Just remember, people on the forum are volunteers and many - like myself - will be happy to guide you to solving your problem, but you will be doingthe work. Given that which node did you decide to use? (full name please)

Are you planning on retreiving the data in the future in NR?

5

I'm using this node https://flows.nodered.org/node/node-red-contrib-excel but I don't know where I'm going to confirm the columns or if I need to create a file ... I've tried a few ways, but so far I did not succeed.

6

Show me what you have tried?

7

this is my project so far, it doesn't update in exel, it saved just one reading.

[{"id":"d72b056f.0d3248","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"ac6f5e08.195e3","type":"mqtt in","z":"d72b056f.0d3248","name":"TCC","topic":"Teste","qos":"0","datatype":"json","broker":"c7266bc3.062ef8","x":60,"y":116,"wires":[["ec66510c.9f189","f5f9f94d.b40778","260fb5ab.7bdeea","e59c4cd5.85271"]]},{"id":"9f4f2111.81f77","type":"debug","z":"d72b056f.0d3248","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":495.5,"y":131,"wires":},{"id":"ec66510c.9f189","type":"ui_gauge","z":"d72b056f.0d3248","name":"temperatura","group":"d8123882.e133f8","order":1,"width":0,"height":0,"gtype":"gage","title":"temperatura","label":"ºC","format":"{{msg.payload.valores[1]}}","min":"-10","max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":424.5,"y":230,"wires":},{"id":"f5f9f94d.b40778","type":"ui_gauge","z":"d72b056f.0d3248","name":"umidade","group":"d8123882.e133f8","order":1,"width":0,"height":0,"gtype":"gage","title":"umidade","label":"%","format":"{{msg.payload.valores[2]}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":389,"y":292,"wires":},{"id":"260fb5ab.7bdeea","type":"ui_text","z":"d72b056f.0d3248","group":"d8123882.e133f8","order":2,"width":0,"height":0,"name":"","label":"ID","format":"{{msg.payload.valores[0]}}","layout":"row-spread","x":394.5,"y":347,"wires":},{"id":"e59c4cd5.85271","type":"excel","z":"d72b056f.0d3248","name":"testeExcel","file":"C:\Users\Eduardoluiz\Desktop/testeexcel.xlsx","x":291.5,"y":118,"wires":[["9f4f2111.81f77"]]},{"id":"c7266bc3.062ef8","type":"mqtt-broker","z":"","name":"TCC ","broker":"m24.cloudmqtt.com","port":"14910","clientid":"","usetls":false,"compatmode":true,"keepalive":"1","cleansession":true,"birthTopic":"esp/test","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"d8123882.e133f8","type":"ui_group","z":"","name":"Aviário IFSC ","tab":"52698c4b.9a7b34","disp":true,"width":"6","collapse":false},{"id":"52698c4b.9a7b34","type":"ui_tab","z":"","name":"TCC - Movitoramento de Aviários","icon":"TCC - Movitoramento de Aviários","disabled":false,"hidden":false}]

exel

8

The node is doing exactly what is suppose to do, it is writing a file for every payload that comes to it. If you want it to write the data from all three sensors, you will have to use a 'join' node to join the three peices together. (search the forum, this has been asked and answered many times)

You will still have a problem even using the join because each time the excel node runs, it will overwrite the previous data in the fil. eIf you want to write more than one row of data, all the rows have to be in the msg.payload. I'd suggest you contact the author of the node, but seeing as it hasn't been updated in 3 years you might not have much luck.

You would be much better writing the data to a database. Then when you want to look at in, have a button that will start a flow to read the database, format the data correctly and write it out to the excel file.

10

Hi, as an alternative approach, if you are using newer versions of Excel, they contain PowerQuery which is a very powerful "Extract, Transform & Load" (ETL) tool. You can easily read a JSON file or indeed a folder full of them if you like. It has a fairly easy to use point and click interface.

So in that case, you simply write your data to a JSON file and then read that file using PowerQuery. To get the latest set of figures in Excel, you can then just refresh the data, PowerQuery will re-read the file. Or, if it suits your needs better, you could write a new file on each update into a folder and get PowerQuery to simply read the whole folder.

Once you have read the file in PowerQuery, there is a point-and-click method to convert it into a table selecting whatever data you want.

11

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