First Post - Combine csv node with my filename assignment

[DWoodlock]

Hello All,

Recently started getting my feet wet with node-red, and slowly progressing, but I've reached a point where I thought I would ask for guidance.

1. Connect to OPCUA server - done
2. Connect to local network share - done using SMBv2
3. Convert the OPCUA data into a CSV file, and save a new file with a filename which is derived from one of the strings provided by the OPCUA.

I have broken it into pieces, current status:

• I can create a new file on the share with the correct file name, but no data inside.
• I can also create a new .csv file on the share which contains the desired data, but not able assign the correct file name.

My thought was perhaps I can create a function which appends the msg.filename in front of all the .csv messages?

P.S. I'm a PLC guy, so Javascript is a new thing to me, and I expect this is either really simple and dumb, or not possible.

Thanks in advance

[TotallyInformation]

Write File can take a filename as a msg input. Check the help for the node. You've already got function nodes in play so just set a msg.filename and make sure to leave the filename blank in the write file node.

[DWoodlock]

The flow in Red (salmon?) works fine for creating a correctly named .csv file.

The example I provided was crappy: It is showing when I tried to combine the two into one, thinking that maybe that was all it took....WRONG.

Anyway this is the 2 individualized ones, If implemented they would be connected to the Join up above in the first picture.

Thanks

[jbudd]

@DWoodlock

P.S. I'm a PLC guy, so Javascript is a new thing to me

Congratulations on choosing Node-red then, a programming language where you can do most things without Javascript, or any procedural language whatsoever

You do not need discrete flows to create a file and then to write to it.
Here is a sample flow which injects some sample data, converts it to CSV format and writes it to a file.
You can import, deploy and play with it. You will probably need to change the output file pathname.
I have simplified setting msg.filename because you didn't say how you derive this, nor include and sample data.

Input data

[
{"name":"Night Riviera","from":"Paddington","departs":"23:45","to":"Penzance","arrives":"07:07"},
{"name":"Caledonian Sleeper","from":"Euston","departs":"20:20","to":"Fort William","arrives":"10:00"}
]

Complete message passed to write file node.
NB the data is in msg.payload and the filename in msg.filename

{
"_msgid":"7a6f48fd9fe2ccd5",
"payload":"Night Riviera,Paddington,Penzance,23:45,07:07\nCaledonian Sleeper,Euston,Fort William,20:20,10:00\n","topic":"","columns":"name,from,to,departs,arrives",
"filename":"/home/pi/junk.csv"
}

Copy and import this code into your Node-red

[{"id":"ca11361396e92b08","type":"file","z":"278bd8f3b69d98a3","name":"","filename":"filename","filenameType":"msg","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":720,"y":100,"wires":[[]]},{"id":"d2fd467a91221e97","type":"csv","z":"278bd8f3b69d98a3","name":"","sep":",","hdrin":"","hdrout":"once","multi":"one","ret":"\\n","temp":"name, \"from\", \"to\", \"departs\", \"arrives\"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":330,"y":100,"wires":[["681b847fdbd88f7a","045535e0759d75ca"]]},{"id":"05f55bde8999fdfc","type":"inject","z":"278bd8f3b69d98a3","name":"Sample Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"name\":\"Night Riviera\",\"from\":\"Paddington\",\"departs\":\"23:45\",\"to\":\"Penzance\",\"arrives\":\"07:07\"},{\"name\":\"Caledonian Sleeper\",\"from\":\"Euston\",\"departs\":\"20:20\",\"to\":\"Fort William\",\"arrives\":\"10:00\"}]","payloadType":"json","x":110,"y":100,"wires":[["88763cb2819cf638","d2fd467a91221e97"]]},{"id":"88763cb2819cf638","type":"debug","z":"278bd8f3b69d98a3","name":"Input payload","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":300,"y":60,"wires":[]},{"id":"681b847fdbd88f7a","type":"debug","z":"278bd8f3b69d98a3","name":"As CSV","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":500,"y":60,"wires":[]},{"id":"045535e0759d75ca","type":"change","z":"278bd8f3b69d98a3","name":"","rules":[{"t":"set","p":"filename","pt":"msg","to":"/home/pi/junk.csv","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":100,"wires":[["ca6daab9bc59599b","ca11361396e92b08"]]},{"id":"ca6daab9bc59599b","type":"debug","z":"278bd8f3b69d98a3","name":"Complete msg object","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":760,"y":60,"wires":[]}]

[DWoodlock]

@jbudd

{
"_msgid":"7a6f48fd9fe2ccd5",
"payload":"Night Riviera,Paddington,Penzance,23:45,07:07\nCaledonian Sleeper,Euston,Fort William,20:20,10:00\n","topic":"","columns":"name,from,to,departs,arrives",
"filename":"/home/pi/junk.csv"
}

Thank you for the examples, I will be looking through them today. Big help!

[DWoodlock]

Thank you for the example, has proven very helpful.

One question that remains as a result is trying to assign the msg.filename from one of the values within the payload.

Below is an example of one of the way's I've tried, but the value and string concat doesn't seem to work, as I continually get a result of unassigned filename.

I find myself wondering if this fails because I'm trying to access a objects value, after it's been converted to .csv format?

Thanks

[DWoodlock]

I was able to get it working by creating a flow variable, and storing my filename before creating the .csv.

Definitely not saying this is the right way, but it works thus far.

Thanks again

[Steve-Mcl]

Hi, please post code as text (in a code block) - it is then searchable and anyone suggesting a change can copy+paste (instead of re-typing)

To my point: On line 1 of your first screenshot, msg.payload will be set to undefined because flow.set does not return a value.

[DWoodlock]

Thanks for the feedback,

My only goal with that first line was to create and assign the following concatenation to something, FileFormat in this case, so I can later use that variable to assign a filename.

[DWoodlock]

I went in, and modified it to only set the flow variable without assigning to a payload, and it works fine, no change.

[jbudd]

Do you have control over the structure of the data coming in to Node-red?
You will make life easier if you can get rid of the spaces in Log Sub File Name eg LogSubFileName, then you can refer to it using dot notation instead of square brackets msg.payload.LogSubFileNode

You have discovered that you can't just put javascript code in the change node.
There are a couple of options.

1. The change node can handle JSONata expressions. In JSONata the concatenation operator is & so this would look like
   
   
   
   You see that JSONata syntax varies a fair bit from javascript (eg no square brackets) and therefore many people avoid using it, me included.
2. You can write javascript code in a function node:
   
   
   
   Your function node code works but it's generally better to avoid using context variables, you certainly don't need one here.
3. In the write-file node, the Filename field also accepts JSONata expressions so the same expression as in option 1 can be entered here. This is probably not appropriate for you because msg.payload["Log Sub File Name"] will probably be gone after the CSV node.

NB I am assuming that the CSV node will pass msg.filename unchanged alongside the converted msg.payload.

ps You might want to specify a full path name for the file location eg in Linux it might be "/home/pi/mydata/something.csv"

[E1cid]

As jbudd's suggestion

@jbudd

operator is & so this would look like

You could also us the JSONata direct in the file write node, in the filename input set to JSONata $$.payload."Log Sub File Name" & ".csv"

[jbudd]

Oh yeah $$. Another reason to shun JSONata (which @E1cid is the local guru for!)

Is that space between payload. and "Log Sub File Name" OK in JSONata?

[E1cid]

Yes it is OK, But is was a typo. $$, not much difference than using msg, and helps a lot to tell Jsonata what scope context to use when creating more complex expressions. I find it a good practice to use.

[DWoodlock]

@jbudd

Do you have control over the structure of the data coming in to Node-red?
You will make life easier if you can get rid of the spaces in Log Sub File Name eg LogSubFileName, then you can refer to it using dot notation instead of square brackets msg.payload.LogSubFileNode

I actually believe I do. TBH I changed the .txt file which reads the tag definitions to include the spaces because of OCD(I guess). I started this project with a flow, and trying to reengineer it to serve a slightly different purpose, so I'm learning as I go. Appreciate the information. This is the .txt file:

ns=2;s=Application.DataLogging.WriteLogData;datatype=Boolean;name=Write Log Data
ns=2;s=Application.DataLogging.WriteLogDataACK;datatype=Boolean;name=Write Log Data ACK
ns=2;s=Application.DataLogging.WriteLogDataError;datatype=Boolean;name=Write Log Data Error
ns=2;s=Application.DataLogging.LogSubFileName;datatype=String;name=Log Sub File Name
ns=2;s=Application.ProgramRecipe.Results.DateOfEntry;datatype=String;name=Date
ns=2;s=Application.ProgramRecipe.Results.TimeOfDay;datatype=String;name=Time
ns=2;s=Application.ProgramRecipe.Results.Operator;datatype=UInt32;name=Employee Number
ns=2;s=Application.ProgramRecipe.Results.JobNumber;datatype=String;name=Job Number
ns=2;s=Application.ProgramRecipe.Results.ModelCode;datatype=String;name=Model Number
ns=2;s=Application.ProgramRecipe.Results.SerialNum;datatype=String;name=Serial Number
ns=2;s=Application.ProgramRecipe.Results.TestType;datatype=String;name=Test Type
ns=2;s=Application.ProgramRecipe.Results.Result;datatype=String;name=Result
ns=2;s=Application.ProgramRecipe.Results.FillCaseFlow;datatype=Float;name=Fill Case Flow
ns=2;s=Application.ProgramRecipe.Results.ReliefPressure;datatype=Float;name=Relief Pressure
ns=2;s=Application.ProgramRecipe.Results.ShiftPressure;datatype=Float;name=Shift Pressure
ns=2;s=Application.ProgramRecipe.Results.Temp1At400;datatype=Float;name=Temp 1 At 400 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At400;datatype=Float;name=Temp 2 At 400 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At800;datatype=Float;name=Temp 1 At 800 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At800;datatype=Float;name=Temp 2 At 800 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At1200;datatype=Float;name=Temp 1 At 1200 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At1200;datatype=Float;name=Temp 2 At 1200 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At1600;datatype=Float;name=Temp 1 At 1600 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At1600;datatype=Float;name=Temp 2 At 1600 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At2000;datatype=Float;name=Temp 1 At 2000 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At2000;datatype=Float;name=Temp 2 At 2000 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At2400;datatype=Float;name=Temp 1 At 2400 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At2400;datatype=Float;name=Temp 2 At 2400 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At2800;datatype=Float;name=Temp 1 At 2800 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At2800;datatype=Float;name=Temp 2 At 2800 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp1At3000;datatype=Float;name=Temp 1 At 3000 RPM
ns=2;s=Application.ProgramRecipe.Results.Temp2At3000;datatype=Float;name=Temp 2 At 3000 RPM
ns=2;s=Application.ProgramRecipe.Results.BearCaseFlow;datatype=Float;name=Bearing Case Flow
ns=2;s=Application.ProgramRecipe.Results.BearBackPress;datatype=Float;name=Bearing Back Pressure
ns=2;s=Application.ProgramRecipe.Results.DispRotorSpeed;datatype=Float;name=Displacement Rotor Speed
ns=2;s=Application.ProgramRecipe.Results.DispMainFlow;datatype=Float;name=Displacement Main Flow
ns=2;s=Application.ProgramRecipe.Results.DispCaseFlow;datatype=Float;name=Displacement Case Flow
ns=2;s=Application.ProgramRecipe.Results.DispBackPress;datatype=Float;name=Displacement Back Pressure
ns=2;s=Application.ProgramRecipe.Results.RunInCaseFlow;datatype=Float;name=Run In Case Flow
ns=2;s=Application.ProgramRecipe.Results.RunInMainFlow;datatype=Float;name=Run In Main Flow
ns=2;s=Application.ProgramRecipe.Results.RunInPressure;datatype=Float;name=Run In Pressure
ns=2;s=Application.ProgramRecipe.Results.RunInTime;datatype=String;name=Run In Time

As I recall, originally the names at the ends didn't have any spaces, but I didn't know any better, so I changed them for visual representation's sake on the dashboard.

@jbudd

Your function node code works but it's generally better to avoid using context variables, you certainly don't need one here.

Good to know, will make an edit and avoid going forward.

@jbudd

ps You might want to specify a full path name for the file location eg in Linux it might be "/home/pi/mydata/something.csv"

Thanks for this as well, my understanding is that our client has arranged a windows share directory, what that means as far as file path is still sort of TBD. I am trying to simulate as best as I can in my office using XM22 Controller>CtrlX core(running node-red)>my PC as "server share directory". I fully expect this may need to change when it comes implementation time.

Thanks again

[jbudd]

I don't know what it is about JSONata, it quickly gets impenetrable for me, yet years and years ago I wholeheartedly embraced regular expressions, surely no less cryptic.

It doesn't help that there are tutorials online but they all seem to focus on extracting subsets of the data rather than manipulating it the way we tend to in a change node.