Hello,
i want to save some data in a csv formatted file (which i can open with excel),
i used the csv node, but when i open the files in excel it does not show the data in columns (all the line goes in 1 column).
so i want to make it manually, my original string data is written like this: “first,second,third…”, i passed it through a change node which replace comma (,) by: “;” (i do this so as when i open the file with excel every data goes in a separated column), and then i passed it through ‘save to file node’.
now i want to make the first row containing the titles of data (first column title, second column title…), and i couldn’t manage to make it and need some assistance.
I hope that you can help.
Hi - the CSV node should be able to do what you want. What did you try ? what error did you get ?
How did you set the Object to CSV section ?
And how are you sending the data ?
The node will expect an array of objects. and a template in order to specify the order of the columns.
when i try with the csv node
[{"id":"9c3d7011.6e14a8","type":"csv","z":"456ce3eb.038f9c","name":"to csv","sep":",","hdrin":false,"hdrout":true,"multi":"one","ret":"\\r\\n","temp":"First,Second,Third","skip":"0","x":450,"y":500,"wires":[[]]}]
i get something like this:
{“First”:“first data sent”,“Second”:10,“Third”:20}
and when i open this with Excel it shows in 1 column, while i want it to be shown like a table:
First Second Third
data1 10 20
data2 15 30
data3 20 40
while: (First Second Third) are the titles.
That looks like you are converting CSV to an object - not the other way round… What are you sending into the csv node ? It should be more like this
[{"id":"e35b377d.e4f318","type":"inject","z":"a3ad1f25.b7363","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":200,"y":460,"wires":[["1f824c91.14c5e3"]]},{"id":"1f824c91.14c5e3","type":"function","z":"a3ad1f25.b7363","name":"","func":"msg.payload = [{A:1,B:2,C:3},{A:1,B:2,C:3},{A:1,B:2,C:3}]\nreturn msg;","outputs":1,"noerr":0,"x":370,"y":460,"wires":[["9f7c43c1.7d823"]]},{"id":"9f7c43c1.7d823","type":"csv","z":"a3ad1f25.b7363","name":"","sep":",","hdrin":"","hdrout":true,"multi":"one","ret":"\\r\\n","temp":"C,B,A","skip":"0","x":530,"y":460,"wires":[["4205c4d.a73013c"]]},{"id":"4205c4d.a73013c","type":"debug","z":"a3ad1f25.b7363","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":460,"wires":[]}]
Or am I misunderstanding ?.. the csv node converts csv into an object or back… if you already have csv formatted data then just send it to a file out node.
1 Like
i’m sending data (via tcp) as a string, like that:
“title,10,15,20” (this is just an example)
i want the first column to be titled as a specific title, an so the others (in the first row).
can you give us an example of how the output should look then ?
title
10,15,20 ?
title
10
15
20 ?
or where are the column names coming from ? maybe you just need to send a line to the file before the data arrives ?
but if there are no column titles then just send it straight to a file… no need for csv node.
1 Like
if i write in a csv files this:
title1;title2;title3
10;15;20
12;17;30
12;15;16
20;15;16
the result should look like this (and this is what i want):
so i need the output to be like that csv file example, unless there is another way to save the data in the file in which there are column titles (i need those titles to make automatic charts with Excel).
the column names are not coming from the data, i just need to write them one time.
"maybe you just need to send a line to the file before the data arrives ?" exactly.
but i don't have to send it every time, that line should be just in the beginning of the file.
if there are no column titles, yes i agree that sending it directly is better.
So maybe use an inject node set to inject that line once at startup
the 'save to file' node is supposed to append to a file every time, and if i inject the line in every time the flow startup that line will exist more than one time, and i just want it to appear one time in the head of the file.
ok - so create the file with just the header in it outside of Node-RED… and then just have Node-RED append to the file.
this is a good solution if i will be just using one file, but in my case i want node-red to create a file every day and work on it.
You could have an inject node set to trigger at 00:01 every day to create that day’s file with the header in.
i’m not sure if node-red will be running and flow is working in that moment every day.
my goal from saving a csv file is to use excel tools (charts, statistics…), and to do that i need to know the title of every column.
if there is another easier way to stock the data so as i can use excel with it i hope that you guide me to it.
There are lots of ways to solve the problem, but only you know how you want it to work.
For example, Create a flow that, before writing data to the file, checks to see if it exists. If is doesn’t, then prepend the header row to the payload you pass the file node.
I think there’s a contrib node for checking if a file exists - search flows.nodered.org for ‘fs’
1 Like
Thank you very much.
i hope that you check this (i tested it and it seems working well):
[{"id":"1993503b.2a2d88","type":"fs-ops-access","z":"456ce3eb.038f9c","name":"","path":"measurements","pathType":"str","filename":"test.csv","filenameType":"str","read":false,"write":true,"throwerror":false,"x":270,"y":540,"wires":[["f5a19eb1.8cf848"],["58e35a35.3ba4e4"]]},{"id":"d0c0fa9b.978bf","type":"inject","z":"456ce3eb.038f9c","name":"","topic":"test","payload":"15,20,25,30","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":500,"wires":[["1993503b.2a2d88"]]},{"id":"f5a19eb1.8cf848","type":"file","z":"456ce3eb.038f9c","name":"Save to file","filename":"measurements/test.csv","appendNewline":true,"createDir":true,"overwriteFile":"false","x":590,"y":500,"wires":[]},{"id":"58e35a35.3ba4e4","type":"function","z":"456ce3eb.038f9c","name":"add time","func":"msg.payload = \"Title1,title2,title3\" +\"\\n\"+ msg.payload;\nmsg.error = null;\nreturn msg;","outputs":1,"noerr":0,"x":440,"y":560,"wires":[["f5a19eb1.8cf848"]]}]
