Load data Infile @variable into infile error

Question

I want to use a variable as the file name in Load data Infile. I run a below code:

Set @d1 = 'C:/Users/name/Desktop/MySQL/1/';
Set @d2 = concat( @d1, '20130114.txt');
load data local infile  @d2  into table Avaya_test (Agent_Name, Login_ID,ACD_Time);

Unfortunately after running, there is a error with the commment like below: "Error Code: 1064. You have an error in your SQL syntax ......"

Variable "@D2" is underlined in this code so it means that this error is caused by this variable.

Can you help me how to define correctly a variable of file name in LOAD DATA @variable infile ?

Thank you.

score 9 · Answer 1 · answered Aug 21 '13 at 21:00

A citation from MySQL documentation:

The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The character_set_filesystem system variable controls the interpretation of the file name.

That means that it can not be a parameter of a prepared statement, stored procedure, or anything "server-side". The string/path evaluation must be done client side.

score 5 · Answer 2 · edited Aug 13 '15 at 12:03

5

In general, it is not possible directly but you can use a temporary file.

The procedure is similar to using a combination PREPARE-EXECUTE. Notice that you cannot use PREPARE with LOAD DATA INFILE, for this reason you require a temporary file.

Here's an example of how to read a file with today's date:

SET @sys_date = CURRENT_DATE();

SET @trg_file = CONCAT("LOAD DATA INFILE '/home/data/file-",@sys_date, "' INTO TABLE new_data FIELDS TERMINATED BY ' ';");

SELECT @trg_file INTO OUTFILE '/tmp/tmp_script.sql';

SOURCE /tmp/tmp_script.sql;

WARNING: You cannot overwrite files with mysql, for this reason the temporary file must not exist. It is a serious problem if you want to automatize the previous example.

edited Aug 13 '15 at 12:03

Meenesh Jain

2,532
2
19
29

answered Aug 13 '15 at 11:46

Fuguilla

51
1
4

If you need to do anything with file processing in connection to LOAD DATA INFILE, I've had awesome results when I pair it with a Bash script. Here's how it works: 1) Have Bash copy your dated data file into a temporary work file held in a "unprocessed" folder. Then call the sql that has the load data infile process. After the sql runs, you can have Bash delete the work file and move the But you CAN – Annatar Sep 30 '16 at 21:12

score 2 · Answer 3 · edited May 23 '17 at 12:32

2

Unfortunately, this does not seem to be possible in mysql.

In addition to Binary Alchemist's answer, it's also not possible with prepaired statements either, as it is not on this list: SQL Syntax Allowed in Prepared Statements

You could use soemthing external to generate your load data infile statement, and then run the sql. For instance you could create it in Excel.

edited May 23 '17 at 12:32

Community

1
1

answered Apr 14 '14 at 01:48

joyleak

58
7

score 1 · Answer 4 · answered Jun 11 '13 at 04:10

1

This sysntax can't work since the variables are interpreted by the server, and the file is read by the mysql client. Therefore for the client @d2 is an illegal file name.

answered Jun 11 '13 at 04:10

tavvit

259
3
6

score 0 · Answer 5 · answered Nov 30 '17 at 11:46

My solution works on Linux/Mac/[Windows with bash] (e.g. cygwin)

Create a template with the load SQL, e.g. load.tpl -- note the %FILENAME% placeholder

LOAD DATA LOCAL INFILE '/path/to/data/%FILENAME%'
INTO TABLE Avaya_test ( Agent_Name, Login_ID, ACD_Time );

Then run this command:

for n in `ls *.txt`; do echo $n; sed -e 's/%FILENAME%/'$n'/g' load.tpl | mysql -u databaseUser databaseName; done

Load data Infile @variable into infile error

5 Answers5

Linked

Related