Export node red stored data to excel

I have a question regarding storing of data.
I want to export the data stored in global context into an excel .I did some research and found a node called csv .But is it possible to get the start, stop and duration values of a flow timer node into the excel using the csv node?

Thanks in advance

Yes, if a. csv file is good enough it is actually quit easy. You can make a function node with the below code and send it to a file out node set to
Append to & create if doesn't exist.

let A = global.get("globalname1");
let B = global.get("globalname2");
let C = global.get("globalname3");

Msg.payload = A + "," + B + "," + C + "/n";

Thank you for your reply .

I tried as per you suggested. but i am getting an error.
"ReferenceError: Msg is not defined (line 5, col 1)".
the line 5 is Msg.payload = A + "," + B + "," + C + "/n";

Can you share (maybe only the relevant) part of your flow?

I think the quick fix is that 'Msg' should be 'msg'. But if you share the flow you are working on I will be able to help better

I changed the Msg to msg ,as a result no error but still how can i export the start,stop and duration from the flow timer to an excel .

Can you export the flow and copy the code here?

flows (2).json (6.6 KB)

please find the attached code

[{"id":"9a693308.7ebaf","type":"subflow","name":"flow-timer","info":"","category":"","in":[{"x":80,"y":100,"wires":[{"id":"7fc82258.93e36c"}]}],"out":[{"x":440,"y":100,"wires":[{"id":"7fc82258.93e36c","port":0}]}],"env":[{"name":"name","type":"str","value":"measure","ui":{"icon":"font-awesome/fa-tag","label":{"en-US":"Timer Name"},"type":"input","opts":{"types":["str","env"]}}},{"name":"operation","type":"str","value":"start","ui":{"icon":"font-awesome/fa-cog","label":{"en-US":"Operation"},"type":"select","opts":{"opts":[{"l":{"en-US":"start"},"v":"start"},{"l":{"en-US":"stop"},"v":"stop"},{"l":{"en-US":"msg.topic"},"v":"msg.topic"},{"l":{"en-US":"msg.operation"},"v":"msg.operation"},{"l":{"en-US":"msg.payload"},"v":"msg.payload"}]}}}],"meta":{"module":"node-red-contrib-flow-performance","type":"flow-performance","version":"1.0.1","author":"Steve-Mcl","desc":"Inline flow performance measure node","keywords":"node-red performance","license":"MIT"},"color":"#DAEAAA","icon":"node-red/timer.svg","status":{"x":280,"y":160,"wires":[{"id":"7fc82258.93e36c","port":1}]}},{"id":"7fc82258.93e36c","type":"function","z":"9a693308.7ebaf","name":"do operation","func":"// @ts-ignore\nvar name = msg.perfName || env.get(\"name\");\n//const NODE_ENV = process.env.NODE_ENV;\n// @ts-ignore\nvar operation = msg.perfOperation || env.get(\"operation\");\nvar measures = global.get(\"flow_timers\") || {};\nvar measure = measures[name] || {};\n\nfunction doOp(measure, operation){\n    if(operation === \"start\"){\n        measure.start = Date.now();//process.hrtime();//Date.now();//change to process.hrtime\n        measure.stop = null;\n        measure.durationMs = null;\n    } else if(operation === \"stop\") {\n        measure.stop = Date.now();//process.hrtime();//Date.now();//change to process.hrtime\n        measure.durationMs = measure.start ? measure.stop - measure.start : null;\n        msg._performance = measure;\n    }\n}\n\n\nif(operation === \"start\"){\n    doOp(measure, operation);\n} else if(operation === \"stop\") {\n    doOp(measure, operation);\n    node.send([null, { payload: { text: name + \": \" + measure.durationMs + \"ms\" }}]);\n} else if(operation === \"msg.topic\") {\n    operation = msg.topic;\n    doOp(measure, operation);\n} else if(operation === \"msg.operation\") {\n    operation = msg.operation;\n    doOp(measure, operation);\n} else if(operation === \"msg.payload\") {\n    operation = msg.payload;\n    doOp(measure, operation);\n} else {\n    return [msg, null];\n}\nmeasures[name] = measure;\nglobal.set(\"flow_timers\", measures);\n\nreturn [msg, null,];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":100,"wires":[[],[]]},{"id":"871aaff24eef975d","type":"tab","label":"Flow 5","disabled":false,"info":"","env":[]},{"id":"4c1096f04e5cccd4","type":"inject","z":"871aaff24eef975d","name":"function","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"function","payload":"function","payloadType":"str","x":70,"y":140,"wires":[["17c9e0fe2cf3069b"]]},{"id":"a91ea458e78cf592","type":"debug","z":"871aaff24eef975d","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":160,"wires":[]},{"id":"6344514b6d9ed5f4","type":"function","z":"871aaff24eef975d","name":"Native JS Sort","func":"msg.payload.sort((a,b) => {\n    if(a.age < b.age){\n        return -1;\n    }else if(a.age > b.age){\n        return 1;\n    }else{\n        return 0;\n    }\n})\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":300,"wires":[["c0ab505d1ab1a350"]]},{"id":"fb8656e3607456d6","type":"comment","z":"871aaff24eef975d","name":"measure time it takes for Function node VS JSONata","info":"","x":410,"y":100,"wires":[]},{"id":"17c9e0fe2cf3069b","type":"function","z":"871aaff24eef975d","name":"Generate a dataset","func":"var data = [];\n\nfunction randomFloat(min, max) { \n    return Math.random() * (max - min) + min; \n}\nfunction randomInt(min, max) {\n  return Math.floor(Math.random() * (max - min + 1) + min);\n}\nfunction randomBool() {\n  return Math.random() >= 0.5;\n}\n\nvar makeRow = function() {\n    return {\n        \"name\": \"Item \" + (data.length+1),\n        \"gender\": randomBool() == true ? \"male\" : \"female\",\n        \"age\": randomInt(18,75),\n        \"height\": randomInt(18,75),\n        \"lat\": randomFloat(51.0,54.9),\n        \"lon\": randomFloat(-1.8, 1.8),\n        \"friendCount\": randomInt(30,300),\n        \"employmentDuration\": randomFloat(1.0,35.9),\n        \"accessLevel\": randomInt(1,9),\n        \"canEdit\": randomBool(),\n        \"canWrite\": randomBool(),\n        \"dob\": `${randomInt(1930,1999)}-${randomInt(1,12)}-${randomInt(1,28)}`\n    }\n}\n\nfor(let i = 0; i < 1000; i++) {\n    data.push(makeRow());\n}\n\nmsg.payload = data;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":286,"y":160,"wires":[["a91ea458e78cf592","d6fbb6e652019dbd"]]},{"id":"d6fbb6e652019dbd","type":"subflow:9a693308.7ebaf","z":"871aaff24eef975d","name":"","env":[{"name":"name","value":"function","type":"str"}],"x":200,"y":300,"wires":[["6344514b6d9ed5f4"]]},{"id":"c0ab505d1ab1a350","type":"subflow:9a693308.7ebaf","z":"871aaff24eef975d","name":"","env":[{"name":"name","value":"function","type":"str"},{"name":"operation","value":"stop","type":"str"}],"x":620,"y":300,"wires":[["9803826b2d026cae","c1274e32273fc54c"]]},{"id":"9803826b2d026cae","type":"debug","z":"871aaff24eef975d","name":"function","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":920,"y":300,"wires":[]},{"id":"c1274e32273fc54c","type":"function","z":"871aaff24eef975d","name":"function 5","func":"let A = global.get(\"flow_timers\");\nlet B = global.get(\"flow_timers\");\nlet C = global.get(\"flow_timers\");\n\nmsg.payload = A + \",\" + B + \",\" + C + \"/n\";\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":440,"wires":[["0f464f36570747e8"]]},{"id":"0f464f36570747e8","type":"csv","z":"871aaff24eef975d","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":570,"y":460,"wires":[["774b28923ba68896"]]},{"id":"02ebd9469249f597","type":"debug","z":"871aaff24eef975d","name":"debug 11","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":980,"y":380,"wires":[]},{"id":"774b28923ba68896","type":"file","z":"871aaff24eef975d","name":"","filename":"Desktop/flow.csv","filenameType":"str","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":750,"y":440,"wires":[["02ebd9469249f597"]]}]

Hi Johnson, I work with your first upload. Thanks for reformatting :slight_smile:

[
    {
        "id": "9a693308.7ebaf",
        "type": "subflow",
        "name": "flow-timer",
        "info": "",
        "category": "",
        "in": [
            {
                "x": 80,
                "y": 100,
                "wires": [
                    {
                        "id": "7fc82258.93e36c"
                    }
                ]
            }
        ],
        "out": [
            {
                "x": 440,
                "y": 100,
                "wires": [
                    {
                        "id": "7fc82258.93e36c",
                        "port": 0
                    }
                ]
            }
        ],
        "env": [
            {
                "name": "name",
                "type": "str",
                "value": "measure",
                "ui": {
                    "icon": "font-awesome/fa-tag",
                    "label": {
                        "en-US": "Timer Name"
                    },
                    "type": "input",
                    "opts": {
                        "types": [
                            "str",
                            "env"
                        ]
                    }
                }
            },
            {
                "name": "operation",
                "type": "str",
                "value": "start",
                "ui": {
                    "icon": "font-awesome/fa-cog",
                    "label": {
                        "en-US": "Operation"
                    },
                    "type": "select",
                    "opts": {
                        "opts": [
                            {
                                "l": {
                                    "en-US": "start"
                                },
                                "v": "start"
                            },
                            {
                                "l": {
                                    "en-US": "stop"
                                },
                                "v": "stop"
                            },
                            {
                                "l": {
                                    "en-US": "msg.topic"
                                },
                                "v": "msg.topic"
                            },
                            {
                                "l": {
                                    "en-US": "msg.operation"
                                },
                                "v": "msg.operation"
                            },
                            {
                                "l": {
                                    "en-US": "msg.payload"
                                },
                                "v": "msg.payload"
                            }
                        ]
                    }
                }
            }
        ],
        "meta": {
            "module": "node-red-contrib-flow-performance",
            "type": "flow-performance",
            "version": "1.0.1",
            "author": "Steve-Mcl",
            "desc": "Inline flow performance measure node",
            "keywords": "node-red performance",
            "license": "MIT"
        },
        "color": "#DAEAAA",
        "icon": "node-red/timer.svg",
        "status": {
            "x": 280,
            "y": 160,
            "wires": [
                {
                    "id": "7fc82258.93e36c",
                    "port": 1
                }
            ]
        }
    },
    {
        "id": "7fc82258.93e36c",
        "type": "function",
        "z": "9a693308.7ebaf",
        "name": "do operation",
        "func": "// @ts-ignore\nvar name = msg.perfName || env.get(\"name\");\n//const NODE_ENV = process.env.NODE_ENV;\n// @ts-ignore\nvar operation = msg.perfOperation || env.get(\"operation\");\nvar measures = global.get(\"flow_timers\") || {};\nvar measure = measures[name] || {};\n\nfunction doOp(measure, operation){\n    if(operation === \"start\"){\n        measure.start = Date.now();//process.hrtime();//Date.now();//change to process.hrtime\n        measure.stop = null;\n        measure.durationMs = null;\n    } else if(operation === \"stop\") {\n        measure.stop = Date.now();//process.hrtime();//Date.now();//change to process.hrtime\n        measure.durationMs = measure.start ? measure.stop - measure.start : null;\n        msg._performance = measure;\n    }\n}\n\n\nif(operation === \"start\"){\n    doOp(measure, operation);\n} else if(operation === \"stop\") {\n    doOp(measure, operation);\n    node.send([null, { payload: { text: name + \": \" + measure.durationMs + \"ms\" }}]);\n} else if(operation === \"msg.topic\") {\n    operation = msg.topic;\n    doOp(measure, operation);\n} else if(operation === \"msg.operation\") {\n    operation = msg.operation;\n    doOp(measure, operation);\n} else if(operation === \"msg.payload\") {\n    operation = msg.payload;\n    doOp(measure, operation);\n} else {\n    return [msg, null];\n}\nmeasures[name] = measure;\nglobal.set(\"flow_timers\", measures);\n\nreturn [msg, null,];",
        "outputs": 2,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 250,
        "y": 100,
        "wires": [
            [],
            []
        ]
    },
    {
        "id": "871aaff24eef975d",
        "type": "tab",
        "label": "Flow 5",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "4c1096f04e5cccd4",
        "type": "inject",
        "z": "871aaff24eef975d",
        "name": "function",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "function",
        "payload": "function",
        "payloadType": "str",
        "x": 110,
        "y": 160,
        "wires": [
            [
                "17c9e0fe2cf3069b"
            ]
        ]
    },
    {
        "id": "a91ea458e78cf592",
        "type": "debug",
        "z": "871aaff24eef975d",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 610,
        "y": 180,
        "wires": []
    },
    {
        "id": "6344514b6d9ed5f4",
        "type": "function",
        "z": "871aaff24eef975d",
        "name": "Native JS Sort",
        "func": "msg.payload.sort((a,b) => {\n    if(a.age < b.age){\n        return -1;\n    }else if(a.age > b.age){\n        return 1;\n    }else{\n        return 0;\n    }\n})\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 420,
        "y": 320,
        "wires": [
            [
                "c0ab505d1ab1a350"
            ]
        ]
    },
    {
        "id": "fb8656e3607456d6",
        "type": "comment",
        "z": "871aaff24eef975d",
        "name": "measure time it takes for Function node VS JSONata",
        "info": "",
        "x": 450,
        "y": 120,
        "wires": []
    },
    {
        "id": "17c9e0fe2cf3069b",
        "type": "function",
        "z": "871aaff24eef975d",
        "name": "Generate a dataset",
        "func": "var data = [];\n\nfunction randomFloat(min, max) { \n    return Math.random() * (max - min) + min; \n}\nfunction randomInt(min, max) {\n  return Math.floor(Math.random() * (max - min + 1) + min);\n}\nfunction randomBool() {\n  return Math.random() >= 0.5;\n}\n\nvar makeRow = function() {\n    return {\n        \"name\": \"Item \" + (data.length+1),\n        \"gender\": randomBool() == true ? \"male\" : \"female\",\n        \"age\": randomInt(18,75),\n        \"height\": randomInt(18,75),\n        \"lat\": randomFloat(51.0,54.9),\n        \"lon\": randomFloat(-1.8, 1.8),\n        \"friendCount\": randomInt(30,300),\n        \"employmentDuration\": randomFloat(1.0,35.9),\n        \"accessLevel\": randomInt(1,9),\n        \"canEdit\": randomBool(),\n        \"canWrite\": randomBool(),\n        \"dob\": `${randomInt(1930,1999)}-${randomInt(1,12)}-${randomInt(1,28)}`\n    }\n}\n\nfor(let i = 0; i < 1000; i++) {\n    data.push(makeRow());\n}\n\nmsg.payload = data;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 326,
        "y": 180,
        "wires": [
            [
                "a91ea458e78cf592",
                "d6fbb6e652019dbd"
            ]
        ]
    },
    {
        "id": "d6fbb6e652019dbd",
        "type": "subflow:9a693308.7ebaf",
        "z": "871aaff24eef975d",
        "name": "",
        "env": [
            {
                "name": "name",
                "value": "function",
                "type": "str"
            }
        ],
        "x": 240,
        "y": 320,
        "wires": [
            [
                "6344514b6d9ed5f4"
            ]
        ]
    },
    {
        "id": "c0ab505d1ab1a350",
        "type": "subflow:9a693308.7ebaf",
        "z": "871aaff24eef975d",
        "name": "",
        "env": [
            {
                "name": "name",
                "value": "function",
                "type": "str"
            },
            {
                "name": "operation",
                "value": "stop",
                "type": "str"
            }
        ],
        "x": 660,
        "y": 320,
        "wires": [
            [
                "9803826b2d026cae",
                "a1e009aebeb54367"
            ]
        ]
    },
    {
        "id": "9803826b2d026cae",
        "type": "debug",
        "z": "871aaff24eef975d",
        "name": "function",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1060,
        "y": 180,
        "wires": []
    },
    {
        "id": "a1e009aebeb54367",
        "type": "function",
        "z": "871aaff24eef975d",
        "name": "Format CSV tekst",
        "func": "let start = msg._performance.start\nlet stop = msg._performance.stop\nlet duration = msg._performance.durationMs\nmsg.payload = start + \",\" + stop + \",\" + duration;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 910,
        "y": 320,
        "wires": [
            [
                "a8ae44cac613fe09",
                "5d432b9a4c7b7ef8"
            ]
        ]
    },
    {
        "id": "a8ae44cac613fe09",
        "type": "debug",
        "z": "871aaff24eef975d",
        "name": "debug 13",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1200,
        "y": 260,
        "wires": []
    },
    {
        "id": "5d432b9a4c7b7ef8",
        "type": "file",
        "z": "871aaff24eef975d",
        "name": "",
        "filename": "C:\\Users\\<USERNAME>\\Desktop\\exceldata.csv",
        "appendNewline": true,
        "createDir": true,
        "overwriteFile": "false",
        "encoding": "none",
        "x": 1240,
        "y": 320,
        "wires": [
            [
                "da56bae22808bfd0"
            ]
        ]
    },
    {
        "id": "da56bae22808bfd0",
        "type": "debug",
        "z": "871aaff24eef975d",
        "name": "debug 12",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1360,
        "y": 360,
        "wires": []
    }
]

This should work, just make sure you change the file location in the file out node:
image

The file location should be an absolute path. If you are running windows, you should only fill your PC username where you see now and it should drop the file on your desktop

Thank you so much for your help.
it is working as I need.
so you have only used a function node to export the data.
A small question I have is ,is it not possible with a csv node?

Yes you can, I found a step-by-step tutorial here:

But for this example I choose to format the CSV string in a Function node. Since I don't have a lot of experience with the CSV node.

Thank you so much for your help.
anyway my requirement is done with the function node which you gave .
but I will give a try with csv also.

:slightly_smiling_face: my pleasure

1 Like

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