# Insert csv. file to SQLite

**URL:** <https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216>\
**Category:** General\
**Created:** [7 March 2021 20:06 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216 "2021-03-07T20:06:20Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [7 March 2021 20:06 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/1 "2021-03-07T20:06:20Z")

</div>

Hi,  
i have many csv. file that i want to send them to a database(i used sqlite hier). I thought that i can use function node after csv node, but i became error that i cannot slove them. I would be happy when i get some advise?  
THX a lot.  
Samira

 ![FLOW3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/9/a9c634f6a4df6fa4910223ee8c8e6a6916dc6998.png)

```auto
[{"id":"cad1ac8d.36dc","type":"function","z":"38d2e76d.d8f4d8","name":"insert ","func":"var payload=msg.payload;\n\n\n\nfor(i=0; i<payload.length ; i++){\n var Data= payload[i][\"Data\"];\n var Time= payload[i][\"Time\"];\n var Temperature= payload[i][\"Temperature\"];\n \n}\n\n var newMsg = {\n \"topic\": \"INSERT INTO TOPI VALUES ( \" + msg.payload + \",\" + Data + \", \" + Time + \", \" + Temperature + \")\"\n}\nreturn newMsg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":770,"y":440,"wires":[["25b70208.ec835e","788cb26f.625b9c"]]}]

```

---

<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:** [7 March 2021 20:28 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/2 "2021-03-07T20:28:49Z")

</div>

you are getting [object] because you are not accessing the correct item

Put `node.warn( [Data, Time, Temperature] )` in your loop - see what the values look like

slightly updated version of your code

```javascript
var payload=msg.payload;

for(let i=0; i<payload.length ; i++){
   var Data= payload[i]["Data"]; //correct this - get the sub property!
   var Time= payload[i]["Time"]; //correct this - get the sub property!
   var Temperature= payload[i]["Temperature"]; //correct this - get the sub property!
   node.warn( [Data, Time, Temperature] ); //check debug sidebar
}

var newMsg = {
   "topic": "INSERT INTO TOPI VALUES ( ? , ? , ?)",
   "payload": [Data, Time, Temperature]
}
return newMsg;

```

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [8 March 2021 11:47 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/4 "2021-03-08T11:47:46Z")

</div>

Hi,  
thank you Steve for your helpful answer. I have some Questions now. I have 3 colums in my csv. file and i want to have Index column in my table in DB. I tried to set Data(date of every row of my file) as primary key but get it "Error: SQLITE\_CONSTRAINT: UNIQUE constraint failed: TOPI.DATA" (TOPI is name of table and DATA is first column of table). As i understand right, i can select date as INT or string OR i should change my type of date?  
I read [SQLite UNIQUE Constraint](https://www.sqlitetutorial.net/sqlite-unique-constraint) too but it doesent work.  
I know, i am wrong somewhere but i cannot find it.

 ![error2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/48f73a8e60a3febc631eefd98023c326fcf9d71f.png)

I should send updated codes.

```auto
[{"id":"cad1ac8d.36dc","type":"function","z":"38d2e76d.d8f4d8","name":"insert ","func":"var payload=msg.payload;\n\n\n\nfor(i=0; i<payload.length ; i++){\n var Data= payload[i][\"Data\"];\n var Time= payload[i][\"Time\"];\n var Temperature= payload[i][\"Temperature\"];\n node.warn( [Data, Time, Temperature] ); //check debug sidebar\n}\n\n var newMsg = {\n\n//\"topic\": \"INSERT INTO TOPI VALUES ( \" + Data + \", \" + Time + \", \" + Temperature + \")\",\n \"topic\": \"INSERT INTO TOPI VALUES ( ?,?,?)\",\n \"payload\": [Data, Time, Temperature]\n \n }\n\n\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":770,"y":420,"wires":[["25b70208.ec835e","788cb26f.625b9c"]]}]

```

TNX  
Samira

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 10:21 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/5 "2021-03-09T10:21:34Z")

</div>

Hi ,  
i have solved the error "unique constraint" but now any data come to my sqlite. I think , i should change maybe my code but i am not sure.  
i am thankful with advise.  
tnx  
Samira

 ![error5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/86c276b4c6f7bdcb42b655462c84ef2b1cd42675.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 March 2021 10:56 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/6 "2021-03-09T10:56:45Z")

</div>

> [@Samira05](#):
>
> i have solved the error "unique constraint" but now any data come to my sqlite. I think , i should change maybe my code but i am not sure.

What do you mean by `now any data come to my sqlite`?  
If you think you should change your code, then change it. remember, no one on the forum knows what your needs are.

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 11:44 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/7 "2021-03-09T11:44:57Z")

</div>

> [@zenofmud](#):
>
> What do you mean by `now any data come to my sqlite` ?

I mean, i cannot see my data in table. I see just :

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

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 11:48 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/8 "2021-03-09T11:48:49Z")

</div>

> [@zenofmud](#):
>
> remember, no one on the forum knows what your needs are.

Hi zenofmud,  
i clearly explaned in my first post what is my goal. If you have a question, you can simply ask and I will be happy to tell you again.

Best Regards  
Samira

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 March 2021 12:13 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/9 "2021-03-09T12:13:39Z")

</div>

How many msg's does your `function` node output? Put a `debug` node on the output of the `function` node to see.

If it is not what you want, ask yourself "Why?"

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [9 March 2021 12:28 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/10 "2021-03-09T12:28:14Z")

</div>

Hi,

The [syntax for Insert sql query](https://www.sqlitetutorial.net/sqlite-insert/) i think should be in the form

```auto
INSERT INTO table (column1,column2 ,..)
VALUES( value1,	value2 ,...);

```

The column names are missing from your query after the name of your table

Also the script only sends one msg .. it should send multiple messages while looping throught the converted csv data.

**Modified function code :**

```auto
var arr = msg.payload;

arr.forEach( row => {
    
    let Data = row.Data
    let Time = row.Time
    let Temperature = row.Temperature
    
    node.send( {
   "topic": "INSERT INTO TOPI (Data, Time, Temperature) VALUES (?, ?, ?)",
   "payload": [Data, Time, Temperature]
})
    
})

return null;

```

ps. you may need to add a Delay node after the Function as to not send to many data to fast to the db  
ps2. **backup you db if you have to as the above code couldnt be tested since we dont use the same db**

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 13:59 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/11 "2021-03-09T13:59:31Z")

</div>

> [@UnborN](#):
>
> Also the script only sends one msg .. it should send multiple messages while looping throught the converted csv data.

Hi UnborN,  
thank you for your response . I build new table and database and wrote your codes, it showed 3 messages that i wanted but there is nothing in table same before.  
I have many csv. file that everyone hat different rows , also i need for loop that start (0 till length of my payload).  
I have another question, Time ist als primary key in my table. and i get always the "Error: SQLITE\_CONSTRAINT: UNIQUE constraint failed: STAR.TIME" .

tnx  
Samira

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 14:19 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/12 "2021-03-09T14:19:23Z")

</div>

Hi,  
i get 3 outputs now and i ask myself "warum i can not get data in table". 😉

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [9 March 2021 14:51 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/13 "2021-03-09T14:51:23Z")

</div>

.. there is still something wrong with the syntax ..  
If you select the sqlite node and read the Help notes on the sidepanel .. it describes how to properly send the values as parameters (which is the safest way, since it sanitizes the values and protects from sql injection).

Change the configuration of your Sqlite node to

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

and in the [forEach](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/forEach) loop (that **does** loop through the whole length of your array)

node.send the `"params" : { $Data:Data, $Time:Time, $Temperature:Temperature }`  
instead of `topic` and `payload`

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 March 2021 15:23 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/14 "2021-03-09T15:23:14Z")

</div>

Please provide your current flow so we can see what you have done.

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [9 March 2021 16:33 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/15 "2021-03-09T16:33:33Z")

</div>

Hi UnborN,  
i thank you so much for your time.  
i did it what you said but stay errors.  
i try to find result more and say you if it works.  
tnx  
Samira

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [17 March 2021 15:22 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/16 "2021-03-17T15:22:03Z")

</div>

> [@UnborN](#):
>
> `arr.forEach( row => {`

Hello zenofmud,  
in last week i could not spend enough time on programm but i tried to reach the Result. I tried to reach answer with arr.forEach() but it still on some errors and i prefer to continue with topic and payload not with params. my last flow is:

```auto
[{"id":"e2c78e0e.28abc","type":"function","z":"581344b8.85f0cc","name":"insert ","func":"var payload=msg.payload;\n\n for(i=0; i<payload.length ; i++){\n var Data= payload[i][\"Data\"];\n var Time= payload[i][\"Time\"];\n var Temperature= payload[i][\"Temperature\"];\n node.warn( [Data, Time, Temperature] ); //check debug sidebar\n \nvar newMsg = {\n\n // \"topic\": \"INSERT INTO STAR VALUES ( \" + Data + \" , \" + Time + \" , \" + Temperature + \")\",\n //\"topic\": \"INSERT INTO DATEI VALUES ( ? , ? , ? )\",\n // \"topic\":\"INSERT or IGNORE into STAR VALUES (? , ? ,? )\",\n //\"payload\": [Data, Time, Temperature]\n \"topic\": \"INSERT INTO STAR (Data, Time, Temperature) VALUES (?, ?, ?)\",\n \"payload\": [Data, Time, Temperature]\n }\n}\nreturn newMsg; \n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":690,"y":360,"wires":[["dfbbaacc.149558","79159544.a872ac","30da63d.19e679c"]]}]

```

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [17 March 2021 16:29 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/17 "2021-03-17T16:29:08Z")

</div>

```auto
var payload = msg.payload;

for(i=0; i < payload.length; i++) {
    
    var Data = payload[i]["Data"];
    var Time = payload[i]["Time"];
    var Temperature = payload[i]["Temperature"];
    node.warn( [Data, Time, Temperature] ); //check debug sidebar
   
    node.send({"topic": `INSERT INTO STAR ('Data', 'Time', 'Temperature') VALUES ('${Data}', '${Time}', ${Temperature})`})
}

```

---

<div class="post-metadata">

**Author:** ![Samira05](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Samira05](https://discourse.nodered.org/u/Samira05)\
**Post date:** [18 March 2021 13:40 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/18 "2021-03-18T13:40:39Z")

</div>

> [@UnborN](#):
>
> `node.send({"topic": `INSERT INTO STAR ('Data', 'Time', 'Temperature') VALUES ('${Data}', '${Time}', ${Temperature})`})`

Hi UnborN,  
tnx for your answer.the problem is error of UNIQUE constraint. at first i set Time as primary key but i had this error. I tried to change my primary key and i add one column(called Numik) as primary key. but it still same error.

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

```auto
var payload=msg.payload;

for(i=0; i<payload.length ; i++){
   var Numik= payload[i]["Numik"];
   var Data= payload[i]["Data"];
   var Time= payload[i]["Time"];
   var Temperature= payload[i]["Temperature"];
   node.warn( [Numik, Data, Time, Temperature] ); //check debug sidebar

    var x= node.send({"topic": `INSERT INTO BETAA ('Numik', 'Data', 'Time', 'Temperature') VALUES ('${Numik}','${Data}', '${Time}', ${Temperature})`})
}
return x;

```

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [18 March 2021 19:02 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/19 "2021-03-18T19:02:09Z")

</div>

> [@Samira05](#):
>
> error of UNIQUE constraint

ok .. so you know what the problem is. You setup your db table in such a way that the primary key (that must be unique) repeats itself and that is not allowed. Setting the Date as primary key will not work since the date repeats itself several times every day for your inserted rows. And in the case of the Time as primary key .. the time will repeat itself the next day.

One solution is to set a field in the db called (lets say) **Datetime** a combination of Date and Time fields from your CSV file .. This surely will be unique and can be set as Primary key. Once you make those changes to your table columns, a variation of the function could be ..

```auto
var payload = msg.payload;

for(i=0; i < payload.length; i++) {
    
    var Data = payload[i]["Data"];
    var Time = payload[i]["Time"];
    var Temperature = payload[i]["Temperature"];
    node.warn( [Data, Time, Temperature] ); //check debug sidebar
   
    node.send({"topic": `INSERT INTO STAR ('Datatime', 'Temperature') VALUES ('${Data} ${Time}', ${Temperature})`})
}

```

ps. As far as the function code goes .. you dont need to save `node.send()` into a variable and then returning it outside the loop. That is not how i shared the code. We used node.send() in this case instead of a `return` because we wanted to send many msgs in the loop.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 March 2021 22:13 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/20 "2021-03-18T22:13:42Z")

</div>

You might want to read this [Understanding the SQLite AUTOINCREMENT](https://www.sqlitetutorial.net/sqlite-autoincrement/) - sqlite has a unique rowid for each row entered

---

<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:** [1 April 2021 22:14 UTC](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/21 "2021-04-01T22:14:30Z")

</div>

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