3

I have a few Excel wordbooks which will be generating dozens of csv files which need to be imported in their corresponding receiving SQL tables every 5 minutes. I wonder which would be the elegant way to do this? Can SQL script be used to import multiple csv files into their respective tables in one batch run (we would upload all csv files in one ftp run)? What you would recommend?

How error–proof is this setup? We need this for a production environment – need to connect local Excel models with interactive charts on the web - fAST.

Just to clarify we will be using wpdatatables plugin to draw charts using the imported csv data on WordPress - in combination with wpbakey you can create a responsive dashboard in a matter of minute using any result from your Excel models of ANY COMPLEXITY - and without the need to pay thousands for any blackbox dashboard solutions.

Won't constant BULK import of the csv tables corrupt the database? I assume you can run multiple sql commands in a stream?

Alexandr
  • 243
  • 2
  • 3
  • 15
Dave
  • 45
  • 1
  • 6
  • Perhaps you could check this question : https://stackoverflow.com/questions/14330314/bulk-insert-in-mysql – Random User Nov 01 '20 at 00:36
  • thank you - can this code be run to import multiple files into their respective tables? and will not constant import of the csv tables corrupt the database? if done every 5 minutes? – Dave Nov 01 '20 at 07:12
  • For importing multiple files into their respective tables, you can run the same command, multiple times, just change the file name and table name. Regarding database integrity, I am not knowledgable enough to give a full answer. However, I suspect deleting entire tables frequently would inflate database logs A LOT. – Random User Nov 01 '20 at 12:22
  • thank you very much - can we just paste a stream of commands and run them all at the same time? the commands woudl just replace rows with new data - no tables will be deleted - do you still think there an be issues with database integrity if tables are updated with csv files every 5 minutes? – Dave Nov 01 '20 at 17:42
  • Assuming there is no referential integrity constraint between the aforementioned tables, I think there is no problem running the commands at the same time. Regarding database integrity, you'd need at least to use the TRUNCATE command to flush the tables, instead of DELETE, so you don't bloat your logs with each file. I am not qualified enough to say if this approach is sane and safe - you might want to create another question to validate wether truncating table followed by LOAD DATA INFILE is the right approach. – Random User Nov 01 '20 at 18:00
  • I am using instructions as per this page: – Dave Nov 02 '20 at 07:39

0 Answers0