Hello All,
Any idea about how import a CSV file to MySQL with node-red ?
My apologies in advance, but I done know how start....
Thanks,
Actually this pretty easy. Grab line from CSV and the headers line if exists then do a SQL insert. msg.topic is how you qualify the query for insert.
So... say CSV file is...
A,B,C
1,2,3
Where ABC is column headings in CSV, 123 is the data in CSV (of course more lines could exist).
So the SQL insert query would be... Say the table name is 'Test'...
msg.topic = "INSERT INTO Test (A,B,C) VALUES ('1','2','3');";
I am assuming, that you know how to create SQL database, table in DB, etc. Also that you know how the SQL nodes work in NR. If that is in error, just let us know.
Many thanks by the quick answer @Nodi.Rubrum,
Yes, My table is ready and I know how works node red (intermedium level, haha) .
After read your comment isnt clear for me...
How I create the insert query ? I mean, I have a CSV file that I suppouse that I have to read it with the (file in) node and I get something like:
Imagine that I have 500 lines....
I normaly work with mysql and node red with insert , update queries , but I dont know how manage the csv...
Thanks again!
Ok, so what you need is a loop right? And to make it easy, I would do one insert per loop or line of data. The easy way to do an insert query initially, is just to create the entire query string in msg.topic and send it to the SQL node. Now that I have said that, I am sure others will mention that SQL injection risk is applicable when you do it this way. But for initial learning this is the simplest case.
Here is an example of what I am talking about for a basic insert query...
[{"id":"2699da.dc015626","type":"function","z":"1b6e3ef2.248181","name":"Insert","func":"const AUDIT = 'audit';\n\nconst theVersion = flow.get(`${AUDIT}.version`),\n theTime = new Date().getTime();\n theContent = '?',\n theSum = '?',\n theLabel = flow.get(`${AUDIT}.label`);\n\nmsg.topic = `INSERT INTO Flow (Label,Version,Time,Content,Sum) VALUES ('${theLabel}','${theVersion}','${theTime}','${theContent}','${theSum}');`;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":380,"wires":[["896d97e.9a70168"]]},{"id":"896d97e.9a70168","type":"mysql","z":"1b6e3ef2.248181","mydb":"8e40ae24.4445","name":"Insert","x":710,"y":380,"wires":[["22aa22b7.8741fe"]]},{"id":"8e40ae24.4445","type":"MySQLdatabase","z":"","name":"Test (Data, Environment)","host":"[redacted]","port":"3306","db":"Environment","tz":"","charset":"UTF8"}]
The above is a function node, but you could do it in a change node as well of course. If you use a template node, you can parameterize the query string, which will avoid the SQL injection risk, but lets let that issue be something we deal with in the (near) future.
The CSV aspect is pretty easy, you can use a file-in node got pull the CSV file into a flow, use a CSV object to help parse it. This should give you an array of objects you can feed to a loop node, and just do the inserts.
[{"id":"c8cf6a8.3f24d98","type":"inject","z":"6bdba21.902255c","name":"Invoke","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":460,"y":300,"wires":[["2ca83f80.b7be6"]]},{"id":"2ca83f80.b7be6","type":"file in","z":"6bdba21.902255c","name":"File","filename":"/tmp/test.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":610,"y":300,"wires":[["e0eebb59.def8b8"]]},{"id":"e0eebb59.def8b8","type":"csv","z":"6bdba21.902255c","name":"CSV -> Java Script Object","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":810,"y":300,"wires":[["aa8a5f73.2c2d"]]},{"id":"aa8a5f73.2c2d","type":"debug","z":"6bdba21.902255c","name":"Data as JavaScript Array of Objects","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1100,"y":300,"wires":[]}]
I leave it to you to setup the loop. 
Rather than reading the whole file in and then looping through, would it be easier to read it one line at a time, so that each message contains one line of the file and the following nodes only have to handle inserting one record into the db at a time.
Or maybe doing a batch insert?
What is a loop node? Do you mean split node?
Split is (IMO) a far better option (built in node and avoids out of stack space do to misconfigured / endless loops)
and in effect either the file in node or the csv node can do the split for you as they can both output one row at a time if you wish.
1 Like
Of course there are several ways to crack this nut.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
