# Store data on a database

**URL:** <https://discourse.nodered.org/t/store-data-on-a-database/7179>\
**Category:** General\
**Created:** [23 January 2019 16:48 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179 "2019-01-23T16:48:44Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 16:48 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/1 "2019-01-23T16:48:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [23 January 2019 17:04 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/2 "2019-01-23T17:04:38Z")

</div>

If your desired output is a file, you may be able to get by with [context variables.](https://nodered.org/docs/user-guide/context) 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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [23 January 2019 17:05 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/3 "2019-01-23T17:05:45Z")

</div>

> [@britzz](#):
>
> Thus, I would like to know which database do you recommend me to implement?

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

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 17:11 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/4 "2019-01-23T17:11:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [23 January 2019 17:14 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/5 "2019-01-23T17:14:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 19:27 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/6 "2019-01-23T19:27:56Z")

</div>

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. 🙂  
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/978dac08b0edb3ca6ada45e2bc79fdc94392ab74.jpeg)  
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/264de19e8ee91f16bfe82c41d850d6e4e1062a8d.jpeg)

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 19:29 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/7 "2019-01-23T19:29:19Z")

</div>

> [  
> {  
> "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](http://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  
> }  
> ]

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [23 January 2019 19:32 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/8 "2019-01-23T19:32:10Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 19:56 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/9 "2019-01-23T19:56:18Z")

</div>

They send it every time something is produced.  
I also tried to append it, but I get something like this: ![table_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/d/d7774d80aef306c749e382053f38c127ab892b10.jpeg)

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/a7288e79cf97c981b394c5a998c73d8493d2314f.jpeg)

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

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [23 January 2019 20:52 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/10 "2019-01-23T20:52:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [23 January 2019 21:11 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/11 "2019-01-23T21:11:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [23 January 2019 21:19 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/12 "2019-01-23T21:19:19Z")

</div>

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":""}]`

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [24 January 2019 18:36 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/13 "2019-01-24T18:36:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [24 January 2019 18:37 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/14 "2019-01-24T18:37:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![britzz](https://avatars.discourse-cdn.com/v4/letter/b/67e7ee/32.png) [@britzz](https://discourse.nodered.org/u/britzz)\
**Post date:** [25 January 2019 14:43 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/15 "2019-01-25T14:43:46Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/0027a4d1880ebb16739699fabaa9989d78f00884.jpeg) ![b](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e0e5f82a72b15c369bdea1308d9cb0003d7595bf.jpeg)

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [25 January 2019 15:15 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/16 "2019-01-25T15:15:03Z")

</div>

![00%20AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/f/f4a2902ba3f9e8739195c3d08c70ed3eb913fdb0.png)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [25 January 2019 20:34 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/17 "2019-01-25T20:34:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![eduardoluiztodero](https://avatars.discourse-cdn.com/v4/letter/e/dc4da7/32.png) [@eduardoluiztodero](https://discourse.nodered.org/u/eduardoluiztodero)\
**Post date:** [13 March 2020 20:04 UTC](https://discourse.nodered.org/t/store-data-on-a-database/7179/18 "2020-03-13T20:04:29Z")

</div>

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