SQLite3 import from CSV

General
1

Does someone here have experience with importing data from a CSV file into SQLite with node-red-node-sqlite ?

I tried so far:

COPY syslog FROM 'E:\Path\to\syslog.csv' DELIMITER ',' CSV HEADER;

and

IMPORT 'E:\Path\to\syslog.csv' syslog

Both COPY and IMPORT throw a syntax error.

Importing the CSV file with the SQLite Browser works without any issues.

2

the node-red node is designed to work with data in node-red

you would need to import the data from the file into node-red using a file in node and then use the sqlite node to add it to the sqlite db

3

If you are just doing it once, a quick google search finds this: Import a CSV File Into an SQLite Table

4

If you want to do it in node-red you could use an exec node to run the sqlite commands to do it.

5

Hey thx for the quick reply!

my setup looks like this:

sqlite3

I get a txt file via http, clean it up and save it to csv. I was just thinking about getting the payload before writing it to file and connect it to the SQLite node. Now I have to figure out how to do that :slight_smile:

And yes, I found this tutorial on how to do it through the terminal - but I need something automatic.

6

To be honest, it looks like you really don't need the CSV file? Do you want to just push the http data into SQLite because you can do that directly(ish) in Node-RED.

There is a node that uses ALAsql that ought to be able to do this for you as well but unfortunately, although the underlying ALAsql module supports SQLite, I don't think that contrib-alasql author has included that feature.

7

Hey,

thank you - alaSQL does look interesting. Documentation seems a bit rare - I will have to play with it a bit to see if I can make it work. I already have it reading the CSV file and creating/reading tables. It might be the solution I was looking for.

And yes, you are right - I would not need the CSV for SQLite. I need the option to backup to CSV and since I already had that file, I hoped that I could use it.

8

Yes, the docs for that have never been brilliant.

For what you want to do, you would be much better off splitting the flow so that one branch writes the CSV and the other writes to the db. If you need to extract a table from the HTML, the html node will do that for you (or if it is too complex, try one of the cheerio nodes). The csv node followed by a file out node should create the CSV for you.