Fixing date field format upon import

[admoore]

I have a comma delimited text file which conatains an improperly formatted date field.

[COLOR=red yellow]mm-dd-yyyy instead of yyyy-mm-dd[/color]

I need to automate the import process into a MySQL database. I have pre-processed such files with awk scripts before to fix the date format prior to import; but, isn't there a simpler way to do it on-the-fly without resorting to an external awk script? If the data were properly formated I would simply do something like:

$result = mysql_query("LOAD DATA INFILE 'path/list.txt'
                       INTO TABLE my_table
                       FIELDS TERMINATED BY ','
                              ENCLOSED BY '\"'
                       (`field1`, `field2`, `date`)
                      ");

Any suggestion on how revise my import to accomodate this date issue without fixing the data externally first will be appreciated.

TIA,

-Allen

 

[sleipnir214]

There's no way to to the transformation while the LOAD DATA query is running. You'll have to fix the data first.


Want the best answers? Ask the best questions!

TANSTAAFL!!