Load Data Infile - edit a field

[Michael Blair]

I would like to edit an incoming date field which is mm/dd/yyyy and
convert it to yyyy-mm-dd as required by mysql.

Any ideas?

[Bill Karwin]

"Michael Blair" <bl*****@blairc o.us> wrote in message
news:OP******** *******@newssvr 30.news.prodigy .com...

I would like to edit an incoming date field which is mm/dd/yyyy and convert
it to yyyy-mm-dd as required by mysql.

There's no field-editing facility in LOAD DATA INFILE.

One solution would be:
1. Create your table with a CHAR field instead of a DATE field.
2. Load the data as is into the CHAR field.
3. Add a new DATE field.
4. Use UPDATE to set the DATE field based on the value in the CHAR field,
using an expression using the builtin STR_TO_DATE() function:
UPDATE myTable SET dateField = STR_TO_DATE(cha rField, '%m/%d/%Y');
5. Drop the old CHAR field.

Another option would be to use Perl or a text editor to reformat the dates
in the input file before you load it.

Regards,
Bill K.

[Michael Blair]

Bill Karwin wrote:

"Michael Blair" <bl*****@blairc o.us> wrote in message
news:OP******** *******@newssvr 30.news.prodigy .com...

I would like to edit an incoming date field which is mm/dd/yyyy and convert
it to yyyy-mm-dd as required by mysql.

There's no field-editing facility in LOAD DATA INFILE.

One solution would be:
1. Create your table with a CHAR field instead of a DATE field.
2. Load the data as is into the CHAR field.
3. Add a new DATE field.
4. Use UPDATE to set the DATE field based on the value in the CHAR field,
using an expression using the builtin STR_TO_DATE() function:
UPDATE myTable SET dateField = STR_TO_DATE(cha rField, '%m/%d/%Y');
5. Drop the old CHAR field.

Another option would be to use Perl or a text editor to reformat the dates
in the input file before you load it.

Regards,
Bill K.

Sounds like a good solution, Thanks!