Hello there,
I want to write a string (with a number and the date) into a csv file. My problem with that is, that I just get the whole string in one column (see first Picture). But i want that the number is in the first (A1) and the date in the second (B1) column.(2 picture)
can anyone help me?
// laden des Objekts
m = Wert1+' '+Time;
return {payload:[m]};
(Wert1 and Time are the variables)
is how I declare it in Node Red
Are passing the output of that Function straight to a File Out node or are you using a CSV node to format your data?
If you are passing the output of that Function straight to a File Out node then you need to put a comma , in between the values you are running - otherwise it isn’t comma-separated values.
var m = Wert1+','+Time;
return {payload:[m]};
no, I m using a CSV node behind the function node to Format the data. But perhaps maybe I have a wrong configuration in the CSV node, or how can i split the string into different columns and not into different rows?
CSV = comma seperated values
i.e. the comma seperates the values passed to the columns so you need to put a comma between Wert1 and Time as Nick showed you.
I tried so, but it still don t work
I wrote exactly the same code than Nick (var m = Wert1+','+Time; )
with the csv configuration as you can see in Picture 1.
connect a debug node to the output of your function node. What does it show?
Can you share your actual flow here? I don’t understand why you are using a CSV node if your Function node is generating the CSV String to write to a file. Without seeing how you’ve wired the nodes together its hard to say what is happening.
It would also help to open the file in a text editor so we can see its actual contents and not what Excel has parsed out of it.
[{"id":"86741c8b.53c03","type":"function","z":"c8832cea.c6494","name":"Gesamtzähler für Backup, I 00000001 [1]","func":"(function() {\n var days = ['Sonntag','Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag'];\n\n var months = ['January','February','March','April','May','June','July','August','September','October','November','December'];\n\n Date.prototype.getMonthName = function() {\n return months[ this.getMonth() ];\n };\n Date.prototype.getDayName = function() {\n return days[ this.getDay() ];\n };\n})();\n\nvar now = new Date();\nvar year = now.getFullYear();\nvar monthX = now.getMonth()+1; \nvar dayX = now.getDate();\nvar hour = now.getHours();\nvar minute = now.getMinutes();\nvar second = now.getSeconds(); \nif(monthX.toString().length == 1) \n{\nvar monthX = '0'+monthX;\n}\nif(dayX.toString().length == 1) \n{\nvar dayX = '0'+dayX;\n} \nif(hour.toString().length == 1) \n{\nvar hour = '0'+hour;\n}\nif(minute.toString().length == 1) \n{\nvar minute = '0'+minute;\n}\nif(second.toString().length == 1) \n{\nvar second = '0'+second;\n} \n\n\n\n\nvar day = now.getDayName();\nvar month = now.getMonthName();\n\n// Variablen für Objekt\nvar Wert1 = global.get('Stundenzähler1FBup') ||0;\nvar Time = global.get('StundeFBup') ||0;\n\n\n// Stunde\n\n // Zählen von Wert1\n Wert1 = global.get('Stundenzähler1FBup')||0;\n Wert1++;\n global.set('Stundenzähler1FBup', Wert1);\n \n \nvar Time = global.set('StundeFBup',day+' '+dayX+'.'+monthX+'.'+year+' '+hour+':'+minute+':'+second); \n\n\n\n\n\n\nreturn msg;\n\n ","outputs":1,"noerr":0,"x":900,"y":40,"wires":[["5545d23f.caea74"]]},{"id":"5545d23f.caea74","type":"function","z":"c8832cea.c6494","name":"erstellt String","func":"(function() {\n var days = ['Sonntag','Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag'];\n\n var months = ['January','February','March','April','May','June','July','August','September','October','November','December'];\n\n Date.prototype.getMonthName = function() {\n return months[ this.getMonth() ];\n };\n Date.prototype.getDayName = function() {\n return days[ this.getDay() ];\n };\n})();\nvar now = new Date();\nvar day = now.getDayName();\nvar month = now.getMonthName();\n\n// Variablen für Objekt\nvar Wert1 = global.get('Stundenzähler1FBup') ||0;\nvar Time = global.get('StundeFBup') ||0;\n\n\n\n\n// laden des Objekts\nvar m =Wert1+','+Time;\n\n\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":1290,"y":40,"wires":[["f5333221.9b2658"]]},{"id":"f5333221.9b2658","type":"csv","z":"c8832cea.c6494","name":"","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"","skip":"0","x":1450,"y":40,"wires":[["55264aea.943134","20667830.21c6f"]]},{"id":"55264aea.943134","type":"file","z":"c8832cea.c6494","name":"","filename":"","appendNewline":false,"createDir":true,"overwriteFile":"false","x":1590,"y":40,"wires":[]},{"id":"20667830.21c6f","type":"debug","z":"c8832cea.c6494","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1610,"y":80,"wires":[]}]You are passing the CSV node an array of strings. From the CSV node's help text:
If the input is an array of simple values, it builds a single line CSV string.
You are passing in [ "114,Montag 25.06.2018 16:04:16"] - an array that contains a String. So the CSV node is treating that whole string as a single value when it generates the CSV output - probably by putting 'quotes' around it due to the fact it contains a ,.
In other words, you are generating CSV in your Function node and then asking the CSV node to re-encode it again.
You have two options:
The fix for the Function is:
return {payload:[Wert1, Time]};
Hi everyone,
I'm struggling with the same problem as dicussed here initially.
I want to save some data in a csv files and only get the data into one column.
Using the fixes here unfortunately won't save my problem.
Would someone be able to help me please and tell me what I'm doing wrong
[{"id":"c70226cf.b082f","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"34662148.7343b6","type":"inject","z":"c70226cf.b082f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"10","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":240,"wires":[["9155baa0.a8f42","92c77911.024668"]]},{"id":"92c77911.024668","type":"function","z":"c70226cf.b082f","name":"Timestamp","func":"// Create a Date object from the payload\nvar date = new Date(msg.payload);\n// Change the payload to be a formatted Date string\nmsg.payload = date.toString();\n// Return the message so it can be sent on\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":510,"y":460,"wires":[["bc3050.04830fb"]]},{"id":"bc3050.04830fb","type":"change","z":"c70226cf.b082f","name":"","rules":[{"t":"set","p":"Timestamp","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":460,"wires":[["1d7bcb03.37a045"]]},{"id":"f7171554.511f8","type":"change","z":"c70226cf.b082f","name":"","rules":[{"t":"set","p":"Sensor1Wert","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":770,"y":560,"wires":[["1d7bcb03.37a045"]]},{"id":"1d7bcb03.37a045","type":"function","z":"c70226cf.b082f","name":"","func":"var Time=flow.get('Timestamp');\nvar Sensor1Wert=flow.get('Sensor1Wert');\nreturn {payload:[Sensor1Wert,Time]};\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1000,"y":520,"wires":[["f9b267b.9474198"]]},{"id":"9155baa0.a8f42","type":"random","z":"c70226cf.b082f","name":"Test Sensor 1","low":"1","high":"100","inte":"true","property":"payload","x":380,"y":200,"wires":[["f7171554.511f8"]]},{"id":"f9b267b.9474198","type":"csv","z":"c70226cf.b082f","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\r\\n","temp":"Sensor1Wert, Time","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":1170,"y":520,"wires":[["501a7235.c49a4c"]]},{"id":"501a7235.c49a4c","type":"file","z":"c70226cf.b082f","name":"","filename":"","appendNewline":false,"createDir":false,"overwriteFile":"false","encoding":"none","x":1330,"y":520,"wires":[[ ]]}]
Welcome to the forum @Mr.816.
Please see this post on how to post flows here so that they are importable.
Please also connect a debug node showing what is going into the csv node, if you are using one, or into the file node otherwise.
In your flow you are putting two separate data items into the payload (from your function node)
return {payload:[Sensor1Wert,Time]};
currently that is a random number and a timestamp. for example:
77,1607337309582
If you want the two items in a single column you will have to concatinate them together.
What do you wish the concatenated data to look like?
77-1607337309582
"77,1607337309582"
77 1607337309582
???
@Colin: Thank you for formating my code. I was looking for the instruction how to post it properly but wasn't able to find it.
I also connected a debug node. The input for the CSV node looks like this:
7.12.2020, 12:41:15node: 25ee6d05.1fb082
msg.payload : array[2]
[ 78, "Mon Dec 07 2020 12:41:15 GMT+0…" ]
@zenofmud : Thank you as well for your reply.
My actual problem is, that I would like to have two seperate columns in my csv file. One for the sensor value one for the timestamp.
I have researched a lot of different options and tried several examples but I was not able to create a csv file with seperate columns. I always have all the data posted in one column. Same problem as originally described here at the beginning.
I think that was @zenofmud actually.
Your flow can be condensed. much can b combined and you don't need the CSV node.
Try this example (make sure to change the path in the file-out to a valid path for the platform you are using
[{"id":"eaf09868.eb44b","type":"inject","z":"c70226cf.b082f","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"TEST","x":130,"y":460,"wires":[["becd8086.ea722"]]},{"id":"c58072d.5619b1","type":"change","z":"c70226cf.b082f","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"Sensor1Wert&','&$millis()","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":140,"y":660,"wires":[["d7e2a943.ccd2a"]]},{"id":"becd8086.ea722","type":"random","z":"c70226cf.b082f","name":"Test Sensor 1","low":"1","high":"100","inte":"true","property":"Sensor1Wert","x":140,"y":560,"wires":[["c58072d.5619b1"]]},{"id":"d7e2a943.ccd2a","type":"file","z":"c70226cf.b082f","name":"","filename":"/Users/Paul/testcsv.csv","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":170,"y":760,"wires":[["6be75d0.9a8e324"]]},{"id":"6be75d0.9a8e324","type":"debug","z":"c70226cf.b082f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":430,"y":760,"wires":[]},{"id":"894da5bf.d0669","type":"comment","z":"c70226cf.b082f","name":"put random number into Sensor1Wert","info":"","x":210,"y":520,"wires":[]},{"id":"2bd4bd82.c3970a","type":"comment","z":"c70226cf.b082f","name":"Start with no payload, just a topic","info":"","x":190,"y":420,"wires":[]},{"id":"2717ca34.2adbf6","type":"comment","z":"c70226cf.b082f","name":"build msg with JSONata","info":"","x":170,"y":620,"wires":[]},{"id":"99ddc5c6.302e7","type":"comment","z":"c70226cf.b082f","name":"Append the msg to the CSV file NOTE: Put the full path to where the file will be","info":"","x":340,"y":720,"wires":[]},{"id":"13ab9854.2ef39","type":"comment","z":"c70226cf.b082f","name":"This path is from a flow running on a Mac.","info":"","x":220,"y":800,"wires":[]}]
Thank you so much for the flow and the description.
I imported it an tried it out. Unfortuanately my csv file has still all the data in one column:
The debug node shows the following input for the file out node:
7.12.2020, 19:28:27node: 6be75d0.9a8e324
TEST : msg : Object
{ _msgid: "aeca13f9.e159b", topic: "TEST", Sensor1Wert: 84, payload: "84,1607365707105" }
Did you open or inport the file? A CSV file is a file where the columns are seperated by a comma which is the case for this file.
This is the result when I open the file in Numbers (macOS)
and here is what it looks like when opened in LibraOffice
What product are you importing the file too?
Are you looking for the file to be tab separated?
Thank you so much again for your answer. I'm using Windows and Excel to access the csv file.
I checked it and I made a mistake importing the file, thats why the columns were not seperated.
Can't believe I was searching in the code the whole time...
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