Missing values after passing through the csv node

pierredemay · 17 2024 14:41

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 :

(left:debit right:credit)

That's a little weird and I think it from the change node called "Converter", but I don't find the problem...

Steve-Mcl · 17 2024 15:28

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.

pierredemay · 21 2024 07:50

Unfortunately, this doesn't seem to be the solution.

Steve-Mcl · 21 2024 08:01

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!

pierredemay · 21 2024 09:03

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).

Steve-Mcl · 21 2024 09:17

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 ===

pierredemay · 21 2024 09:56

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.

pierredemay · 21 2024 10:57

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

E1cid · 21 2024 15:30

JSONata is strict 0 != "0"

So you would not want to see the whole thing transformed in one JSONata expression?

Steve-Mcl · 21 2024 15:43

Ouch, my eyes

E1cid · 21 2024 15:55

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.

pierredemay · 23 2024 09:26

Thank you all for your help !

system · 21 2024 09:26

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

Topic		Replies	Views
While Passing Array to Node and Reformating Array into an Object Data Gets Lost General	3	386	5 2019
How to create csv/excel from object names and values General	2	184	10 2023
After hours of google still cant create a CSV? General	5	209	15 2022
Any "change node" for JSON object General	5	1581	23 2020
HELP Convert array of objects to array of arrays using change node General	9	273	7 January 2023

Missing values after passing through the csv node

Related Topics