# Create CSV file from MSSQL-Plus stored procedure?

**URL:** <https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516>\
**Category:** General\
**Created:** [9 August 2021 09:17 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516 "2021-08-09T09:17:46Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![grant1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grant1/32/49890_2.png) [@grant1](https://discourse.nodered.org/u/grant1)\
**Post date:** [9 August 2021 09:17 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/1 "2021-08-09T09:17:46Z")

</div>

Is there a way to create a csv file from an array that is being returned by a MSSQL stored procedure? I would like for this .csv file to be created & placed on a network share or other convenient location.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/35a48c6896efffb947825792f1edb987d4788b08.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 August 2021 10:17 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/2 "2021-08-09T10:17:17Z")

</div>

Yes. But without data examples it is hard to say how.

---

<div class="post-metadata">

**Author:** ![grant1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grant1/32/49890_2.png) [@grant1](https://discourse.nodered.org/u/grant1)\
**Post date:** [9 August 2021 11:14 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/3 "2021-08-09T11:14:40Z")

</div>

Here is what the CSV looks like that is generated by the stored procedure.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/0/2033c3a54a2e7c60861979fa31e2f63c263f2a96.png)

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [9 August 2021 11:34 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/4 "2021-08-09T11:34:35Z")

</div>

> [@grant1](#):
>
> Is there a way to create a csv file from an array that is being returned by a MSSQL stored procedure?

I use the CSV and File nodes to export out the data returned from a SQL query in mySQL and then FTP node to put it on a NAS (not stored procedure from MSSQL), but I think it should be the similar. The relevant section of the flow is below - hope this helps.

 ![Screen Shot 2021-08-09 at 6.31.12 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/0/c0c74848d5909df402331f3c2f31b70b37658ffc.jpeg)

```auto
[{"id":"62e86954.30b21","type":"switch","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Record count > 0","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"gt","v":"0","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":270.66668701171875,"y":2681,"wires":[["e044e5aa.ccf9"],["7b739ef5.766258"]]},{"id":"e044e5aa.ccf9","type":"csv","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":481.66668701171875,"y":2676,"wires":[["a1a3d10a.321a7"]]},{"id":"a1a3d10a.321a7","type":"file","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Save Logs CSV","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":642.9999694824219,"y":2675.66650390625,"wires":[["2025b474.5c5fb4"]]},{"id":"2025b474.5c5fb4","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Set FTP File Paths","rules":[{"t":"set","p":"localFilename","pt":"msg","to":"filename","tot":"msg"},{"t":"set","p":"filename","pt":"msg","to":"ftpFilename","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":860.9998474121094,"y":2675.666748046875,"wires":[["55eda512.15332c"]]}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 August 2021 11:51 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/5 "2021-08-09T11:51:25Z")

</div>

It would be best to supply data in a copyable format.  
You need to supply a copy of the incoming data from the sql query and a copy of how the csv should look.  
Images are not any help when supplying data.

---

<div class="post-metadata">

**Author:** ![grant1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grant1/32/49890_2.png) [@grant1](https://discourse.nodered.org/u/grant1)\
**Post date:** [9 August 2021 17:58 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/6 "2021-08-09T17:58:44Z")

</div>

Thank you for the pointers. The CSV node should do the trick, but for some reason I am getting only 4 rows of my expected output.

When I put the Debug node at the end of the MSSQL-PLUS node, I can see the expected output:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/6/76f5b24ab41d13cb60eed56e79ef5d201727009b.png)

But when I put the Debug node at the end of the CSV node, I am getting only the first 4 rows of data (the blue colored rows). The Excel file is as I get the data from SQL.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/f/1ff773463d41050901e04dba4e697bd67a1e5377.png)

Here is how I configured the CSV node. I have tried several different settings and still get the same general results. **Is there any possible reason why the CSV would only capture the first 4 rows?**

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/0/3015a9c875ff913e651c5a5be30fd0f5f4c2363a.png)

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [9 August 2021 18:11 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/7 "2021-08-09T18:11:48Z")

</div>

> [@grant1](#):
>
> Is there any possible reason why the CSV would only capture the first 4 rows?

If you are looking at the output in the debug node, I believe that node displays only a limited number of characters. See this topic:

> [@The complete msg.payload in Debug - howto](https://discourse.nodered.org/t/the-complete-msg-payload-in-debug-howto/7951/3):
>
> Actually, the option to show the "complete msg object" will not help showing such a long string... it is there to show the top level fields that are defined on the incoming msg object (topic and payload are just two of those fields, but there are others). There is a section in settings.js that will allow you to show longer data fields in the debug sidebar: // The maximum length, in characters, of any message sent to the debug sidebar tab debugMaxLength: 1000, Any changes you make to this file…

If you use the file node to output the data, I think you will see the entire dataset.

My CSV Node Configuration:

 ![Screen Shot 2021-08-09 at 1.23.00 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/0/208ba3ee43ad5879017d8e685f289dfeba56098e.jpeg)

My file node configuration:

 ![Screen Shot 2021-08-09 at 1.23.23 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/2/5225c7db1d9abaf06a94de2f3304cb36f53c3dfa.png)

---

<div class="post-metadata">

**Author:** ![grant1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grant1/32/49890_2.png) [@grant1](https://discourse.nodered.org/u/grant1)\
**Post date:** [11 August 2021 00:30 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/8 "2021-08-11T00:30:13Z")

</div>

Thank you! The CSV node + File node is all that was needed. The rest is just me working out the nitty-gritty details of the CSV and then saving the file somewhere where I can access it. Really appreciate this.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [25 August 2021 00:30 UTC](https://discourse.nodered.org/t/create-csv-file-from-mssql-plus-stored-procedure/49516/9 "2021-08-25T00:30:35Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.