# Sending CSV Data to MSSQL Server

**URL:** <https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922>\
**Category:** General\
**Created:** [16 April 2020 06:14 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922 "2020-04-16T06:14:54Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [16 April 2020 06:14 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/1 "2020-04-16T06:14:54Z")

</div>

I am very new to Node-RED and working on my first flow to send CSV data to MSSQL database tables. I have read the data from CSV files, but unable to solve issues to send the data to MSSQL server. I am getting error that cannot connect to the MSSQL server. How can I send CSV data to MSSQL database?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/b/9bf85dd74436bd00fd131b08e852a02e0c0ab66a.png)  
This is my flow. Please let me know if I am doing it correctly. Thanks

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [16 April 2020 07:05 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/2 "2020-04-16T07:05:14Z")

</div>

Please read [the documentation](https://flows.nodered.org/node/node-red-contrib-mssql-plus)  
The mssql node uses queries to insert/delete/select data.

---

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [20 April 2020 05:35 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/3 "2020-04-20T05:35:46Z")

</div>

I went through the documentation and tried writing a query to mssql node, but I am still facing issues and errors. I am very new to this platform so can I help me to know how can I proceed

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/8/e8ff380b95771790b5e57e80df04a112b5a71c2b.png).  
Here is the snap shot of my workflow, query( I have written it using very basic knowledge of sql) and errors I am getting.  
Please let me know how can I proceed in this case. Thnaks.

---

<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:** [20 April 2020 08:20 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/4 "2020-04-20T08:20:19Z")

</div>

Well for starters your SQL is complete nonsense.

Start simple.

1. Use a SQL tool (like microsoft sql server management studio) and get a simple SELECT working

2. Copy there working SQL to an MSSQL node and ensure it works

3. Try building the dynamic where clause using {{{mustache}}} syntax (and read the help info and examples provided in the side bar info panel for the MSSQL-PLUS node)

Edit...  
The connection is probably not setup correctly either (that's why you're getting connection errors)

Try this simple query

```auto
select getdate() as 'servertime'

```

⬆ that query should work on any MS SQL server without having to know what databases or tables it has. Use that as a simple test query to ensure your connection settings are correct.

---

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [23 April 2020 06:02 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/5 "2020-04-23T06:02:35Z")

</div>

Hello, Thanks a lot for the help and information. I tried working with the small functions and now I did able to successfully select the data, tried dynamic values. But I am still not able to understand the mustache format and how to use it.  
In MSSQL, I successfully copied the CSV data to my table by writing queries in MSSQL but when I am using the same in node red, I am getting some syntax errors.  
The query in my MSSQL is as follows-  
BULK INSERT dbo.TEST\_City\_Data  
FROM 'E:\TDK\_Sensors\_AG\_co\_KG\US-1\citiesCopy.csv'  
WITH  
(  
FIRSTROW = 2,  
FIELDTERMINATOR = ',', --CSV field delimiter  
ROWTERMINATOR = '\n', --Use to shift the control to next row  
TABLOCK

)

This is working perfect in MSSQL .  
But while transferring the same into node red MSSQL node, I am getting errors. How should i proceed?  
Thanks a lot

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 April 2020 06:09 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/6 "2020-04-23T06:09:07Z")

</div>

> I am getting errors.

Start with showing us the errors

---

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [23 April 2020 06:16 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/7 "2020-04-23T06:16:26Z")

</div>

Okay. I used the same SQL queries from MSSQL to NODE RED. Can you tell me how to transform them to mustache format. I am confused.  
These are the errors which I am getting.  
I guess I am messing somewhere with the syntax.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/e/aee3f5f4acb28d19dffa4e22cb90d59430427c8c.png)  
Please have a look and let me know.  
Thanks.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 April 2020 06:30 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/8 "2020-04-23T06:30:15Z")

</div>

Looking at this line:

`FROM 'E:\TDK_Sensors_AG_co_KG\US-1\citiesCopy.csv';`

See the `;` this is a statement terminator, try removing it.  
Question will remain if the node supports local file reading like this.

---

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [23 April 2020 06:39 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/9 "2020-04-23T06:39:50Z")

</div>

I tried it. I think there is some problem with the syntax. Can you help me which how to modify the syntax for Node red. I am unable to find any good documentation for it as well.

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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 April 2020 06:42 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/10 "2020-04-23T06:42:18Z")

</div>

Yes, you use the mustache format for literal values.

I can imagine it to be something like:

```auto
WITH FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

```

---

<div class="post-metadata">

**Author:** ![red\_arrowhead](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@red\_arrowhead](https://discourse.nodered.org/u/red_arrowhead)\
**Post date:** [23 April 2020 06:53 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/11 "2020-04-23T06:53:35Z")

</div>

I tried changing it, but still it has some issue.

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

So, I do not have to use any mustache format here?  
it is used for literal values means for specific values?

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [23 April 2020 07:17 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/12 "2020-04-23T07:17:53Z")

</div>

Enclose the WITH parameters with `( )`

```auto
WITH (
FIRSTROW = ...
...
)

```

---

<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:** [23 April 2020 09:58 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/13 "2020-04-23T09:58:02Z")

</div>

As @bakman2 states, your query is incorrect.

**FOREWORD: I am using the updated version called MSSQL-PLUS...**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/8/7844712241a7aecadf0e0c9386d7e6fcf190bf13.png)

- The Original MSSQL node has issues & short commings
  - _for example, it doesnt show you the final rendered query (so you cant evaluate what happened to your mustache), cant do multiple queries, gets confused when there is more than one connection, doesnt have extensive built in help. etc, etc, etc_

- If you are not using this, then I recommend the following...
  - FIRST uninstall MSSQL
  - THEN install the MSSQL-PLUS version
  - THEN restart node-red

### Mustache

As for mustache - its explained in the help info & examples are provided...

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

### Here is a working example...

#### change node (to set `msg.filename`, `msg.FIRSTROW` etc)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/4/a448fe2165911df20a3730a1e15f6b64144f2017.png)

#### the query (with {{{mustache}}})...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cd07393a0dc4264ee65785203eb9b852ef09cbe8.png)

#### the result....

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

---

<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:** [22 June 2020 10:07 UTC](https://discourse.nodered.org/t/sending-csv-data-to-mssql-server/24922/14 "2020-06-22T10:07:26Z")

</div>

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