Load Data Infile - edit a field

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?

Re: Load Data Infile - edit a field

"Michael Blair" wrote in message
news:OPhzf.920$NS6.612@newssvr30.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(charField, '%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.

Re: Load Data Infile - edit a field

Bill Karwin wrote:
> "Michael Blair" wrote in message
> news:OPhzf.920$NS6.612@newssvr30.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(charField, '%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!