Store data on a database

General
1

Hi all,

I am working on a project with several nodes (Raspberry pi Zero) that are installed on a factory and collect data from several machines.
At the moment, each Raspberry pi sends a string via MQTT.

On Node-Red I get the string and divide it in order to display it on my dashboard.
My string is like: MachineNr, OperationNr, PiecesProduced, Pieces/hour, And with a csv node I then have:
msg.payload.col1 = MachineNr
msg.payload.col2 = OperationNr
msg.payload.col3 = PiecesProduced
msg.payload.col4 = Pieces/hour
Now, my goal is to gather all this information in database table so I can save it to a file by the end of the day.

Thus, I would like to know which database do you recommend me to implement?
Is there some similar example that I can follow?
Is it possible to save it to a spreadsheet?

Thanks in advance!!

2

If your desired output is a file, you may be able to get by with context variables. If you're worried about losing data in the event of an unplanned restart, setting up persistent context storage would be a good additional step.

3

That depends on what you need to do with the information after it is saved in the database.

4

My goal is that by the end of the day I can extract this information to a spreadsheet.

5

In that case you can write it to a CSV file (there is a node that will generate the CSV data for you). That file can then be read into Excel or whatever your favourite spreadsheet is. No need for a database.

6

First of all thank you all very much for the attention given to this matter!!

@Colin I already tried it but I only get one line on my csv file.
I will leave you below what I have so far and what I would like to have. Maybe it helps you helping me. :slight_smile:
At the moment I display the information on dashboard text nodes, which is very nice to have an overview but not good to store for further analysis.

Here is a overview of the architecture of my system (I'm sorry for having used Paint for it)

system
system.jpg855Ă—507 40 KB

And the kind of table that I intend to save.
Please note that on each interaction. For example, a change on pieces produced must update the table.
table

7

[
{
"id": "9ab68ffd.5b0d18",
"type": "csv",
"z": "31745f4c.f2f8e8",
"name": "CSV",
"sep": ";",
"hdrin": "",
"hdrout": "",
"multi": "one",
"ret": "\n",
"temp": "",
"skip": "0",
"x": 450,
"y": 460,
"wires": [
[
"88ef10c.5e0c27"
]
]
},
{
"id": "88ef10c.5e0c27",
"type": "switch",
"z": "31745f4c.f2f8e8",
"name": "",
"property": "payload.col1",
"propertyType": "msg",
"rules": [
{
"t": "eq",
"v": "1",
"vt": "str"
},
{
"t": "eq",
"v": "2",
"vt": "str"
},
{
"t": "eq",
"v": "3",
"vt": "str"
},
{
"t": "eq",
"v": "4",
"vt": "str"
},
{
"t": "eq",
"v": "5",
"vt": "str"
},
{
"t": "eq",
"v": "6",
"vt": "str"
},
{
"t": "eq",
"v": "7",
"vt": "str"
},
{
"t": "eq",
"v": "8",
"vt": "str"
},
{
"t": "eq",
"v": "9",
"vt": "str"
},
{
"t": "eq",
"v": "10",
"vt": "str"
},
{
"t": "eq",
"v": "11",
"vt": "str"
},
{
"t": "eq",
"v": "12",
"vt": "str"
}
],
"checkall": "true",
"repair": false,
"outputs": 12,
"x": 650,
"y": 460,
"wires": [
[
"48a9ed71.fe077c"
],
[
"e7dc257.a18ddd8"
],
[
"1903b8f9.6e1df7"
],
[
"c9d57bdd.06039"
],
[
"38468e8.9dc00f2"
],
[
"c5a0b974.ab5c"
],
[
"f8552c6c.b5a4d"
],
,
,
,
,

]
},
{
"id": "6513e3cf.3c9dc4",
"type": "mqtt in",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "Test/abc",
"qos": "0",
"broker": "59cb4e43.e42fb",
"x": 80,
"y": 460,
"wires": [
[
"ff239393.feaba",
"5a74e1d4.ef8de8"
]
]
},
{
"id": "e2d6712a.7c7d7",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "6f006bc7.0bc96c",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 200,
"wires":
},
{
"id": "d721e1d5.42b1",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "1;2;3;56",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 580,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "3e93c68d.60f32a",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "2;3;4;544",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 620,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "143cc434.982744",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "6f006bc7.0bc96c",
"order": 3,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 200,
"wires":
},
{
"id": "10b8ea85.17f4fd",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "e1a77136.83616",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 240,
"wires":
},
{
"id": "226fc249.af755e",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "e1a77136.83616",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 240,
"wires":
},
{
"id": "4d507d7f.b1f424",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "e1a77136.83616",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 240,
"wires":
},
{
"id": "f40fbb83.17234",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "6f006bc7.0bc96c",
"order": 1,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 200,
"wires":
},
{
"id": "ff239393.feaba",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 310,
"y": 460,
"wires": [
[
"9ab68ffd.5b0d18"
]
]
},
{
"id": "48a9ed71.fe077c",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "1",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 200,
"wires": [
[
"f40fbb83.17234",
"143cc434.982744",
"e2d6712a.7c7d7"
]
]
},
{
"id": "135683e5.dd377c",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "1;1;2;300",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 660,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "32e7c7a.bebaeb8",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "2;4;5;400",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 700,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "e7dc257.a18ddd8",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "2",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 240,
"wires": [
[
"10b8ea85.17f4fd",
"4d507d7f.b1f424",
"226fc249.af755e"
]
]
},
{
"id": "c0d19a39.005c",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "8c992532.5fe8e8",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 280,
"wires":
},
{
"id": "25f2139d.645d34",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "8c992532.5fe8e8",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 280,
"wires":
},
{
"id": "89aa1817.28cac",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "8c992532.5fe8e8",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 280,
"wires":
},
{
"id": "1903b8f9.6e1df7",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "3",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 280,
"wires": [
[
"c0d19a39.005c",
"89aa1817.28cac",
"25f2139d.645d34"
]
]
},
{
"id": "b25d2989.c4b5",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "c74ce953.f87f68",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 320,
"wires":
},
{
"id": "1f55e81a.e1cd2",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "c74ce953.f87f68",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 320,
"wires":
},
{
"id": "237b1a58.ffa926",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "c74ce953.f87f68",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 320,
"wires":
},
{
"id": "c9d57bdd.06039",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "4",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 320,
"wires": [
[
"b25d2989.c4b5",
"237b1a58.ffa926",
"1f55e81a.e1cd2"
]
]
},
{
"id": "931e9c24.93c6f",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "232853c0.00c37c",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 360,
"wires":
},
{
"id": "c59de990.85334",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "232853c0.00c37c",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 360,
"wires":
},
{
"id": "213dba61.63d2e6",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "232853c0.00c37c",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 360,
"wires":
},
{
"id": "38468e8.9dc00f2",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "5",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 360,
"wires": [
[
"931e9c24.93c6f",
"213dba61.63d2e6",
"c59de990.85334"
]
]
},
{
"id": "90f589b1.ecca08",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "5276c147.62adf8",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 400,
"wires":
},
{
"id": "f767417f.a45a68",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "5276c147.62adf8",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 400,
"wires":
},
{
"id": "7f888d02.f0dd0c",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "5276c147.62adf8",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 400,
"wires":
},
{
"id": "c5a0b974.ab5c",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "6",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 400,
"wires": [
[
"90f589b1.ecca08",
"7f888d02.f0dd0c",
"f767417f.a45a68"
]
]
},
{
"id": "91793012.7ff358",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "4b464f59.f8598",
"order": 1,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col1}}",
"layout": "col-center",
"x": 1070,
"y": 440,
"wires":
},
{
"id": "68db69a.1c3af98",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "4b464f59.f8598",
"order": 2,
"width": "2",
"height": "1",
"name": "",
"label": "",
"format": "{{msg.payload.col3}}",
"layout": "col-center",
"x": 1330,
"y": 440,
"wires":
},
{
"id": "66340a9a.6b33dc",
"type": "ui_text",
"z": "31745f4c.f2f8e8",
"group": "4b464f59.f8598",
"order": 3,
"width": "0",
"height": "0",
"name": "",
"label": "",
"format": "{{msg.payload.col2}}",
"layout": "col-center",
"x": 1190,
"y": 440,
"wires":
},
{
"id": "f8552c6c.b5a4d",
"type": "function",
"z": "31745f4c.f2f8e8",
"name": "7",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 870,
"y": 440,
"wires": [
[
"91793012.7ff358",
"66340a9a.6b33dc",
"68db69a.1c3af98"
]
]
},
{
"id": "8ecea5de.328918",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "3;5;4;621",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 740,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "ae3012c1.7435f8",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "4;7;7;489",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 780,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "10ebe680.4714c2",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "5;6;6;574",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 820,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "bf309626.93c388",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "6;8;6;566",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 860,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "ecf8fe52.c906",
"type": "inject",
"z": "31745f4c.f2f8e8",
"name": "",
"topic": "",
"payload": "8;9;7;581",
"payloadType": "str",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 60,
"y": 900,
"wires": [
[
"ff239393.feaba"
]
]
},
{
"id": "5a74e1d4.ef8de8",
"type": "debug",
"z": "31745f4c.f2f8e8",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 450,
"y": 360,
"wires":
},
{
"id": "59cb4e43.e42fb",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "iot.eclipse.org",
"port": "1883",
"clientid": "",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
},
{
"id": "6f006bc7.0bc96c",
"type": "ui_group",
"z": "",
"name": "Posto1",
"tab": "84499b6b.8ac208",
"order": 2,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "e1a77136.83616",
"type": "ui_group",
"z": "",
"name": "Posto2",
"tab": "84499b6b.8ac208",
"order": 3,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "8c992532.5fe8e8",
"type": "ui_group",
"z": "",
"name": "Posto3",
"tab": "84499b6b.8ac208",
"order": 4,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "c74ce953.f87f68",
"type": "ui_group",
"z": "",
"name": "Posto4",
"tab": "84499b6b.8ac208",
"order": 5,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "232853c0.00c37c",
"type": "ui_group",
"z": "",
"name": "Posto5",
"tab": "84499b6b.8ac208",
"order": 6,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "5276c147.62adf8",
"type": "ui_group",
"z": "",
"name": "Posto6",
"tab": "84499b6b.8ac208",
"order": 7,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "4b464f59.f8598",
"type": "ui_group",
"z": "",
"name": "Posto7",
"tab": "84499b6b.8ac208",
"order": 8,
"disp": false,
"width": "2",
"collapse": false
},
{
"id": "84499b6b.8ac208",
"type": "ui_tab",
"z": "",
"name": "Home",
"icon": "assessment",
"order": 2
}
]

8

You can have that CSV node write to a file. Set it to append with each message.

Also, do your machines send messages to that MQTT broker at regular intervals, or every time they produce something?

9

They send it every time something is produced.
I also tried to append it, but I get something like this:table_1

Where each new message gets added to my table, but what I really wanted was only to update the values for each machine. Something like this:

table_2

Thus, my question about the databases. But, of course, if I am missing something and if it's possible to do with csv and then to file I would be very happy. Thanks in advance, @JayDickson

10

So the machine sending the data keeps its own count of cycles, pieces produced, and pieces per hour? If that's the case, you may benefit from storing those values in a context variable, and just dumping a report built from those at the end of every day. I'll draw something up.

11

Exactly. Each machine has its status stored, updated and sent.
Thank you very much @JayDickson! I appreciate your support!

12

Here's a quick and dirty start based on what you already posted. Note the template node isn't set up for all of your machines because I'm lazy. I'm sure with some work, you could probably work this into something with less hard-coding.

[{"id":"92a2bf7d.9f0908","type":"csv","z":"ea82fe03.503ca8","name":"CSV","sep":";","hdrin":"","hdrout":"","multi":"one","ret":"\n","temp":"Device,OperationNr,piecesProduced,piecesHour","skip":"0","x":950,"y":340,"wires":[["1a888dad.b38cc2"]]},{"id":"7d3c5f92.2913d","type":"mqtt in","z":"ea82fe03.503ca8","name":"","topic":"Test/abc","qos":"0","broker":"5ab52b9b.f77a34","x":780,"y":280,"wires":[["92a2bf7d.9f0908"]]},{"id":"1d7ee223.f6e21e","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"1;2;3;56","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":340,"wires":[["92a2bf7d.9f0908"]]},{"id":"16e91d6e.1f880b","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"2;3;4;544","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":380,"wires":[["92a2bf7d.9f0908"]]},{"id":"a49e37f0.b6c808","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"1;1;2;300","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":420,"wires":[["92a2bf7d.9f0908"]]},{"id":"df2ba261.39d9c","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"2;4;5;400","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":460,"wires":[["92a2bf7d.9f0908"]]},{"id":"bedb0652.e97f58","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"3;5;4;621","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":500,"wires":[["92a2bf7d.9f0908"]]},{"id":"5369ca8e.0e98f4","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"4;7;7;489","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":540,"wires":[["92a2bf7d.9f0908"]]},{"id":"ce431780.1b04d8","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"5;6;6;574","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":580,"wires":[["92a2bf7d.9f0908"]]},{"id":"a5715e73.9f7ac8","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"6;8;6;566","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":620,"wires":[["92a2bf7d.9f0908"]]},{"id":"19c84db4.4ae55a","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"8;9;7;581","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":780,"y":660,"wires":[["92a2bf7d.9f0908"]]},{"id":"3da746be.2cb522","type":"debug","z":"ea82fe03.503ca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1250,"y":340,"wires":[]},{"id":"1a888dad.b38cc2","type":"function","z":"ea82fe03.503ca8","name":"To Flow Variable","func":"node.send(msg);\nflow.set(String(\"Machine\" + msg.payload.Device),msg.payload)\n","outputs":1,"noerr":0,"x":1100,"y":340,"wires":[["3da746be.2cb522"]]},{"id":"64ac014.3ef5f","type":"inject","z":"ea82fe03.503ca8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 12 * * *","once":false,"onceDelay":0.1,"x":770,"y":700,"wires":[["9fb67b49.b1c168"]]},{"id":"9fb67b49.b1c168","type":"template","z":"ea82fe03.503ca8","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Machine,Operations,Total Pieces,Pieces per hour\nMachine1,{{flow.Machine1.OperationNr}},{{flow.Machine1.piecesProduced}},{{flow.Machine1.piecesHour}}\nMachine2,{{flow.Machine2.OperationNr}},{{flow.Machine2.piecesProduced}},{{flow.Machine2.piecesHour}}\nMachine3,{{flow.Machine3.OperationNr}},{{flow.Machine3.piecesProduced}},{{flow.Machine3.piecesHour}}","output":"str","x":920,"y":700,"wires":[["5df125a.120725c","3505a019.6c22b"]]},{"id":"5df125a.120725c","type":"debug","z":"ea82fe03.503ca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1070,"y":760,"wires":[]},{"id":"3505a019.6c22b","type":"file","z":"ea82fe03.503ca8","name":"","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"false","x":1050,"y":700,"wires":[[]]},{"id":"5ab52b9b.f77a34","type":"mqtt-broker","z":"","name":"","broker":"iot.eclipse.org","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

1 Like
13

I just took a look on your suggestion and I think it will work like I wanted. Thank you very much!!

14

If you make further refinements I'd love to see the end result.

15

Sure will! @JayDickson
I am now making the first tests and just realized that now I am also saving col1;col2;col3;col4 As you can see in the images below. Is there something missing? I took a look on every node and try to find the problem, but no luck... Any idea what could be?
Thanks again!!

a b

16

00%20AM
Screen Shot 2019-01-25 at 10.16.00 AM.png1456Ă—1232 78.4 KB

1 Like
17

Depending on what version of Excel you are using, you could take the file that a persistent variable creates and easily reformat.

I've not tried this but it should work if you have a reasonably new version of Excel. Use JSON in your variable to accumulate the data. Make sure the variable is persisted to the filing system.

Then use Excel's PowerQuery capability to read the file and reformat the JSON to a table - there is a function for that.

1 Like
18

Hello I am trying to do the same idea. How do I create the file where I can open it in excel?