# From MSSQL to csv to email

**URL:** <https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315>\
**Category:** General\
**Created:** [14 August 2019 11:50 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315 "2019-08-14T11:50:52Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![petrdolejs](https://avatars.discourse-cdn.com/v4/letter/p/eada6e/32.png) [@petrdolejs](https://discourse.nodered.org/u/petrdolejs)\
**Post date:** [14 August 2019 11:50 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/1 "2019-08-14T11:50:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [14 August 2019 12:30 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/2 "2019-08-14T12:30:01Z")

</div>

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`

```auto
//set payload to a buffer
msg.payload = Buffer.from(msg.payload);
return msg;

```

---

<div class="post-metadata">

**Author:** ![petrdolejs](https://avatars.discourse-cdn.com/v4/letter/p/eada6e/32.png) [@petrdolejs](https://discourse.nodered.org/u/petrdolejs)\
**Post date:** [15 August 2019 05:06 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/3 "2019-08-15T05:06:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [15 August 2019 08:45 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/4 "2019-08-15T08:45:44Z")

</div>

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...

```auto
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...

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![Georg25](https://avatars.discourse-cdn.com/v4/letter/g/ed8c4c/32.png) [@Georg25](https://discourse.nodered.org/u/Georg25)\
**Post date:** [3 April 2021 08:43 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/5 "2021-04-03T08:43:52Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 April 2021 08:52 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/6 "2021-04-03T08:52:25Z")

</div>

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](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

> [@Georg25](#):
>
> Timestamp and payload are variable in this case and I can´t manage that the CSV node includes them

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?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 April 2021 09:29 UTC](https://discourse.nodered.org/t/from-mssql-to-csv-to-email/14315/7 "2021-04-03T09:29:10Z")

</div>

try this...

```auto
[{"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":[]}]

```