# How to refresh my sqlite 3 database through node red

**URL:** <https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133>\
**Category:** General\
**Created:** [21 September 2020 05:44 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133 "2020-09-21T05:44:00Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 12:37 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/21 "2020-09-21T12:37:45Z")

</div>

Yes sir , i want to export my sqlite into csv file how can i do that

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 12:48 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/22 "2020-09-21T12:48:18Z")

</div>

@shipra as I've suggested in other threads... break it down into steps.

You have data in a an sqlite database, you want it in a file. What are the steps your flow needs to take to do that? Write down that list of steps, then think about how you can complete each step.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [21 September 2020 12:51 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/23 "2020-09-21T12:51:30Z")

</div>

> [@shipra](#):
>
> i want to export my sqlite into csv file how can i do that

I thought you wanted to update the view in the database browser.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 13:36 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/24 "2020-09-21T13:36:41Z")

</div>

i have tried this please check and suggest

 ![s](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/a/7ab735e68a09aa75cc1cc4a8328e92a3fde7804e.png)

I am getting this error

```auto
9/21/2020, 6:18:45 PMnode: Writing into .csv filefunction : (error)
"Function tried to send a message of type string"

```

My csv function looks like this

```auto
if (msg.payload=="Refresh_DataBase")
{
   var data= "SrNo"+ "," + "Date_and_Time_India"+ ","+"Date_and_Time_UK"+ ","+"Event_Type"+ "," +"Event_Description"+ ","+"Person_ID"+ ","+"Person_Name_Description"+ ","+"Fob_Tag_ID"+ ","+"Trade_ID "+ ","+"Door_ID"+ ","+"Door_Description"+ ","+"Status"
    msg.payload1=data;
   
}

return msg.payload1;

```

please suggest where i am doing wrong

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 13:37 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/25 "2020-09-21T13:37:07Z")

</div>

ye sir i want how can i expost my sqlite into csv file

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 13:38 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/26 "2020-09-21T13:38:37Z")

</div>

Where is the list of steps you think the code should be taking? It is impossible to look at a flow and know what you are thinking.

The error is exactly what it says - your Function code is returning `msg.payload1` which is a String. It should be returning `msg`.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 13:47 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/27 "2020-09-21T13:47:00Z")

</div>

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

Sir my steps are like this  
User enter Fob id-\>if valid then insert a row in my DB(image attached)-\>store into a csv file  
user enter tag id-\>////////same as above///////////  
user enter tag+fob id -\>///////same as above///////////

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 13:56 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/28 "2020-09-21T13:56:54Z")

</div>

So every time you update a row in the sqlite database, you also want to write the whole database out as an CSV file?

So lets focus on the steps to get the data out of the database and into the file. What steps does your flow need to take to do that? And which part of those steps do you need help on?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:00 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/29 "2020-09-21T14:00:37Z")

</div>

Sir i tried this and getting some output like this

 ![99](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/b/abddfd726e74a4b0cf42b70719afe090c41c2f61.png)

I want to upload my whole DB into csv which is not coming why?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:01 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/30 "2020-09-21T14:01:08Z")

</div>

> [@knolleary](#):
>
> you also want to write the whole database out as an CSV file?

yes please tell how can i do that?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 14:10 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/31 "2020-09-21T14:10:58Z")

</div>

I am very patiently trying to get _you_ to figure it out.

Yet again you have posted a screenshot and asked me what it isn't working. You have not provided _any_ information about what that screenshot shows or what is not working about it.

You have also not answered the questions I asked. So let me try to spell it out. You need a flow that can:

1. get all the data from the database
2. format that data appropriately
3. write it to a file.

Let us focus on these steps. Here are some questions for you to answer.

1. what node do you use to query the database?
2. what SQL query do you need to use to get the data from the database?
3. how do you pass that query to the database node?
4. What node can you use to look at the result of the database query in order to understand the structure of the data you are getting back?
5. What node in the palette can you use to generate CSV (hint: not the Function node...)

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:24 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/32 "2020-09-21T14:24:41Z")

</div>

> [@knolleary](#):
>
> - get all the data from the database
> - format that data appropriately
> - write it to a file.

yes sir i want this.

> [@knolleary](#):
>
> what node do you use to query the database?

SQLite3

> [@knolleary](#):
>
> what SQL query do you need to use to get the data from the database?

I replaced my older query and wrote this  
let newMsg1={};

newMsg1.topic= `SELECT * FROM NCS_DATABASE2`;  
return newMsg1;

but not getting the output in csv file

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:28 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/33 "2020-09-21T14:28:15Z")

</div>

> [@knolleary](#):
>
> What node in the palette can you use to generate CSV (hint: not the Function node...)

ok then how can i do this?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 14:31 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/34 "2020-09-21T14:31:06Z")

</div>

> [@shipra](#):
>
> then how can i do this?

I give up. Use the CSV node. If you had spent 30 seconds looking you would have found it.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:31 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/35 "2020-09-21T14:31:39Z")

</div>

![9](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/1/4145f9f280fde3340d14ecc98fbcbbe089ab8363.png)

Sir i get my DB into csv but one issue is coming new SrNo is not coming in a new line why?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 14:33 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/36 "2020-09-21T14:33:16Z")

</div>

> [@shipra](#):
>
> why?

You haven't shared any information about your flow. I can no longer help you. Good luck.

I have asked you repeatedly to provide enough information to for us to help you. Given you seem unwilling to learn or to listen to the advice you are given, I cannot spend more of my free time on this.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:40 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/37 "2020-09-21T14:40:02Z")

</div>

Sir, sorry but duw to my english maybe you feel like i am not listening or not obeying you but that not the case i am properly following you .  
my flow  
Database i want in csv file.  
the things i tried  
I have taken one refresh pub node whenever i publish on this node by older stored value whatever will be in DB previously it should get stored in csv file format  
For this i have taken

1. REFRESH PUB node so that it should trigger my csv file generation
2. func node where i have written SELECT \* FROM NCS\_DATABASE2;  
3)csv file node where i have given my csv file directory.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:44 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/38 "2020-09-21T14:44:46Z")

</div>

I am getting like this

 ![l](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/3/230d5442c11bec488a633b91997274b1b62714eb.png)

It is not looking good i mean headers and data all are getting in one row ,So kindly tell how can i do it in proper manner

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [21 September 2020 14:45 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/39 "2020-09-21T14:45:04Z")

</div>

So you are selecting all the data from the database. Good.

I am guessing (because you have not shown us), that you think you can just write the result of the database query straight to a file and expect it to be in the right format.

That is not how it works.

Look again at the list of 3 steps I described you having to do:

1. get all the data from the database
2. format that data appropriately
3. write it to a file.

At the moment it sounds like you are doing 1 and 3 - but you are not doing 2.

I have suggested you look at the CSV node in the palette. This can be used to take the result of the database query and generate valid CSV for you. You say you are listening to me, but I see no evidence that you have looked at the CSV node yet.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [21 September 2020 14:50 UTC](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133/40 "2020-09-21T14:50:50Z")

</div>

![89](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d3b2abcd9ed4b3cc8d46f520457067feec931d08.png) Sir ,you are genius .thank you so much and sorry for the trouble.now I am getting the output in correct format

[Previous page](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133.md?page=1)

[Next page](https://discourse.nodered.org/t/how-to-refresh-my-sqlite-3-database-through-node-red/33133.md?page=3)
