From MSSQL to csv to email

Hello, little problem here. I'm reading data from MSSQL and I want them to put in csv file(create that file and put in) and after that send that by email. I think it should be easy but I can't figure that.

Here are my nodes
[{"id":"b965d0f.146f13","type":"inject","z":"f2c4701d.a13f6","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":150,"y":580,"wires":[["6d561629.287b28"]]},{"id":"6d561629.287b28","type":"MSSQL","z":"f2c4701d.a13f6","mssqlCN":"7d98b4ae.aae53c","name":"","query":"SELECT TOP (100) [Pc]\n ,[Datum]\n ,[Cislo_elmeru]\n ,[Hodnota_T1]\n ,[Hodnota_T2]\n FROM [scdb].[dbo].[elmery] ORDER BY Datum DESC","outField":"payload","x":310,"y":600,"wires":[["9cad07f5.3e5248"]]},{"id":"9cad07f5.3e5248","type":"csv","z":"f2c4701d.a13f6","name":"","sep":",","hdrin":true,"hdrout":false,"multi":"one","ret":"\\n","temp":"Pc,Datum,Cislo_elmeru,Hodnota_T1,Hodnota_T2","skip":"0","x":490,"y":640,"wires":[["53539f45.fe98e"]]},{"id":"53539f45.fe98e","type":"e-mail","z":"f2c4701d.a13f6","server":"smtp.gmail.com","port":"465","secure":true,"name":"","dname":"","x":880,"y":640,"wires":[]},{"id":"7d98b4ae.aae53c","type":"MSSQL-CN","z":"","name":"scdb","server":"185.58.41.21","encyption":true,"database":"scdb"}]

The output of the CSV node says...

If the input is an array of arrays, or an array of objects, a multiple-line CSV string is created.

The built in help for email node says...

If the payload is a binary buffer then it will be converted to an attachment.

So try adding a function node between CSV and Email

//set payload to a buffer
msg.payload = Buffer.from(msg.payload);
return msg;
1 Like

I did what you said, now I receive on email file but it's not .csv it's .bin and I can't open it in Excel for example. I also tried instead of csv node Excel node (node-red-contrib-excel) but when I open it in Excel it's clear.

The file is fine - it just needs to be renamed to xxx.csv.

The Email node says...

If the payload is a binary buffer then it will be converted to an attachment. The filename should be set using msg.filename

and

with a subject of msg.topic

so add that to the function node...

msg.topic = "Sensor data attached"
msg.filename = "data.csv"
msg.payload = Buffer.from(msg.payload);
return msg;

Here is a working example I knocked up...

[{"id":"a14fc6ed.f35998","type":"inject","z":"f1d1607b.077aa","name":"Simulated data","topic":"","payload":"[{\"colour\":\"red\",\"value\":\"#f00\"},{\"colour\":\"green\",\"value\":\"#0f0\"},{\"colour\":\"blue\",\"value\":\"#00f\"},{\"colour\":\"cyan\",\"value\":\"#0ff\"},{\"colour\":\"magenta\",\"value\":\"#f0f\"},{\"colour\":\"yellow\",\"value\":\"#ff0\"},{\"colour\":\"black\",\"value\":\"#000\"}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":460,"wires":[["89849c13.01d3e"]]},{"id":"89849c13.01d3e","type":"csv","z":"f1d1607b.077aa","name":"","sep":",","hdrin":false,"hdrout":true,"multi":"one","ret":"\\n","temp":"colour,value","skip":"0","x":290,"y":460,"wires":[["73812b52.25b8d4"]]},{"id":"4c5ed49.07eb82c","type":"e-mail","z":"f1d1607b.077aa","server":"smtp.gmail.com","port":"465","secure":true,"tls":false,"name":"","dname":"","x":730,"y":460,"wires":[]},{"id":"79d99799.5e6318","type":"debug","z":"f1d1607b.077aa","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":590,"y":500,"wires":[]},{"id":"73812b52.25b8d4","type":"function","z":"f1d1607b.077aa","name":"Set filename and convert to buffer","func":"msg.topic = \"Sensor data attached\"\nmsg.filename = \"data.csv\"\nmsg.payload = Buffer.from(msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":500,"y":460,"wires":[["79d99799.5e6318","4c5ed49.07eb82c"]]}]
1 Like