Datetime in Excel CSV Field

[Divante]

Hi, basically i am parsing some rtf/word files and generating excel csv's for upload into mysql database....my issue is in one of the fields i want to store the value in the mysql datetime / date  format but excel keeps reformating it. Any help on how to overcome this will be highly appreciated.

[gristoi]

leave the excel date format as it is and use the date conversion power in mysql to alter the date to a timestamp on insert:

let say for example you had it showing in the excel sheet as 01/10/2011 00:01:01, then you would do this on the insert:

$date = '01/10/2011 00:01:01';
INSERT INTO tbl (`datecolumn`) values (STR_TO_DATE($date,'%d/%m/%Y $h:%i:%s'))

 

you basically use the string to date function in mysql. the left hand part of the argument is the date you are passing in and the right hand part telss it what format the date is in. Mysql will do the rest from there and convert it into a datetime / date for you

 

[Adam]

CSV isn't Excel specific, essentially it's just a plain-text file. Excel is reformatting the date according to your preference when you open it... Just don't open it in Excel before you import it into the database.

[Divante]

thanks alot i appreciate the quick responses...thats why i love this forum