Trouble creating a csv file with headers

Hello all,

I've been trying to create a process that creates a csv file out of all the payloads he receives in a flow.

The payload is received from an excel file that is being put inside a watch node. I then split the payload to create a payload for each line in the original excel file.

Then, taking some parts of the payload I am trying to create a new csv out of it. However, I am struggling to correctly add headers.

This is what it should look like:

Screenshot_7

This is how it turns out:

Screenshot_8

I've tried all kinds of different setting variations in the csv node but couldnt get any better result than what I've attached above.

This is the flow (with slight modifications)

[
    {
        "id": "d7590e04b0745cb1",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "a981af01d73ded28",
        "type": "watch",
        "z": "d7590e04b0745cb1",
        "name": "watch for excel file",
        "files": "C:\\Users\\username\\Desktop\\source",
        "recursive": false,
        "x": 170,
        "y": 120,
        "wires": [
            [
                "eccbf571acfc6758"
            ]
        ]
    },
    {
        "id": "eccbf571acfc6758",
        "type": "trigger",
        "z": "d7590e04b0745cb1",
        "name": "",
        "op1": "",
        "op2": "",
        "op1type": "nul",
        "op2type": "payl",
        "duration": "5",
        "extend": true,
        "overrideDelay": false,
        "units": "s",
        "reset": "",
        "bytopic": "topic",
        "topic": "filename",
        "outputs": 1,
        "x": 160,
        "y": 220,
        "wires": [
            [
                "76bef2dc99a838b3"
            ]
        ]
    },
    {
        "id": "76bef2dc99a838b3",
        "type": "file in",
        "z": "d7590e04b0745cb1",
        "name": "",
        "filename": "",
        "format": "stream",
        "chunk": false,
        "sendError": false,
        "encoding": "none",
        "x": 320,
        "y": 220,
        "wires": [
            [
                "830747a5a674faf6",
                "7ee5f99e063f25ec"
            ]
        ]
    },
    {
        "id": "830747a5a674faf6",
        "type": "book",
        "z": "d7590e04b0745cb1",
        "name": "",
        "raw": false,
        "x": 470,
        "y": 220,
        "wires": [
            [
                "ce66ba62b76cd52f"
            ]
        ]
    },
    {
        "id": "ce66ba62b76cd52f",
        "type": "sheet",
        "z": "d7590e04b0745cb1",
        "name": "",
        "sheetName": "Report",
        "x": 590,
        "y": 220,
        "wires": [
            [
                "7a4725f8d252dc21"
            ]
        ]
    },
    {
        "id": "7a4725f8d252dc21",
        "type": "sheet-to-json",
        "z": "d7590e04b0745cb1",
        "name": "",
        "raw": "false",
        "range": "A1:N10000",
        "header": "default",
        "blankrows": false,
        "x": 730,
        "y": 220,
        "wires": [
            [
                "c8424dd03d597762"
            ]
        ]
    },
    {
        "id": "c8424dd03d597762",
        "type": "split",
        "z": "d7590e04b0745cb1",
        "name": "split payload to each row from excel",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "payload",
        "x": 960,
        "y": 220,
        "wires": [
            [
                "a6b39e8087a3e2c6"
            ]
        ]
    },
    {
        "id": "a6b39e8087a3e2c6",
        "type": "change",
        "z": "d7590e04b0745cb1",
        "name": "swap characters",
        "rules": [
            {
                "t": "change",
                "p": "payload.branch_name",
                "pt": "msg",
                "from": "'",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.branch_name",
                "pt": "msg",
                "from": "\"",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.contact",
                "pt": "msg",
                "from": "\"",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.contact",
                "pt": "msg",
                "from": "'",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.address",
                "pt": "msg",
                "from": "\"",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.address",
                "pt": "msg",
                "from": "'",
                "fromt": "str",
                "to": "",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "payload.date",
                "pt": "msg",
                "from": "/",
                "fromt": "str",
                "to": ".",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1220,
        "y": 220,
        "wires": [
            [
                "d9ea7d206e55fcd9"
            ]
        ]
    },
    {
        "id": "d9ea7d206e55fcd9",
        "type": "change",
        "z": "d7590e04b0745cb1",
        "name": "set variables",
        "rules": [
            {
                "t": "set",
                "p": "branch_name",
                "pt": "msg",
                "to": "payload.branch_name",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "branch_num",
                "pt": "msg",
                "to": "payload.branch_num",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "contact",
                "pt": "msg",
                "to": "payload.contact",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "address",
                "pt": "msg",
                "to": "payload.address",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "city",
                "pt": "msg",
                "to": "payload.city",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "phone1",
                "pt": "msg",
                "to": "payload.phone1",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "phone2",
                "pt": "msg",
                "to": "payload.phone2",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "cust_num",
                "pt": "msg",
                "to": "payload.cust_num",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "comments",
                "pt": "msg",
                "to": "payload.comments",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "date",
                "pt": "msg",
                "to": "payload.date",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 190,
        "y": 280,
        "wires": [
            [
                "e4b726f4bced2df7"
            ]
        ]
    },
    {
        "id": "7ee5f99e063f25ec",
        "type": "debug",
        "z": "d7590e04b0745cb1",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 470,
        "y": 120,
        "wires": []
    },
    {
        "id": "b458b6da2bdb3658",
        "type": "function",
        "z": "d7590e04b0745cb1",
        "name": "set filename & define payload",
        "func": "msg.filename =  \"C:\\\\users\\\\username\\\\desktop\\\\csvoutput\" + Math.round(new Date(Date.now()).getTime()/1000) + \".csv\"\nmsg.shekels = \"שקלים\"\nmsg.service = \"004\"\nvar data = msg.payload[\"תאריך איסוף\"] + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + \"\" + \",\" + 0 + \",\" + msg.shekels + \",\" + msg.payload[\"מס סניף\"] + \",\" + msg.payload[\"מס סניף\"] + \",\" + \"\" + \",\" + \"\" + \",\" + msg.payload[\"הערות\"] + \",\" + \"\" + \",\" + msg.service + \",\" + \"\"\nmsg.payload = data;\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 570,
        "y": 280,
        "wires": [
            [
                "1c03f1f9e59c716e"
            ]
        ]
    },
    {
        "id": "eabd7ce64900228b",
        "type": "debug",
        "z": "d7590e04b0745cb1",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1110,
        "y": 280,
        "wires": []
    },
    {
        "id": "e4b726f4bced2df7",
        "type": "delay",
        "z": "d7590e04b0745cb1",
        "name": "",
        "pauseType": "delay",
        "timeout": "3",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "1",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "5",
        "randomUnits": "seconds",
        "drop": false,
        "allowrate": false,
        "outputs": 1,
        "x": 360,
        "y": 280,
        "wires": [
            [
                "b458b6da2bdb3658"
            ]
        ]
    },
    {
        "id": "7b8fece26495b395",
        "type": "file",
        "z": "d7590e04b0745cb1",
        "name": "",
        "filename": "",
        "appendNewline": true,
        "createDir": false,
        "overwriteFile": "false",
        "encoding": "utf8",
        "x": 940,
        "y": 280,
        "wires": [
            [
                "eabd7ce64900228b"
            ]
        ]
    },
    {
        "id": "1c03f1f9e59c716e",
        "type": "csv",
        "z": "d7590e04b0745cb1",
        "name": "",
        "sep": ",",
        "hdrin": false,
        "hdrout": "all",
        "multi": "one",
        "ret": "\\r\\n",
        "temp": "תאריך ביצוע איסוף,מספר ברקוד,סוג תכולה,ערך מובל,סניף בנק יעד,קוד בנק יעד,סניף בנק מקור,קוד בנק מקור,סניף בנק משלם,קוד בנק משלם,צרורות,סוג מטבע,אתר,אתר מוצא,אתר יעד,מכשיר,מס קריאה אצל הלקוח,תאריך הטענה,סוג שירות,קוד תקלה",
        "skip": "0",
        "strings": false,
        "include_empty_strings": true,
        "include_null_values": true,
        "x": 790,
        "y": 280,
        "wires": [
            [
                "7b8fece26495b395"
            ]
        ]
    }
]

Also, I am inserting empty values as you can see in the function node in purpose. How can I get that to output nothing instead of outputting "null"?

Much thanks for any help.

In my opinion this cant work - and I do not like function nodes as well, if they are not absolutely necessary. Create for each line a msg - and for each column in a msg object. I use a join node and this works perfectly. In my opinion you destroy the perfect structure not keeping the columns as properties of the payload. If you wouldn't do that you could directly use the csv node - to convert it back, instead of your "function-node". If you have decimal numbers with commas - then you should think about a different separator - but I dont know.

In addition you should use the option to send headers only once in your csv node - instead each time. I don't think that your intention is, to get above each data row a header.

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