Parameterizing the file name in MYSQL LOAD DATA INFILE

Question

Is there a way to dynamically specify a file name in LOAD DATA INFILE? Can it be parameterized as, for example (the syntax may be incorrect) LOAD DATA INFILE '$ filename'?

Answer 1

Unfortunately, this function is not yet supported in MySQL and is currently listed as an error / function request # 39115 http://bugs.mysql.com/bug.php?id=39115

Answer 2

Or you can make a temporary copy of the file (BATCH example):

LOAD_DATA.bat

COPY %1 TempFileToLoad.csv mysql --user=myuser --password=mypass MyDatabase < ScriptLoadMyDatabase.sql DEL TempFileToLoad.csv 

SQL (for information):

ScriptLoadMyDatabase.sql

 load data infile 'TempFileToLoad.csv' IGNORE into table tLoad FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' lines terminated by '\r\n' IGNORE 1 LINES (@DateCrea, NomClient, PrenomClient, TypeMvt, @Montant, NumeroClient) set DateCrea = str_to_date(@DateCrea, '%Y-%m-%d'), Montant = (round(@Montant / 1000)*2) ; 

And finished to put the link to the BAT file in the Windows SendTo folder.

Answer 3

Quote from MySQL Documentation :

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be specified as a literal string.

This means that it cannot be a parameter of a prepared statement. But no one forbids string interpolation, while a statement is just a string in your PHP code.

Answer 4

If you ask if it can be used in a script; you can do something like this with php:

 <?php $mysqli = new mysqli("host", "user", "pwd", "db"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $sql = "CREATE TABLE number1 (id INT PRIMARY KEY auto_increment,data TEXT)"; if ($result = $mysqli->query($sql)) { } else { printf("<br>%s",$mysqli->error); } $host = $_SERVER['HTTP_HOST']; $uri = rtrim(dirname($_SERVER['PHP_SELF']), '/\\'); $filename = "data.csv"; $sql = "LOAD DATA LOCAL INFILE '$host$uri$filename' INTO TABLE number1"; if ($result = $mysqli->query($sql)) { } else { printf("<br>%s",$mysqli->error); } // Close the DB connection $mysqli->close(); exit; %> 

If the file is in the same folder as the script, just use $ filename a instead of $ host $ uri $ filename. I compiled this quickly from several scripts that I use, sorry if it doesn't work without debugging, but it should be pretty close. This requires mysqli.