Strange behaviour Csv node

I have a strange issue with the core Csv node.

When the check is set for Include empty strings there are always 2 message's per csv row.
The first message only contains the first column name with no value.
If you disable the check there is only the expected 1 message per row with the object that contains the csv data.

Running on a Windows 10 machine with NR3.0.2, updated test machine to V3.1.3 has the same issue.

[{"id":"61b9e0b5ddd16b80","type":"csv","z":"9e38d6c8b155bad2","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":true,"include_null_values":true,"x":530,"y":460,"wires":[["7856411b210cf3f9"]]},{"id":"cbbab2d7a33172b0","type":"file in","z":"9e38d6c8b155bad2","name":"","filename":"payload","filenameType":"msg","format":"utf8","chunk":false,"sendError":false,"encoding":"utf8","allProps":false,"x":540,"y":380,"wires":[["61b9e0b5ddd16b80","cc6f864112b94d3d"]]},{"id":"85abdc5b6dde425f","type":"inject","z":"9e38d6c8b155bad2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"C:\\\\temp\\\\booking.csv","payloadType":"str","x":560,"y":300,"wires":[["cbbab2d7a33172b0"]]},{"id":"7856411b210cf3f9","type":"debug","z":"9e38d6c8b155bad2","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":760,"y":460,"wires":[]},{"id":"cc6f864112b94d3d","type":"debug","z":"9e38d6c8b155bad2","name":"debug 11","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":760,"y":380,"wires":[]}]

Can't upload csv file here, this is the content.

Date,Username,Day,Start,End,Duration,Room
"aap","gerrits","Wednesday","09:00","17:30","","(SYL) 2.4.17"

Not strange, your csv file most likely has a new line at end
e.g.

Date,Username,Day,Start,End,Duration,Room
"aap","gerrits","Wednesday","09:00","17:30","","(SYL) 2.4.17"

Try removing it before the csv node. Or create the csv file without a newline at end.
e.g.

[{"id":"85abdc5b6dde425f","type":"inject","z":"d1395164b4eec73e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"C:\\\\temp\\\\booking.csv","payloadType":"str","x":560,"y":6660,"wires":[["b57fb3379ebd0e3c"]]},{"id":"b57fb3379ebd0e3c","type":"template","z":"d1395164b4eec73e","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Date,Username,Day,Start,End,Duration,Room\n\"aap\",\"gerrits\",\"Wednesday\",\"09:00\",\"17:30\",\"\",\"(SYL) 2.4.17\"\n","output":"str","x":520,"y":6720,"wires":[["cc6f864112b94d3d","4f8ad8590f6f20f7"]]},{"id":"cc6f864112b94d3d","type":"debug","z":"d1395164b4eec73e","name":"debug 11","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":760,"y":6740,"wires":[]},{"id":"4f8ad8590f6f20f7","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$trim($$.payload)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":6760,"wires":[["61b9e0b5ddd16b80"]]},{"id":"61b9e0b5ddd16b80","type":"csv","z":"d1395164b4eec73e","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":true,"include_null_values":true,"x":530,"y":6820,"wires":[["7856411b210cf3f9"]]},{"id":"7856411b210cf3f9","type":"debug","z":"d1395164b4eec73e","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":760,"y":6820,"wires":[]}]

I've seen this before... it's usually caused by your CSV data being authored in Windows, so each line ends with cr nl characters (including the header line).

For some reason this is parsed as 2 line separators -- you can verify by looking at a hex dump of your data file. If that is indeed the cause, it would be a bug in the csv node's line handling code.

Thanks for the quick reply's.

Looking at the csv file with Notepad++ there's indeed a CR and LF

Notepad

Removing the CR so only the LF is left solved the problem.
Will try the example provided by E1cid to see if it works correct for me.

I will report it as bug on Github.

Removing the CR or LF with the JSONata $trim was not working for me. Not that expert in JSONata why not.

Did the job now in a function node,

msg.payload = msg.payload.replace(/(\r)/gm, "")

That is because @shrickus nailed the issue.

my presumption was close, but trim would not have worked.
Should of been $replace($$.payload,"\r", "") as the \r was not at end but in middle.

Anyway you found your own solution, which is great.

2 Likes

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