# Missing values after passing through the csv node

**URL:** <https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978>\
**Category:** General\
**Created:** [15 May 2024 13:19 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978 "2024-05-15T13:19:57Z")\
**Posts on this page:** 13\
**Page:** 2

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [17 May 2024 14:41 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/21 "2024-05-17T14:41:17Z")

</div>

> [@Steve-Mcl](#):
>
> Here is a demo flow that does the 3rd option. Others may provide better solutions.

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 :

 ![Capture d’écran 2024-05-17 à 16.40.02](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/1/71f6ca62be371843a651ccf48a0eaff87f781666.png)  
(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...

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [17 May 2024 15:28 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/22 "2024-05-17T15:28:53Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/80586d5985cd04fb554c8f9e0438f7e00981c588.png)  
see how LI\_CREDIT is an empty string - better off setting them to `0` or `null` than empty string.

---

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [21 May 2024 07:50 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/23 "2024-05-21T07:50:37Z")

</div>

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

 ![Capture d’écran 2024-05-21 à 09.35.56](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/c/cc5c223211f3307f49f7a3cbf12f70f629fa51d1.png)  
 ![Capture d’écran 2024-05-21 à 09.35.21](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/7/078f084684f58f1096a551dfa6f6f51ad16e3619.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2024 08:01 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/24 "2024-05-21T08:01:02Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [21 May 2024 09:03 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/25 "2024-05-21T09:03:34Z")

</div>

Thanks for that reminder, I keep that in mind 😉

```auto
[{"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)_.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2024 09:17 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/26 "2024-05-21T09:17:33Z")

</div>

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:

```auto
$$.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 `===`

---

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [21 May 2024 09:56 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/27 "2024-05-21T09:56:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [21 May 2024 10:57 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/28 "2024-05-21T10:57:06Z")

</div>

I have currently found a way to view the csv file in the mac file manager.

 ![Capture d’écran 2024-05-21 à 12.53.40](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/2/32c2bd7abc6708caf80ae5c2cd2d15291a03325b.jpeg)

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 👌🏼

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [21 May 2024 15:30 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/29 "2024-05-21T15:30:27Z")

</div>

> [@Steve-Mcl](#):
>
> EDIT:
> 
> FYI, I added `$.Debit = 0 or $.Debit = "0"` since I am not certain if JSONata `=` is a type strict like JS `===`

JSONata is strict 0 != "0"

> [@pierredemay](#):
>
> _(not fan of JSONata too)_.

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

 ![chrome_screenshot_21 May 2024 16_25_23 BST](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/3/e30e21d43b2a7e8c6f850da8a3566c1df87d921a.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2024 15:43 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/30 "2024-05-21T15:43:08Z")

</div>

> [@E1cid](#):
>
> So you would not want to see the whole thing transformed in one JSONata expression

Ouch, my eyes

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [21 May 2024 15:55 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/31 "2024-05-21T15:55:31Z")

</div>

> [@Steve-Mcl](#):
>
> 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.

> **[Oregon scientists are building a better bionic eye](https://www.opb.org/article/2023/09/13/university-oregon-scientists-bionic-eye-retinal-implant-technology/)**
>
> Serious vision loss affects millions of Americans each year, and biological strategies are still decades away from restoring eyesight lost to macular degeneration. But University of Oregon researchers are looking to create an electronic solution — a...

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.

---

<div class="post-metadata">

**Author:** ![pierredemay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pierredemay/32/90948_2.png) [@pierredemay](https://discourse.nodered.org/u/pierredemay)\
**Post date:** [23 May 2024 09:26 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/32 "2024-05-23T09:26:24Z")

</div>

Thank you all for your help !

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 August 2024 09:26 UTC](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978/33 "2024-08-21T09:26:57Z")

</div>

Closed on 21 August 2024 09:26 UTC

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

[Previous page](https://discourse.nodered.org/t/missing-values-after-passing-through-the-csv-node/87978.md?page=1)
