Import excel file and read data from it

General
1

Hello everyone,

Is it possible to import an excel file (xlsx, xlsm) into node-red and extract data from it without precising the path of said file ?

Almost like a "drag and drop your file here" on the dashboard.

Thanks in advance,

2

There are nodes you can use, you could also use the readExcelFile npm library directly in a function node if you prefer.

3

Thank you for your answer ! But I'm pretty new to node red and I don't know how to use an npm library in a function node, do you perhaps have an example ? (Sorry !)

Also, can it be done with no access to the internet ?

Thanks in advance !

4

see this article for using npm modules in a function node

1 Like
5

Start by looking through the "flows" section of this site and search for excel to see if you can find a suitable node. The use of an npm library is a bit more of an advanced topic and probably not so useful if you don't know any JavaScript.

6

I could be wrong but I haven't seen any nodes that support drag and drop on Dashboard.

7

The spreadsheet-in node can read excel files, works quite well. This could be used with a file upload in dashboard, or any other way you read a file within node-red

8

There is an example flow using dropzone js here: https://flows.nodered.org/flow/c70d0b4c54b583cf30c7e989b74feb68

9

Thank you for your reply ; I did try to use the spreadsheet-in nodes, however I can't get them to work...

The first time I deploy the flow, nothing happens and the debug nodes return nothing, and if I try to change the settings of the nodes (for instance, the output and encoding of the "read file" node, or the range of cells in the "sheet to json" node) : whenever I try to deploy, it says "lost connection to the server" and basically crashes...

Did I set up the nodes wrong ?

Sorry and thank you for your help !

Node-RED version : v3.0.2
Node.js version : v18.15.0

[
    {
        "id": "8c0b80d407f06452",
        "type": "book",
        "z": "2d72e3a85743de48",
        "name": "test.xlsm",
        "raw": false,
        "x": 720,
        "y": 220,
        "wires": [
            [
                "a95ed85a5a8d87ae"
            ]
        ]
    },
    {
        "id": "a95ed85a5a8d87ae",
        "type": "sheet",
        "z": "2d72e3a85743de48",
        "name": "",
        "sheetName": "feuille1",
        "x": 890,
        "y": 220,
        "wires": [
            [
                "da6e244de92f56d6",
                "b54fb66e1296fa18"
            ]
        ]
    },
    {
        "id": "da6e244de92f56d6",
        "type": "cell",
        "z": "2d72e3a85743de48",
        "name": "",
        "address": "A1",
        "dataType": "w",
        "x": 1030,
        "y": 220,
        "wires": [
            [
                "ce1511dba0046374"
            ]
        ]
    },
    {
        "id": "8083a45afd8af295",
        "type": "file in",
        "z": "2d72e3a85743de48",
        "name": "",
        "filename": "C:\\Downloads\\test.xlsm",
        "filenameType": "str",
        "format": "utf8",
        "chunk": false,
        "sendError": false,
        "encoding": "none",
        "allProps": false,
        "x": 390,
        "y": 220,
        "wires": [
            [
                "8c0b80d407f06452"
            ]
        ]
    },
    {
        "id": "8b9eb5ad0c063de7",
        "type": "inject",
        "z": "2d72e3a85743de48",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 200,
        "y": 220,
        "wires": [
            [
                "8083a45afd8af295"
            ]
        ]
    },
    {
        "id": "ce1511dba0046374",
        "type": "debug",
        "z": "2d72e3a85743de48",
        "name": "debug 15",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1160,
        "y": 220,
        "wires": []
    },
    {
        "id": "b54fb66e1296fa18",
        "type": "sheet-to-json",
        "z": "2d72e3a85743de48",
        "name": "",
        "raw": "false",
        "range": "A1:B2",
        "header": "default",
        "blankrows": false,
        "x": 1030,
        "y": 280,
        "wires": [
            [
                "2e73542474bd8c3c"
            ]
        ]
    },
    {
        "id": "2e73542474bd8c3c",
        "type": "debug",
        "z": "2d72e3a85743de48",
        "name": "debug 16",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1180,
        "y": 280,
        "wires": []
    }
]
10

Set the read file node to a single Buffer Object and try again.

11

Interesting, I might have a look at that some time. :+1:

12

That worked! Thanks :slight_smile: Now to see about that drag and drop

13

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