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:
This is how it turns out:
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.
