# SQLite data to Excel file

**URL:** <https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443>\
**Category:** General\
**Created:** [14 March 2024 21:55 UTC](https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443 "2024-03-14T21:55:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jttjtt](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jttjtt](https://discourse.nodered.org/u/jttjtt)\
**Post date:** [14 March 2024 21:55 UTC](https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443/1 "2024-03-14T21:55:28Z")

</div>

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:

```auto
[
    {
        "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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd0954956d70424ebb8ba04e32b2619958bb7ca6.png)

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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [14 March 2024 22:53 UTC](https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443/2 "2024-03-14T22:53:26Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![jttjtt](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jttjtt](https://discourse.nodered.org/u/jttjtt)\
**Post date:** [15 March 2024 07:04 UTC](https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443/3 "2024-03-15T07:04:19Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [13 June 2024 07:05 UTC](https://discourse.nodered.org/t/sqlite-data-to-excel-file/86443/4 "2024-06-13T07:05:00Z")

</div>

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