MySQL - Load Data Infile with variable path

Question

I'm having a problem with setting a variable path for putting data into my table. This is how i build my path:

SET @path1 = CONCAT('C:/Projekte/Metrics/DXL_CSV_EXPORT_DATA/', YEAR(NOW()), '_',    MONTH(NOW()), '_', DAY(NOW()), '%', '/_','BeMiko/');`

Every day another Folder is created on the server. I want to automatically import the information from the .csv files from inside these folders each day.

I import data from files with:

LOAD DATA INFILE 
path...
IGNORE INTO TABLE table1 FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '<*line_end*>\r\n' IGNORE 1 ROWS;

How do i use the string inside my @path1 variable as path? Or if this is not possible: Are there other ways to solve this problem?

You should be able to generate the SQL statement on-the-fly. Here is an example. — RandomSeed
– RandomSeed, Commented Aug 19, 2014 at 11:00
This could work, but somehow i always get an error if i try to execute the procedure. The error says: This command is not supported in the prepared statement protocol yet (Error Code 1295). How can i fix this? — Officer Bacon
– Officer Bacon, Commented Aug 19, 2014 at 12:22
Oops. Then I don't know. Do you have shell access? Then you could mysql -e "LOAD DATA INFILE $var ..." — RandomSeed
– RandomSeed, Commented Aug 19, 2014 at 12:23
Working in a shell isn't what i should do right now. I need to create a procedure, which gets executed once a day (event..). — Officer Bacon
– Officer Bacon, Commented Aug 19, 2014 at 12:32

Bogdan Mircea Stanciu · Accepted Answer · 2016-12-28 10:01:59Z

2

Variables can't be used to substitute paths in the LOAD DATA INFILE statement.

Also:

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

as seen in the mysql manual

answered Dec 28, 2016 at 10:01

Bogdan Mircea Stanciu

2221 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Load Data Infile with variable path

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related