From MSSQL to csv to email

General
1

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"}]

2

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
3

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.

4

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
5

Hi,

I would like to create a report about my water temperature over the whole day and send it to me as a CSV-file via email, to analyze it. To do this, I found you flow, which I want to use a base.
I modified the flow as followed.
[{"id":"89d6a749.8a08d8","type":"inject","z":"ecbfd3ca.f6ae7","name":"Simulated data","props":[{"p":"timestamp","v":"","vt":"date"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":113,"y":517,"wires":[["9e220d54.1c1f3"]]},{"id":"9e220d54.1c1f3","type":"random","z":"ecbfd3ca.f6ae7","name":"Temp Water","low":"-5","high":"45","inte":"true","property":"payload","x":348,"y":502,"wires":[["3a52282c.1a2658"]]},{"id":"3a52282c.1a2658","type":"function","z":"ecbfd3ca.f6ae7","name":"Set filename and convert to buffer","func":"[\n {\n \"Time\": msg.timestamp,\n \"value\": msg.payload,\n },\n \n]\nreturn msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","x":250,"y":365,"wires":[["69326278.0b2f4c","1fe3bda5.426d22"]]},{"id":"1fe3bda5.426d22","type":"csv","z":"ecbfd3ca.f6ae7","name":"","sep":",","hdrin":false,"hdrout":"all","multi":"one","ret":"\\n","temp":"colour,value","skip":"0","strings":true,"include_empty_strings":false,"include_null_values":false,"x":427,"y":257,"wires":[["cf0afa9c.fed218"]]},{"id":"cf0afa9c.fed218","type":"function","z":"ecbfd3ca.f6ae7","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,"initialize":"","finalize":"","x":710,"y":243,"wires":[["e0f2f93d.3eb938","619eada2.872754","6a643f74.988d7"]]},{"id":"6a643f74.988d7","type":"e-mail","z":"ecbfd3ca.f6ae7","server":"smtp.gmail.com","port":"465","secure":true,"tls":true,"name":"","dname":"","x":918,"y":379,"wires":[]}]

Timestamp and payload are variable in this case and I can´t manage that the CSV node includes them.

Do you know I a can solve this problem?
Thanks in advance :slight_smile:

6

Hi. First, you flow cannot be imported. In order to make code more readable and importable it is important to surround your code with three backticks
```
like this
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

That's not really clear. What is your issue? Does the email arrive? Does it have a csv attachment? Does the attachment have your data?

7

try this...

[{"id":"a14fc6ed.f35998","type":"inject","z":"83a23089.dc697","name":"Simulated data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":295,"y":900,"wires":[["3af75735.9c7b28"]],"l":false},{"id":"89849c13.01d3e","type":"csv","z":"83a23089.dc697","name":"","sep":",","hdrin":false,"hdrout":"all","multi":"one","ret":"\\n","temp":"Timestamp,payload","skip":"0","strings":true,"include_empty_strings":false,"include_null_values":false,"x":610,"y":900,"wires":[["73812b52.25b8d4","594171aa.dfd1e"]]},{"id":"4c5ed49.07eb82c","type":"e-mail","z":"83a23089.dc697","server":"smtp.gmail.com","port":"465","secure":true,"tls":false,"name":"","dname":"","x":1050,"y":900,"wires":[]},{"id":"79d99799.5e6318","type":"debug","z":"83a23089.dc697","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":950,"y":960,"wires":[]},{"id":"73812b52.25b8d4","type":"function","z":"83a23089.dc697","name":"Set filename and add content","func":"msg.attachments = [\n    {\n        filename : \"data.csv\",\n        content: Buffer.from(msg.payload),\n    }\n]\n\nmsg.topic = \"Sensor data\";\nmsg.payload = \"See data.csv attached\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":800,"y":900,"wires":[["79d99799.5e6318","4c5ed49.07eb82c"]]},{"id":"3af75735.9c7b28","type":"function","z":"83a23089.dc697","name":"generate random data","func":"var rowCount = 200;\nvar data = [];\nfor (let index = 0; index < rowCount; index++) {\n    const row = {\n        Timestamp: (Date.now())/(1000*60*60*24)+25569, //convert hs timestamp (Date.now()) to excel date number\n        payload: \"random number - \" + (Math.random() * 10)\n    }\n    data.push(row);\n}\nmsg.payload = data;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":900,"wires":[["89849c13.01d3e"]]},{"id":"594171aa.dfd1e","type":"debug","z":"83a23089.dc697","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":960,"wires":[]}]