SQLite data to Excel file

General
1

Hello, i am struggling with exporting SQlite data to Excel file. I have found sample flow which can make a simple Excel file with two records. It looks like this:

[
    {
        "id": "8835f3f4f0a07f4c",
        "type": "inject",
        "z": "74afde7d.c4af",
        "name": "",
        "props": [
            {
                "p": "home",
                "v": "HOME",
                "vt": "env"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payloadType": "str",
        "x": 190,
        "y": 540,
        "wires": [
            [
                "b02d4d07dc1f6208"
            ]
        ]
    },
    {
        "id": "b02d4d07dc1f6208",
        "type": "function",
        "z": "74afde7d.c4af",
        "name": "example data",
        "func": "msg.payload = [{\n        header: {\n            'author': 'authorName',\n            'title': 'title'\n        },\n        items: [\n         {\n            author:'john',\n            title:'how to use this'\n         },\n         {\n            author:'Bob',\n            title:'so Easy'\n         }\n        ],\n        sheetName: 'sheet1',\n    }];\nmsg.filepath = 'c:\\\\Zalohy\\\\output.xlsx';\nmsg.topic=1\n\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 390,
        "y": 540,
        "wires": [
            [
                "6268ad9d730a90f9"
            ]
        ]
    },
    {
        "id": "6268ad9d730a90f9",
        "type": "excelsheets",
        "z": "74afde7d.c4af",
        "name": "",
        "file": "",
        "x": 610,
        "y": 540,
        "wires": [
            []
        ]
    }
]

But when I make a query on SQLite db, i get output like this:

image

(there is only date and timestamp field exported - just as a sample data).

And I am struggling how to format this output from SQLite db to format for Excelsheets node, which will export me this data with header Date and Timestamp and all the values from DB).

The node Excelsheets used is node-red-contrib-excelsheets.

Thanks for help!

2

Hello .. sorry its not very clear to me .. at the moment you only have date and timestamp in your sqlite db ?

Here is a test flow based on your db sample data, which demonstrates how to convert it.
ready to be sent to the excelsheet node

[{"id":"8835f3f4f0a07f4c","type":"inject","z":"54efb553244c241f","name":"Sqlite Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"date\":\"2024-03-14\",\"timestamp\":\"2024-03-14 20:24:11\"},{\"date\":\"2024-03-14\",\"timestamp\":\"2024-03-14 20:24:11\"},{\"date\":\"2024-03-14\",\"timestamp\":\"2024-03-14 20:24:11\"}]","payloadType":"json","x":320,"y":2640,"wires":[["b02d4d07dc1f6208"]]},{"id":"b02d4d07dc1f6208","type":"function","z":"54efb553244c241f","name":"process data for Excelsheet","func":"msg.payload = [{\n        header: {\n            date: 'Date',\n            timestamp: 'Timestamp'\n        },\n        items: msg.payload,\n        sheetName: 'sheet1',\n}];\n    \nmsg.filepath = 'c:\\\\Zalohy\\\\output.xlsx';\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":2640,"wires":[["345c542a5ca7b54c"]]},{"id":"345c542a5ca7b54c","type":"debug","z":"54efb553244c241f","name":"to Excelsheet node","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":810,"y":2640,"wires":[]}]
3

Hello @UnborN ,
thanks a lot for your help. To make it more clear - no, I have also some another data in database, the date and time field are only one part of them. I knew, that this should be done very easily, but I did not know why. I now see it in your function node, how you made it. It works well.
Now I can add more columns and add data into excel. Thanks a lot for help!

1 Like