By this, I change my flow and it works !
The only modification I do is to inject my file rather than 5 or 6 objects.
The file is perfect but the debits and credits are a bit buggy :
That's a little weird and I think it from the change node called "Converter", but I don't find the problem...
Alignment of numbers in a spreadsheet is often related to the value being considered a string or a number. Check the data BEFORE it goes into CSV node - do numbers look like numbers or strings? (numbers are shown in blue, strings in redish orange) and are USUALLY determined by the first row of data
see how LI_CREDIT is an empty string - better off setting them to 0 or null than empty string.
Seems odd that some rows are recognised as numbers (right aligned) and others are interpreted as strings (left aligned)
Perhaps there is extra non printing characters in the data.
Try adding $number() around the 1st arg of the tertiary statements.
e.g, something like. "LI_DEBIT": ($.Debit || $.Debit == 0 || $.Debit == "0") ? $number($.Debit) : null
(I am not a fan of JSONata - super slow and difficult syntax)
Friendly reminder: Please do NOT post pictures of code. Post the code in a code block.
Pictures of code are not searchable, text from within cannot easily be copy/pasted (to provide fixes) and they are not as easy to read!
Thanks for that reminder, I keep that in mind 
[{"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":"b6f48a9686873260","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":490,"y":440,"wires":[["70df4b713dbde058"],["83a97711d2124147"]]},{"id":"c61fccdafaa0d73f","type":"file","z":"99e019d5.6f0748","name":"Converted payrolls","filename":"/Users/pierre.demay/Downloads/converted_payroll.csv","filenameType":"str","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1250,"y":480,"wires":[[]]},{"id":"39c7f94430e9bc18","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":1380,"y":420,"wires":[]},{"id":"01e93f44240ae48d","type":"debug","z":"99e019d5.6f0748","name":"After convert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":340,"wires":[]},{"id":"70df4b713dbde058","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 == 0 || $.Debit == \"0\") ? $number($.Debit) : null,\t \"LI_CREDIT\": ($.Credit || $.Credit == 0 || $.Credit == \"0\") ? $number($.Credit) : null\t \"LI_DEVISE\": \"USD\",\t \"LI_PIECE\": \"\",\t \"LI_AFFAIRE\": \"\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":400,"wires":[["3f75f2b15d06a79c"]]},{"id":"6e7ff3542689d007","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":1210,"y":420,"wires":[["39c7f94430e9bc18","c61fccdafaa0d73f"]]},{"id":"fd06513d329c277d","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":850,"y":420,"wires":[["9357b06e56fe6e74","06bef7c14f1874c2"]]},{"id":"9357b06e56fe6e74","type":"debug","z":"99e019d5.6f0748","name":"After join","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1000,"y":480,"wires":[]},{"id":"3f75f2b15d06a79c","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":790,"y":340,"wires":[["fd06513d329c277d","01e93f44240ae48d"]]},{"id":"83a97711d2124147","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":680,"y":480,"wires":[["fd06513d329c277d"]]},{"id":"06bef7c14f1874c2","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":1030,"y":420,"wires":[["6e7ff3542689d007"]]},{"id":"367231415fcc33e2","type":"split","z":"99e019d5.6f0748","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":330,"y":440,"wires":[["b6f48a9686873260","3da73b9bc9f311bf"]]},{"id":"3bcc7f4a6bac6b1e","type":"inject","z":"99e019d5.6f0748","name":"Initialization","props":[],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"","x":170,"y":320,"wires":[["4963d8f88af763f7"]]},{"id":"4963d8f88af763f7","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":340,"y":320,"wires":[["5aee9603e5fc56ef"]]},{"id":"5aee9603e5fc56ef","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":330,"y":380,"wires":[["367231415fcc33e2"]]},{"id":"3da73b9bc9f311bf","type":"debug","z":"99e019d5.6f0748","name":"Base data","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":500,"wires":[]},{"id":"01b8721a3bd9f145","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":170,"y":440,"wires":[["367231415fcc33e2"]]}]
I tried to understand what's the problem and I think it's the multiple condition that doesn't work (not fan of JSONata too).
Yeah, i rearly do JSONata (it is awful IMO, but some folk like it)
Here is a working version instead of the rubbish I posted earlier:
$$.payload.{
"EC_NUMERO": $moment($.Date, "MM/DD/YY").format("[BCUSA]YY[PR]MMDDYYYY"),
"EC_DATE": $moment($.Date, "MM/DD/YY").format("DD/MM/YYYY"),
"EC_CODE_JOURNAL": "GS",
"EC_EXERCICE": 24,
"LI_COMPTE": $."Account Number",
"LI_LIBELLE": $."Account Name",
"LI_DEBIT": ($.Debit or $.Debit = 0 or $.Debit = "0") ? $number($.Debit) : null,
"LI_CREDIT": ($.Credit or $.Credit = 0 or $.Credit = "0") ? $number($.Credit) : null,
"LI_DEVISE": "USD",
"LI_PIECE": "",
"LI_AFFAIRE": ""
}
Key points.
| JavaScript | JSONata |
|---|---|
= |
:= |
== |
= |
|| |
or |
&& |
and |
Bah!
EDIT:
FYI, I added $.Debit = 0 or $.Debit = "0" since I am not certain if JSONata = is a type strict like JS ===
I tried and, the change node "Converter" works but, there is the same problem in the csv file (same when I replace null by 0)
I don't resend you my flow cause it's the same as above not to mention the fact that I've modified the change node.
I have currently found a way to view the csv file in the mac file manager.
The file looks fine to me, but I still don't understand why it doesn't display properly when I open it on Google Sheet.
UP : it's also ok for Excel 
JSONata is strict 0 != "0"
So you would not want to see the whole thing transformed in one JSONata expression?
Ouch, my eyes
Steve McLaughlin a man unable to see.
We can rebuild him, we have the technology, we have the capability to make the first six million node-red seeing man.
p.s. My dad was one of the first to get a Cochlea implant, he hears to well now, he can even link to blue tooth micro phones.
Thank you all for your help !
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks⢠or registeredŽ trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
