LOAD DATA INFILE easily convert YYYYMMDD to YYYY-MM-DD?

Question

Hi I have an INFILE I want to import, but the dates are of the form :

AADR,20120403,31.43,31.43,31.4,31.4,1100
AAU,20120403,2.64,2.65,2.56,2.65,85700
AAVX,20120403,162.49,162.49,154.24,156.65,2200

Is there any easy way to convert the dates to be '2012-04-03' without having to do something like open it first with a perl script, convert the dates, and then writing the file back out again?

TIA !!

Answer 1

This loads and converts in one step, no need for another table. For more information see the manual.

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
FIELDS TERMINATED BY ',' 
(column1, @var1, column3, ...)
SET column2 = STR_TO_DATE(@var1,'%Y%m%d')