Can I concatenate a hard-coded string with a column in a MySQL LOAD DATA INFILE?

Question

I have a file with some MySQL commands for loading a single column into a database table:

TRUNCATE TABLE datamap;

LOAD DATA LOCAL INFILE 'datamap.txt'
INTO TABLE datamap
Fields terminated by  ','
enclosed by    '"'
escaped by    '^'
Lines terminated by '\r\n'
(datapath);

The datapath file contains a list of files and their directories, i.e. x:\files\filename1.txt

All files are now in a single directory and I would like to hard-code the path into the command above, such that I can load only the filename but have the full path saved to the column in my table. I am aware that I could add an additional UPDATE statement above to update every column in the table after I've loaded it but that doesn't seem very efficient.

Does the LOAD DATA INFILE command support concatenating fixed strings with data being read in from a file?

Answer 1

TRUNCATE TABLE datamap;

LOAD DATA LOCAL INFILE 'datamap.txt'
INTO TABLE datamap
Fields terminated by  ','
enclosed by    '"'
escaped by    '^'
Lines terminated by '\r\n'
(col1, col2, @my_variable)
SET col3 = CONCAT('x:\files\', @my_variable);