Im loading a file straight into a database, to avoid having to process it using PHP. Because the file contains 2+ million rows.
However, I do still need to "process" the values before inserting.
The file consists of
Col 1 | Col 2
200 2505555
I can insert this just fine using
"LOAD DATA local INFILE '%s' INTO TABLE number_check FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\
' IGNORE 0 LINES "
However.... I want to insert it into only one column. Concatenated.
Col 1
2002505555
Getting this far would be great. And might be ok. But one step further would be even better, if someone knows how. I basically want to also add dashes and insert it like this
Col 1
200-250-5555
Is this possible using the LOAD DATA query?? Also ideally, if the column exists, it shouldn't insert it. Col1 should be a unique key.
Ok figured this one out on my own again. Heres the answer incase anyones interested.
SET ID = concat(SUBSTR(`area`, 1, 3), '-', SUBSTR(`number`, 1,3),'-', SUBSTR(`number`,4,7))
Or the full query
$query = sprintf("LOAD DATA local INFILE '%s' INTO TABLE number_check FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\
' IGNORE 0 LINES SET ID = concat(SUBSTR(`area`, 1, 3), '-', SUBSTR(`number`, 1,3),'-', SUBSTR(`number`,4,7))", addslashes($file));