Adding a string to a field during import with LOAD DATA LOCAL INFILE

Question

Im importing a csv file to a mysql table with the following query;

"LOAD DATA INFILE 'myfielname.csv'
INTO table customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r'
IGNORE 3 LINES
(sales,regional,accounts)
";

Is there any way to insert a string of characters before a field that is to be imported?

For example: The field 'sales' above refers to account id numbers, which are being used in the application. Id like to append a URL before account number during import so the final record in the table will be as follows:

String I want to come before 'sales', but within the same record: http://www.url.com?id=

If a given sales id was 1234 the final record in the table would be http://www.url.com?id=1234

Thanks in advance for your help.

Answer 1

Try someting like this

LOAD DATA LOCAL INFILE 'C:/test.csv' 
INTO TABLE test.test1 
FIELDS TERMINATED BY ';'
(@test1col,@test2col) 
set test1col=CONCAT('http://url.com?id=',@test1col),test2col=@test2col;

The test csv has 2 columns. I created a test table like this

CREATE TABLE `test1` (
  `test1col` varchar(200) DEFAULT NULL,
  `test2col` varchar(2000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You could try immediatley with your own, just make sure you name the columns correctly!

Give it a try it worked for me.