Missing values after passing through the csv node

General
1

Hi everyone,

I'm trying to pass some objects (similar to the example below) at the CSV format but it's not going as planned.

{"Numero":"BCUSA24PR04152024","Date":"15/04/2024","Code Journal":"GS","Exercice":24,"Compte":6515097,"Libelle":"Payroll Expenses:Gross Wages:Paul, Jeremy","Debit":6041.67,"Credit":"","Devise":"","Piece":"","Affaire":"B000030"}

There is the result of my CSV node :

Capture d’écran 2024-05-15 à 15.10.10
Capture d’écran 2024-05-15 à 15.10.10832×691 76.2 KB

I really don't understand why the most of the values are empty.

Capture d’écran 2024-05-15 à 15.12.10
Maybe I need a join node between the change nodes and the csv node ? I don't know.

If someone can help me, it would be a pleasure.

2

As we cannot verify settings from a screenshot nor can we see the structure or content of data going into your csv node, we will not be able to provide a quick and easy answer.

In order to help us help you please do the following: Make a basic demo flow, complete with sample data as the input (you can put the sample data into an inject or via a template node) & export it to a comment.

Remember to sanitise data before export.

one thing i can see - you have 2 converter nodes feeding into 1 CSV node. If you are expecting them to be merged into a single row, that will NOT happen (messages NEVER arrive from different wires at the same time and do not auto-magically merge themself into something)

3 
[{"id":"5400a130.bbd76","type":"tab","label":"Pay slips","disabled":false,"info":"# Pay slips\n\n---\n\n## Context\n\nThis flow aims to **convert U.S. pay slips** into the format accepted by Akuiteo, the Braincube ERP.\n\nThe goal of this program is to **lighten the workload for U.S. accountants** that actually convert this files manually before import them into Akuiteo.\n\n---\n\n*Pierre Demay*"},{"id":"30c577c1.ce10e8","type":"inject","z":"5400a130.bbd76","name":"Initialization","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","payload":"[{\"Account Number\":6515098,\"Account Name\":\"Payroll Expenses: Gross Wages: Panico, M\",\"Employee\":\"Panico, Miranda S\",\"Debit\":3464.75,\"Date\":\"4/15/24\"},{\"Account Number\":6515102,\"Account Name\":\"Payroll Expenses: Gross Wages: Scheindel, Monika\",\"Employee\":\"Scheindel, Monika M\",\"Debit\":2291.67,\"Date\":\"4/15/24\"}]","payloadType":"json","x":490,"y":300,"wires":[["a2596d30.bc2a3"]]},{"id":"bb323284.efa04","type":"change","z":"5400a130.bbd76","name":"Converter","rules":[{"t":"set","p":"payslip.Numero","pt":"msg","to":"\"BCUSA\"\t& $moment($$.payload.Date, \"MM/DD/YY\").format(\"YY\")\t& \"PR\"\t& $replace($moment($$.payload.Date, \"MM/DD/YY\").format(\"MMDDYYYY\"), \"/\", \"\")","tot":"jsonata"},{"t":"set","p":"payslip.Date","pt":"msg","to":"$moment($$.payload.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\")","tot":"jsonata"},{"t":"set","p":"payslip[\"Code Journal\"]","pt":"msg","to":"GS","tot":"str"},{"t":"set","p":"payslip.Exercice","pt":"msg","to":"24","tot":"num"},{"t":"set","p":"payslip.Compte","pt":"msg","to":"payload[\"Account Number\"]","tot":"msg"},{"t":"set","p":"payslip.Libelle","pt":"msg","to":"payload[\"Account Name\"]","tot":"msg"},{"t":"set","p":"payslip.Debit","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Credit","pt":"msg","to":"payload.Credit","tot":"msg"},{"t":"set","p":"payslip.Devise","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Piece","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Affaire","pt":"msg","to":"B000030","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":260,"wires":[["c4da4c63.3e4cf","d0bdeb00.eeb068"]]},{"id":"85323343.d245d","type":"change","z":"5400a130.bbd76","name":"Converter","rules":[{"t":"set","p":"payslip.Numero","pt":"msg","to":"\"BCUSA\"\t& $moment($$.payload.Date, \"MM/DD/YY\").format(\"YY\")\t& \"PR\"\t& $replace($moment($$.payload.Date, \"MM/DD/YY\").format(\"MMDDYYYY\"), \"/\", \"\")","tot":"jsonata"},{"t":"set","p":"payslip.Date","pt":"msg","to":"$moment($$.payload.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\")","tot":"jsonata"},{"t":"set","p":"payslip[\"Code Journal\"]","pt":"msg","to":"GS","tot":"str"},{"t":"set","p":"payslip.Exercice","pt":"msg","to":"24","tot":"num"},{"t":"set","p":"payslip.Compte","pt":"msg","to":"payload[\"Account Number\"]","tot":"msg"},{"t":"set","p":"payslip.Libelle","pt":"msg","to":"payload[\"Account Name\"]","tot":"msg"},{"t":"set","p":"payslip.Debit","pt":"msg","to":"payload.Debit","tot":"msg"},{"t":"set","p":"payslip.Credit","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Devise","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Piece","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Affaire","pt":"msg","to":"B000030","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1080,"y":340,"wires":[["c4da4c63.3e4cf","d0bdeb00.eeb068"]]},{"id":"332cb41b.26293c","type":"switch","z":"5400a130.bbd76","name":"Check Debit","property":"payload.Debit","propertyType":"msg","rules":[{"t":"null"},{"t":"nnull"}],"checkall":"false","repair":false,"outputs":2,"x":890,"y":300,"wires":[["bb323284.efa04"],["85323343.d245d"]]},{"id":"61639d41.baa294","type":"file","z":"5400a130.bbd76","name":"Converted payslips","filename":"convert_payslips.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1450,"y":300,"wires":[[]]},{"id":"9438380f.a113d8","type":"debug","z":"5400a130.bbd76","name":"In CSV format","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1440,"y":240,"wires":[]},{"id":"c4da4c63.3e4cf","type":"csv","z":"5400a130.bbd76","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":false,"include_null_values":false,"x":1250,"y":300,"wires":[["9438380f.a113d8"]]},{"id":"d0bdeb00.eeb068","type":"debug","z":"5400a130.bbd76","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payslip","targetType":"msg","statusVal":"","statusType":"auto","x":1270,"y":360,"wires":[]},{"id":"a2596d30.bc2a3","type":"split","z":"5400a130.bbd76","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":300,"wires":[["26c10aea.f47826","332cb41b.26293c"]]},{"id":"26c10aea.f47826","type":"debug","z":"5400a130.bbd76","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":860,"y":200,"wires":[]}]
4

Your converter nodes create an object msg.payslip, but the csv node uses msg.payload. You also set the column input in the csv node, and as msg.payload does not have these properties, empty properties are created.

Make your converter use msg.payload , or after move msg.payslip to msg.payload.

Or you can convert the whole array of objects with JSONata
e.g.

[{"id":"30c577c1.ce10e8","type":"inject","z":"d1395164b4eec73e","name":"Initialization","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","payload":"[{\"Account Number\":6515098,\"Account Name\":\"Payroll Expenses: Gross Wages: Panico, M\",\"Employee\":\"Panico, Miranda S\",\"Debit\":3464.75,\"Date\":\"4/15/24\"},{\"Account Number\":6515102,\"Account Name\":\"Payroll Expenses: Gross Wages: Scheindel, Monika\",\"Employee\":\"Scheindel, Monika M\",\"Debit\":2291.67,\"Date\":\"4/15/24\"}]","payloadType":"json","x":150,"y":4700,"wires":[["aa6c56f7e185d730"]]},{"id":"aa6c56f7e185d730","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"Numero\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"Date\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"Code Journal\":\"GS\",\t    \"Exercice\":24,\t    \"Compte\": $.\"Account Number\",\t    \"Libelle\": $.\"Account Name\",\t    \"Debit\": $.Debit ? $.Debit : \"\",\t    \"Credit\": $.Credit ? $.Credit : \"\",\t    \"Devise\":\"\",\t    \"Piece\":\"\",\t    \"Affaire\":\"B000030\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":4760,"wires":[["aac60417a328c697","923fab2668a587f8"]]},{"id":"aac60417a328c697","type":"csv","z":"d1395164b4eec73e","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":490,"y":4760,"wires":[["13e55929a3f886a0"]]},{"id":"923fab2668a587f8","type":"debug","z":"d1395164b4eec73e","name":"debug 2489","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":430,"y":4700,"wires":[]},{"id":"13e55929a3f886a0","type":"debug","z":"d1395164b4eec73e","name":"debug 2490","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":4700,"wires":[]}]

The expression

$$.payload.{
    "Numero": $moment($.Date, "MM/DD/YY").format("[BCUSA]YY[PR]MMDDYYYY"),
    "Date": $moment($.Date, "MM/DD/YY").format("DD/MM/YYYY"),
    "Code Journal":"GS",
    "Exercice":24,
    "Compte": $."Account Number",
    "Libelle": $."Account Name",
    "Debit": $.Debit ? $.Debit : "",
    "Credit": $.Credit ? $.Credit : "",
    "Devise":"",
    "Piece":"",
    "Affaire":"B000030"
}
5

image
image1872×906 97.7 KB

[{"id":"30c577c1.ce10e8","type":"inject","z":"5400a130.bbd76","name":"Initialization","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","payload":"[{\"Account Number\":6515098,\"Account Name\":\"Payroll Expenses: Gross Wages: Panico, M\",\"Employee\":\"Panico, Miranda S\",\"Debit\":3464.75,\"Date\":\"4/15/24\"},{\"Account Number\":6515102,\"Account Name\":\"Payroll Expenses: Gross Wages: Scheindel, Monika\",\"Employee\":\"Scheindel, Monika M\",\"Debit\":2291.67,\"Date\":\"4/15/24\"}]","payloadType":"json","x":270,"y":200,"wires":[["a2596d30.bc2a3"]]},{"id":"a2596d30.bc2a3","type":"split","z":"5400a130.bbd76","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":310,"y":280,"wires":[["26c10aea.f47826","332cb41b.26293c"]]},{"id":"26c10aea.f47826","type":"debug","z":"5400a130.bbd76","name":"after split","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":220,"wires":[]},{"id":"332cb41b.26293c","type":"switch","z":"5400a130.bbd76","name":"Check Debit","property":"payload.Debit","propertyType":"msg","rules":[{"t":"null"},{"t":"nnull"}],"checkall":"false","repair":false,"outputs":2,"x":490,"y":280,"wires":[["bb323284.efa04"],["85323343.d245d"]]},{"id":"bb323284.efa04","type":"change","z":"5400a130.bbd76","name":"Converter","rules":[{"t":"set","p":"payslip.Numero","pt":"msg","to":"\"BCUSA\"\t& $moment($$.payload.Date, \"MM/DD/YY\").format(\"YY\")\t& \"PR\"\t& $replace($moment($$.payload.Date, \"MM/DD/YY\").format(\"MMDDYYYY\"), \"/\", \"\")","tot":"jsonata"},{"t":"set","p":"payslip.Date","pt":"msg","to":"$moment($$.payload.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\")","tot":"jsonata"},{"t":"set","p":"payslip[\"Code Journal\"]","pt":"msg","to":"GS","tot":"str"},{"t":"set","p":"payslip.Exercice","pt":"msg","to":"24","tot":"num"},{"t":"set","p":"payslip.Compte","pt":"msg","to":"payload[\"Account Number\"]","tot":"msg"},{"t":"set","p":"payslip.Libelle","pt":"msg","to":"payload[\"Account Name\"]","tot":"msg"},{"t":"set","p":"payslip.Debit","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Credit","pt":"msg","to":"payload.Credit","tot":"msg"},{"t":"set","p":"payslip.Devise","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Piece","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Affaire","pt":"msg","to":"B000030","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":240,"wires":[["454a87cbe3d047c3"]]},{"id":"85323343.d245d","type":"change","z":"5400a130.bbd76","name":"Converter","rules":[{"t":"set","p":"payslip.Numero","pt":"msg","to":"\"BCUSA\"\t& $moment($$.payload.Date, \"MM/DD/YY\").format(\"YY\")\t& \"PR\"\t& $replace($moment($$.payload.Date, \"MM/DD/YY\").format(\"MMDDYYYY\"), \"/\", \"\")","tot":"jsonata"},{"t":"set","p":"payslip.Date","pt":"msg","to":"$moment($$.payload.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\")","tot":"jsonata"},{"t":"set","p":"payslip[\"Code Journal\"]","pt":"msg","to":"GS","tot":"str"},{"t":"set","p":"payslip.Exercice","pt":"msg","to":"24","tot":"num"},{"t":"set","p":"payslip.Compte","pt":"msg","to":"payload[\"Account Number\"]","tot":"msg"},{"t":"set","p":"payslip.Libelle","pt":"msg","to":"payload[\"Account Name\"]","tot":"msg"},{"t":"set","p":"payslip.Debit","pt":"msg","to":"payload.Debit","tot":"msg"},{"t":"set","p":"payslip.Credit","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Devise","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Piece","pt":"msg","to":"","tot":"str"},{"t":"set","p":"payslip.Affaire","pt":"msg","to":"B000030","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":320,"wires":[["454a87cbe3d047c3"]]},{"id":"454a87cbe3d047c3","type":"junction","z":"5400a130.bbd76","x":780,"y":280,"wires":[["a3901add2cc10d26","d0bdeb00.eeb068"]]},{"id":"a3901add2cc10d26","type":"change","z":"5400a130.bbd76","name":"move payslip -> payload","rules":[{"t":"move","p":"payload","pt":"msg","to":"original","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payslip","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":930,"y":280,"wires":[["1099c5bd6ca4440e","9d9a1204226fa1fe"]]},{"id":"d0bdeb00.eeb068","type":"debug","z":"5400a130.bbd76","name":"After convert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":340,"wires":[]},{"id":"1099c5bd6ca4440e","type":"join","z":"5400a130.bbd76","name":"auto join","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1160,"y":280,"wires":[["c4da4c63.3e4cf","52d3872a922b517e"]]},{"id":"9d9a1204226fa1fe","type":"debug","z":"5400a130.bbd76","name":"After move","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1010,"y":220,"wires":[]},{"id":"c4da4c63.3e4cf","type":"csv","z":"5400a130.bbd76","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1310,"y":280,"wires":[["9438380f.a113d8"]]},{"id":"52d3872a922b517e","type":"debug","z":"5400a130.bbd76","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1140,"y":340,"wires":[]},{"id":"9438380f.a113d8","type":"debug","z":"5400a130.bbd76","name":"In CSV format","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1340,"y":220,"wires":[]}]
6

Thank you for your help guys,

I was trying to apply your advices in my flow but, I notes that the join node works when I use only 2 objects and don't works when I use all my data.

There is the flow I've used to test it :

[{"id":"a6772b5d.7fd4d8","type":"tab","label":"Flow2test","disabled":false,"info":""},{"id":"55ee51d8.c5926","type":"split","z":"a6772b5d.7fd4d8","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":330,"y":180,"wires":[["367c504d.90fed","45f1b418.cf081c"]]},{"id":"45f1b418.cf081c","type":"debug","z":"a6772b5d.7fd4d8","name":"Base data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":120,"wires":[]},{"id":"367c504d.90fed","type":"switch","z":"a6772b5d.7fd4d8","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":490,"y":240,"wires":[["5de8d4a4.f1032c"]]},{"id":"8ba4da9a.de0c68","type":"debug","z":"a6772b5d.7fd4d8","name":"In CSV format","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1200,"y":300,"wires":[]},{"id":"846b7ffe.c2e84","type":"debug","z":"a6772b5d.7fd4d8","name":"After convert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":300,"wires":[]},{"id":"5de8d4a4.f1032c","type":"change","z":"a6772b5d.7fd4d8","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"Numero\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"Date\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"Code Journal\":\"GS\",\t    \"Exercice\":24,\t    \"Compte\": $.\"Account Number\",\t    \"Libelle\": $.\"Account Name\",\t    \"Debit\": $.Debit ? $.Debit : \"\",\t    \"Credit\": $.Credit ? $.Credit : \"\",\t    \"Devise\":\"\",\t    \"Piece\":\"\",\t    \"Affaire\":\"B000030\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":240,"wires":[["846b7ffe.c2e84","33af0d1.6aa54f2"]]},{"id":"36e4e1df.9767be","type":"csv","z":"a6772b5d.7fd4d8","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1010,"y":240,"wires":[["8ba4da9a.de0c68"]]},{"id":"33af0d1.6aa54f2","type":"join","z":"a6772b5d.7fd4d8","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":850,"y":240,"wires":[["36e4e1df.9767be","dcf5bbd2.7a6aa8"]]},{"id":"dcf5bbd2.7a6aa8","type":"debug","z":"a6772b5d.7fd4d8","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1020,"y":180,"wires":[]},{"id":"1976038e.70b70c","type":"inject","z":"a6772b5d.7fd4d8","name":"Initialization","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","payload":"[{\"Account Number\":6515098,\"Account Name\":\"Payroll Expenses: Gross Wages: Panico, M\",\"Employee\":\"Panico, Miranda S\",\"Debit\":3464.75,\"Date\":\"4/15/24\"},{\"Account Number\":6515102,\"Account Name\":\"Payroll Expenses: Gross Wages: Scheindel, Monika\",\"Employee\":\"Scheindel, Monika M\",\"Debit\":2291.67,\"Date\":\"4/15/24\"}]","payloadType":"json","x":170,"y":180,"wires":[["55ee51d8.c5926"]]},{"id":"a6ce5123.ed9fb","type":"inject","z":"a6772b5d.7fd4d8","name":"Initialization","props":[],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","x":170,"y":440,"wires":[["7afed7e8.92e088"]]},{"id":"7afed7e8.92e088","type":"file in","z":"a6772b5d.7fd4d8","name":"Pay slips","filename":"pay_slip.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":300,"y":520,"wires":[["3448d9d1.bc1956"]]},{"id":"3448d9d1.bc1956","type":"csv","z":"a6772b5d.7fd4d8","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"2","strings":true,"include_empty_strings":"","include_null_values":"","x":410,"y":600,"wires":[["ec3ab745.2d0198"]]},{"id":"ec3ab745.2d0198","type":"split","z":"a6772b5d.7fd4d8","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":570,"y":600,"wires":[["e59d0c5.7ac45f","8027deb1.2b906"]]},{"id":"8027deb1.2b906","type":"debug","z":"a6772b5d.7fd4d8","name":"Base data","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":540,"wires":[]},{"id":"e59d0c5.7ac45f","type":"switch","z":"a6772b5d.7fd4d8","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":730,"y":660,"wires":[["4d5793f8.c4cd2c"]]},{"id":"a818724d.245d9","type":"debug","z":"a6772b5d.7fd4d8","name":"In CSV format","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1440,"y":720,"wires":[]},{"id":"1946ab7e.f56f45","type":"debug","z":"a6772b5d.7fd4d8","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1110,"y":720,"wires":[]},{"id":"4d5793f8.c4cd2c","type":"change","z":"a6772b5d.7fd4d8","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"Numero\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"Date\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"Code Journal\":\"GS\",\t    \"Exercice\":24,\t    \"Compte\": $.\"Account Number\",\t    \"Libelle\": $.\"Account Name\",\t    \"Debit\": $.Debit ? $.Debit : \"\",\t    \"Credit\": $.Credit ? $.Credit : \"\",\t    \"Devise\":\"\",\t    \"Piece\":\"\",\t    \"Affaire\":\"B000030\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":920,"y":660,"wires":[["1946ab7e.f56f45","9ebebe02.aa1b5"]]},{"id":"d2cd0f0d.e56e1","type":"csv","z":"a6772b5d.7fd4d8","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1250,"y":660,"wires":[["a818724d.245d9"]]},{"id":"9ebebe02.aa1b5","type":"join","z":"a6772b5d.7fd4d8","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1090,"y":660,"wires":[["d2cd0f0d.e56e1","5925b42b.5320bc"]]},{"id":"5925b42b.5320bc","type":"debug","z":"a6772b5d.7fd4d8","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1260,"y":600,"wires":[]}]
7

Add debug nodes to your flow and work out at what point it is failing. Then show us what you are feeding into the failing node, what is coming out, and how you have configured it.

8

Normally you can see everything of the flow :

If you wanna show more in details, there is the JSON of the flow in my previous message.

9

I just want you to do the detective work. Work out exactly which node is not doing what you expect, what its inputs are and what its outputs are and should be.

10

Also, I should point out, your version of node-red does NOT contain the newer CSV parser. The current implementation has issues with spaces in the template and is not fully RFC4180 compliant.

If you are willing to upgrade to Node-RED v4 beta, the CSV node has had a make over and is now much more close to full RFC compliance and is a good percentage faster too.

for reference: CSV node outputs non valid RFC4180 CSV data under certain conditions ¡ Issue #3934 ¡ node-red/node-red ¡ GitHub

11

I don't know what's the last version of Node-RED but I think I've the last version ? :

Capture d’écran 2024-05-16 à 16.09.27
Capture d’écran 2024-05-16 à 16.09.27624×836 99.4 KB

Anyway, despite my research, I still don't understand how to solve my problem with my join node... I don't think the CSV node is a problem.

12

I have no idea what v4.4.0 is but by the looks of it you are running Node-RED version 1.3.7 which is VERY old. I also assume the version of node is seriously out of date (end of life)

The problems you are facing way well be partly related to those ↑ facts.

I gave you a working demo here: Missing values after passing through the csv node - #5 by Steve-Mcl - what is the issue? Reply with a minimal demo flow that demonstrates your issue.

13

You can find on the top of the flow my original work and underside your flow which I've adapted with my new version :

[{"id":"99e019d5.6f0748","type":"tab","label":"Payrolls","disabled":false,"info":"# Pay slips\n\n---\n\n## Context\n\nThis flow aims to **convert U.S. payrolls** into the format accepted by Akuiteo, the Braincube ERP.\n\nThe goal of this program is to **lighten the workload for U.S. accountants** that actually convert this files manually before import them into Akuiteo.\n\n---\n\n*Pierre Demay*"},{"id":"88afbed.6d18c4","type":"inject","z":"99e019d5.6f0748","name":"Initialization","props":[],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","x":270,"y":220,"wires":[["82817428.553bd8"]]},{"id":"82817428.553bd8","type":"file in","z":"99e019d5.6f0748","name":"Payrolls","filename":"/Users/pierre.demay/Downloads/payroll.csv","filenameType":"str","format":"utf8","chunk":false,"sendError":false,"encoding":"none","allProps":false,"x":400,"y":300,"wires":[["c54bd09e.bae91"]]},{"id":"c54bd09e.bae91","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"2","strings":true,"include_empty_strings":"","include_null_values":"","x":510,"y":380,"wires":[["3c85a2f7.61c58e"]]},{"id":"3c85a2f7.61c58e","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":380,"wires":[["6c104440.bdd89c"]]},{"id":"6c104440.bdd89c","type":"switch","z":"99e019d5.6f0748","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":830,"y":440,"wires":[["41c67a30.b3b1b4"]]},{"id":"d1e7d6bf.c81308","type":"file","z":"99e019d5.6f0748","name":"Converted payrolls","filename":"convert_payroll.csv","filenameType":"msg","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1550,"y":440,"wires":[[]]},{"id":"49acb3e9.6d52fc","type":"debug","z":"99e019d5.6f0748","name":"In CSV format","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1540,"y":500,"wires":[]},{"id":"609ed4e2.c3687c","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1370,"y":320,"wires":[]},{"id":"41c67a30.b3b1b4","type":"change","z":"99e019d5.6f0748","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"EC_NUMERO\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"EC_DATE\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"EC_CODE_JOURNAL\":\"GS\",\t    \"EC_EXERCICE\":24,\t    \"LI_COMPTE\": $.\"Account Number\",\t    \"LI_LIBELLE\": $.\"Account Name\",\t    \"LI_DEBIT\": $.Debit ? $.Debit : \"\",\t    \"LI_CREDIT\": $.Credit ? $.Credit : \"\",\t    \"LI_DEVISE\":\"USD\",\t    \"LI_PIECE\":\"\",\t    \"LI_AFFAIRE\":\"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1020,"y":440,"wires":[["9e408c43e92333db"]]},{"id":"4adff48d.dca0dc","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"EC_NUMERO,EC_DATE,EC_CODE_JOURNAL,EC_EXERCICE,LI_COMPTE,LI_LIBELLE,LI_DEBIT,LI_CREDIT,LI_DEVISE,LI_PIECE,LI_AFFAIRE","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1350,"y":440,"wires":[["49acb3e9.6d52fc"]]},{"id":"8c31afa3.d689c","type":"join","z":"99e019d5.6f0748","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1190,"y":440,"wires":[["4adff48d.dca0dc","6c7a3826.4cb128"]]},{"id":"6c7a3826.4cb128","type":"debug","z":"99e019d5.6f0748","name":"After join","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1360,"y":380,"wires":[]},{"id":"9e408c43e92333db","type":"function","z":"99e019d5.6f0748","name":"Project finder","func":"if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"1\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"2\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6500\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6501\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6510\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6515\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6520\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6525\") {\n    msg.payload.LI_AFFAIRE = \"???\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6530\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6600\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6603\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6608\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6609\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6610\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6615\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6620\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6625\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6700\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6715\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1110,"y":360,"wires":[["8c31afa3.d689c","609ed4e2.c3687c"]]},{"id":"30c577c1.ce10e8","type":"inject","z":"99e019d5.6f0748","name":"Initialization","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","payload":"[{\"Account Number\":6515098,\"Account Name\":\"Payroll Expenses: Gross Wages: Panico, M\",\"Employee\":\"Panico, Miranda S\",\"Debit\":3464.75,\"Date\":\"4/15/24\"},{\"Account Number\":6515102,\"Account Name\":\"Payroll Expenses: Gross Wages: Scheindel, Monika\",\"Employee\":\"Scheindel, Monika M\",\"Debit\":2291.67,\"Date\":\"4/15/24\"}]","payloadType":"json","x":270,"y":540,"wires":[["b47f45eddefb7c5c"]]},{"id":"a2596d30.bc2a3","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":710,"y":660,"wires":[["26c10aea.f47826","45d3e4c7104128b9"]]},{"id":"26c10aea.f47826","type":"debug","z":"99e019d5.6f0748","name":"Base data","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":840,"y":600,"wires":[]},{"id":"d0bdeb00.eeb068","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1030,"y":600,"wires":[]},{"id":"1099c5bd6ca4440e","type":"join","z":"99e019d5.6f0748","name":"auto join","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1060,"y":660,"wires":[["c4da4c63.3e4cf","52d3872a922b517e"]]},{"id":"c4da4c63.3e4cf","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1230,"y":660,"wires":[["9438380f.a113d8"]]},{"id":"52d3872a922b517e","type":"debug","z":"99e019d5.6f0748","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1200,"y":600,"wires":[]},{"id":"9438380f.a113d8","type":"debug","z":"99e019d5.6f0748","name":"In CSV format","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1400,"y":660,"wires":[]},{"id":"b47f45eddefb7c5c","type":"file in","z":"99e019d5.6f0748","name":"Payrolls","filename":"/Users/pierre.demay/Downloads/payroll.csv","filenameType":"str","format":"utf8","chunk":false,"sendError":false,"encoding":"none","allProps":false,"x":420,"y":600,"wires":[["6b3dcf64453dcbe1"]]},{"id":"6b3dcf64453dcbe1","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"2","strings":true,"include_empty_strings":"","include_null_values":"","x":550,"y":660,"wires":[["a2596d30.bc2a3"]]},{"id":"45d3e4c7104128b9","type":"change","z":"99e019d5.6f0748","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"EC_NUMERO\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"EC_DATE\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"EC_CODE_JOURNAL\":\"GS\",\t    \"EC_EXERCICE\":24,\t    \"LI_COMPTE\": $.\"Account Number\",\t    \"LI_LIBELLE\": $.\"Account Name\",\t    \"LI_DEBIT\": $.Debit ? $.Debit : \"\",\t    \"LI_CREDIT\": $.Credit ? $.Credit : \"\",\t    \"LI_DEVISE\":\"USD\",\t    \"LI_PIECE\":\"\",\t    \"LI_AFFAIRE\":\"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":880,"y":660,"wires":[["d0bdeb00.eeb068","1099c5bd6ca4440e"]]},{"id":"cd5796bbb3a63de7","type":"switch","z":"99e019d5.6f0748","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":790,"y":720,"wires":[[]]},{"id":"d823f7215f31ed92","type":"function","z":"99e019d5.6f0748","name":"Project finder","func":"if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"1\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"2\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6500\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6501\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6510\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6515\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6520\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6525\") {\n    msg.payload.LI_AFFAIRE = \"???\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6530\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6600\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6603\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6608\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6609\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6610\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6615\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6620\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6625\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6700\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6715\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":970,"y":720,"wires":[[]]},{"id":"cac9b08bdc2b1c51","type":"comment","z":"99e019d5.6f0748","name":"Between Split and Converter","info":"","x":740,"y":760,"wires":[]},{"id":"164d22d946227d65","type":"comment","z":"99e019d5.6f0748","name":"Between Converter and Join","info":"","x":1020,"y":760,"wires":[]}]

As you can see, I separate two node : a split node and a function node.
The split node is used to ignore the "totals" line from my file, and the function nodeallows me to set LI_AFFAIRE value.

Without them, the join node has no problems but when I link one of this two, it doesn't work anymore.

14

I have no idea what that means - you have NOT provided a working demo flow (I do not have access to your file /Users/pierre.demay/Downloads/payroll.csv

Please create a reproducible demo flow that highlights your issue.

You can include the CSV data in a template node (that will simulate the file read node) - sanitized of course. Also, it does not have to be hundreds of rows, keep things minimal, a few rows should suffice.

15

Is that enough?

[{"id":"99e019d5.6f0748","type":"tab","label":"Payrolls","disabled":false,"info":"# Pay slips\n\n---\n\n## Context\n\nThis flow aims to **convert U.S. payrolls** into the format accepted by Akuiteo, the Braincube ERP.\n\nThe goal of this program is to **lighten the workload for U.S. accountants** that actually convert this files manually before import them into Akuiteo.\n\n---\n\n*Pierre Demay*"},{"id":"3c85a2f7.61c58e","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":380,"wires":[["6c104440.bdd89c"]]},{"id":"6c104440.bdd89c","type":"switch","z":"99e019d5.6f0748","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":830,"y":440,"wires":[["41c67a30.b3b1b4"]]},{"id":"d1e7d6bf.c81308","type":"file","z":"99e019d5.6f0748","name":"Converted payrolls","filename":"convert_payroll.csv","filenameType":"msg","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1550,"y":440,"wires":[[]]},{"id":"49acb3e9.6d52fc","type":"debug","z":"99e019d5.6f0748","name":"In CSV format","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1540,"y":500,"wires":[]},{"id":"609ed4e2.c3687c","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1370,"y":320,"wires":[]},{"id":"41c67a30.b3b1b4","type":"change","z":"99e019d5.6f0748","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"EC_NUMERO\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"EC_DATE\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"EC_CODE_JOURNAL\":\"GS\",\t    \"EC_EXERCICE\":24,\t    \"LI_COMPTE\": $.\"Account Number\",\t    \"LI_LIBELLE\": $.\"Account Name\",\t    \"LI_DEBIT\": $.Debit ? $.Debit : \"\",\t    \"LI_CREDIT\": $.Credit ? $.Credit : \"\",\t    \"LI_DEVISE\":\"USD\",\t    \"LI_PIECE\":\"\",\t    \"LI_AFFAIRE\":\"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1020,"y":440,"wires":[["9e408c43e92333db"]]},{"id":"4adff48d.dca0dc","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"EC_NUMERO,EC_DATE,EC_CODE_JOURNAL,EC_EXERCICE,LI_COMPTE,LI_LIBELLE,LI_DEBIT,LI_CREDIT,LI_DEVISE,LI_PIECE,LI_AFFAIRE","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1350,"y":440,"wires":[["49acb3e9.6d52fc"]]},{"id":"8c31afa3.d689c","type":"join","z":"99e019d5.6f0748","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1190,"y":440,"wires":[["4adff48d.dca0dc","6c7a3826.4cb128"]]},{"id":"6c7a3826.4cb128","type":"debug","z":"99e019d5.6f0748","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1360,"y":380,"wires":[]},{"id":"9e408c43e92333db","type":"function","z":"99e019d5.6f0748","name":"Project finder","func":"if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"1\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"2\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6500\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6501\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6510\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6515\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6520\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6525\") {\n    msg.payload.LI_AFFAIRE = \"???\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6530\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6600\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6603\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6608\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6609\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6610\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6615\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6620\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6625\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6700\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6715\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1110,"y":360,"wires":[["8c31afa3.d689c","609ed4e2.c3687c"]]},{"id":"a2596d30.bc2a3","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":710,"y":660,"wires":[["26c10aea.f47826","45d3e4c7104128b9"]]},{"id":"26c10aea.f47826","type":"debug","z":"99e019d5.6f0748","name":"After split","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":840,"y":600,"wires":[]},{"id":"d0bdeb00.eeb068","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1030,"y":600,"wires":[]},{"id":"1099c5bd6ca4440e","type":"join","z":"99e019d5.6f0748","name":"auto join","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1060,"y":660,"wires":[["c4da4c63.3e4cf","52d3872a922b517e"]]},{"id":"c4da4c63.3e4cf","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"Numero,Date,Code Journal,Exercice,Compte,Libelle,Debit,Credit,Devise,Piece,Affaire","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1230,"y":660,"wires":[["9438380f.a113d8"]]},{"id":"52d3872a922b517e","type":"debug","z":"99e019d5.6f0748","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1200,"y":600,"wires":[]},{"id":"9438380f.a113d8","type":"debug","z":"99e019d5.6f0748","name":"In CSV format","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1400,"y":660,"wires":[]},{"id":"45d3e4c7104128b9","type":"change","z":"99e019d5.6f0748","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"EC_NUMERO\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"EC_DATE\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"EC_CODE_JOURNAL\":\"GS\",\t    \"EC_EXERCICE\":24,\t    \"LI_COMPTE\": $.\"Account Number\",\t    \"LI_LIBELLE\": $.\"Account Name\",\t    \"LI_DEBIT\": $.Debit ? $.Debit : \"\",\t    \"LI_CREDIT\": $.Credit ? $.Credit : \"\",\t    \"LI_DEVISE\":\"USD\",\t    \"LI_PIECE\":\"\",\t    \"LI_AFFAIRE\":\"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":880,"y":660,"wires":[["d0bdeb00.eeb068","1099c5bd6ca4440e"]]},{"id":"cd5796bbb3a63de7","type":"switch","z":"99e019d5.6f0748","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"}],"checkall":"false","repair":false,"outputs":1,"x":790,"y":720,"wires":[[]]},{"id":"d823f7215f31ed92","type":"function","z":"99e019d5.6f0748","name":"Project finder","func":"if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"1\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"2\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6500\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6501\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6510\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6515\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6520\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6525\") {\n    msg.payload.LI_AFFAIRE = \"???\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6530\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6600\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6603\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6608\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6609\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6610\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6615\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6620\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6625\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6700\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6715\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":970,"y":720,"wires":[[]]},{"id":"cac9b08bdc2b1c51","type":"comment","z":"99e019d5.6f0748","name":"Between Split and Converter","info":"","x":740,"y":760,"wires":[]},{"id":"164d22d946227d65","type":"comment","z":"99e019d5.6f0748","name":"Between Converter and Join","info":"","x":1020,"y":760,"wires":[]},{"id":"af74f3f779a4e63f","type":"inject","z":"99e019d5.6f0748","name":"Initialization","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Credit\":4.55,\"Date\":\"4/15/24\"},{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Debit\":402.34,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Credit\":16.51,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Debit\":384.47,\"Date\":\"4/15/24\"},{\"Account Number\":6700045,\"Account Name\":\"Payroll Expenses:BONUS: Hannan, Alicia\",\"Employee\":\"Hannan, Alicia C\",\"Debit\":1000,\"Date\":\"4/15/24\"},{\"Account Number\":\"Totals\",\"Debit\":223208.73,\"Credit\":223208.73}]","payloadType":"json","x":550,"y":660,"wires":[["a2596d30.bc2a3"]]},{"id":"c111d91887101d41","type":"inject","z":"99e019d5.6f0748","name":"Initialization","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Credit\":4.55,\"Date\":\"4/15/24\"},{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Debit\":402.34,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Credit\":16.51,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Debit\":384.47,\"Date\":\"4/15/24\"},{\"Account Number\":6700045,\"Account Name\":\"Payroll Expenses:BONUS: Hannan, Alicia\",\"Employee\":\"Hannan, Alicia C\",\"Debit\":1000,\"Date\":\"4/15/24\"},{\"Account Number\":\"Totals\",\"Debit\":223208.73,\"Credit\":223208.73}]","payloadType":"json","x":510,"y":380,"wires":[["3c85a2f7.61c58e"]]}]

I don't understand. Is it better to do this or the inject node done the job?

16

Perfect.

Your issue is you are removing items between split and join

What you should understand is the split nodes add info in msg.parts to aid the join node in understanding when all of the messages have been processed (this is detailed in the split and join nodes built-in help). So, since you remove unwanted items, the join node does not receive all parts and it doesnt know your operations are finished!

there are a number of ways to handle this. for example,

  • you can set msg.complete on the last message to tell the join node to release all of its messages.
  • you could build an array in a function and manually join them
  • you could set a flag e.g. msg.payload.unwanted and then filter out the unwanted items AFTER the join has done its magic

Here is a demo flow that does the 3rd option. Others may provide better solutions.

[{"id":"3c85a2f7.61c58e","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":590,"y":380,"wires":[["6c104440.bdd89c"]]},{"id":"6c104440.bdd89c","type":"switch","z":"99e019d5.6f0748","name":"Check Totals","property":"payload[\"Account Number\"]","propertyType":"msg","rules":[{"t":"neq","v":"Totals","vt":"str"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":750,"y":380,"wires":[["41c67a30.b3b1b4"],["2205a62c64c9881c"]]},{"id":"d1e7d6bf.c81308","type":"file","z":"99e019d5.6f0748","name":"Converted payrolls","filename":"convert_payroll.csv","filenameType":"msg","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1630,"y":460,"wires":[[]]},{"id":"49acb3e9.6d52fc","type":"debug","z":"99e019d5.6f0748","name":"In CSV format","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1420,"y":460,"wires":[]},{"id":"609ed4e2.c3687c","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1410,"y":360,"wires":[]},{"id":"41c67a30.b3b1b4","type":"change","z":"99e019d5.6f0748","name":"Converter","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t    \"EC_NUMERO\": $moment($.Date, \"MM/DD/YY\").format(\"[BCUSA]YY[PR]MMDDYYYY\"),\t    \"EC_DATE\": $moment($.Date, \"MM/DD/YY\").format(\"DD/MM/YYYY\"),\t    \"EC_CODE_JOURNAL\":\"GS\",\t    \"EC_EXERCICE\":24,\t    \"LI_COMPTE\": $.\"Account Number\",\t    \"LI_LIBELLE\": $.\"Account Name\",\t    \"LI_DEBIT\": $.Debit ? $.Debit : \"\",\t    \"LI_CREDIT\": $.Credit ? $.Credit : \"\",\t    \"LI_DEVISE\":\"USD\",\t    \"LI_PIECE\":\"\",\t    \"LI_AFFAIRE\":\"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":940,"y":360,"wires":[["9e408c43e92333db"]]},{"id":"4adff48d.dca0dc","type":"csv","z":"99e019d5.6f0748","name":"","sep":",","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"EC_NUMERO,EC_DATE,EC_CODE_JOURNAL,EC_EXERCICE,LI_COMPTE,LI_LIBELLE,LI_DEBIT,LI_CREDIT,LI_DEVISE,LI_PIECE,LI_AFFAIRE","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":1250,"y":460,"wires":[["49acb3e9.6d52fc"]]},{"id":"8c31afa3.d689c","type":"join","z":"99e019d5.6f0748","name":"","mode":"auto","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1250,"y":400,"wires":[["6c7a3826.4cb128","aec095ba4be632ca"]]},{"id":"6c7a3826.4cb128","type":"debug","z":"99e019d5.6f0748","name":"After join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1400,"y":400,"wires":[]},{"id":"9e408c43e92333db","type":"function","z":"99e019d5.6f0748","name":"Project finder","func":"if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"1\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 1) == \"2\") {\n    msg.payload.LI_AFFAIRE = \"B000030\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6500\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6501\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6510\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6515\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6520\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6525\") {\n    msg.payload.LI_AFFAIRE = \"???\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6530\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6600\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6603\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6608\") {\n    msg.payload.LI_AFFAIRE = \"B000029\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6609\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6610\") {\n    msg.payload.LI_AFFAIRE = \"B000024\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6615\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6620\") {\n    msg.payload.LI_AFFAIRE = \"B000026\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6625\") {\n    msg.payload.LI_AFFAIRE = \"B000040\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6700\") {\n    msg.payload.LI_AFFAIRE = \"B000031\";\n    return msg;\n} else if (msg.payload.LI_COMPTE.toString().substr(0, 4) == \"6715\") {\n    msg.payload.LI_AFFAIRE = \"B000025\";\n    return msg;\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1090,"y":360,"wires":[["8c31afa3.d689c","609ed4e2.c3687c"]]},{"id":"c111d91887101d41","type":"inject","z":"99e019d5.6f0748","name":"Initialization 6 rows","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Credit\":4.55,\"Date\":\"4/15/24\"},{\"Account Number\":6620500,\"Account Name\":\"Payroll Expenses:Social Charges:Social Charges-Tech\",\"Employee\":\"Yamada, Kuranosuke\",\"Debit\":402.34,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Credit\":16.51,\"Date\":\"4/15/24\"},{\"Account Number\":662550,\"Account Name\":\"Payroll Expenses:Social Charges:Support\",\"Employee\":\"Cojo, Ekaterina\",\"Debit\":384.47,\"Date\":\"4/15/24\"},{\"Account Number\":6700045,\"Account Name\":\"Payroll Expenses:BONUS: Hannan, Alicia\",\"Employee\":\"Hannan, Alicia C\",\"Debit\":1000,\"Date\":\"4/15/24\"},{\"Account Number\":\"Totals\",\"Debit\":223208.73,\"Credit\":223208.73}]","payloadType":"json","x":430,"y":380,"wires":[["3c85a2f7.61c58e"]]},{"id":"2205a62c64c9881c","type":"change","z":"99e019d5.6f0748","name":"mark unwanted","rules":[{"t":"set","p":"payload.unwanted","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":400,"wires":[["8c31afa3.d689c"]]},{"id":"aec095ba4be632ca","type":"function","z":"99e019d5.6f0748","name":"remove unwanted","func":"msg.payload = msg.payload.filter(row => !row.unwanted)\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":460,"wires":[["4adff48d.dca0dc"]]}]
17

As I understand it:

  • The flow input is a multi-line CSV file.
  • It is split (into individual lines).
  • After some manipulation local to each line, the line is written to another CSV file.

So long as the file-out node is set to append, what is the point of having a join node?
Delete the file at the start of the flow and append each line individually.

1 Like
18

There are several issues I see

  • You split the array but discard elements , to add to Steve's solutions, this can be solved also using recreate message sequence in the switch node.
  • Your data element where Account Number equals "Totals" has no date or other info, this is messing with your JSONata. Scratch tha, i missed the !=. Saying that all could be done in single JSONata, if we had the output you required.
  • This seem similar to OG issue but again is completely different. If this is a new converter then please start a new issue. It would also be best to show what the final output from the inject input you are trying to get at.
19

The only disadvantage I see is that since the filename is being dynamically generated/provided, the file will be closed and re-opened every time (and probably seeked upon each append). I suspect it will be slower. Other than that, great idea & would avoid the issue with managing dropped messages.

20

A feature I've have never used nor read up on!