Excel and MySQL

[firedrop84]

Hi,

 

I have an excel sheet with many data there. I am trying to import all these data to the righ tables in the database which I could manage to do it successfully. However, the issue I am facing is with the date coloumns.

 

in the database, I am using a timestamp and the field is integer in the db (yes, I know I could use date as the field type but now it's quite late to change as it might affect my php code).

 

my question is how can I change a normal date format in excel from something like dd/mm/yyyy to timestamp in excel. Any ideas?

 

Best Regards,

Ahmed

[Mchl]

I'd just import this data to a temporary (not SQL TEMPORARY) table and do all necessary conversions there. Then copy to actual table.

[PFMaBiSmAd]

You can use the mysql STR_TO_DATE function directly in a query to convert any date format into a mysql DATE value - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

 

If necessary, you can then use the mysql UNIX_TIMESTAMP() function to convert that into a Unix Timestamp - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp

 

it's quite late to change as it might affect my php code

 

Yes, it will likely reduce the amount of code and result in faster execution. It's never too late to fix design problems.

[JustLikeIcarus]

If you need an excel formula I found this.

 

=(((E2-(6*3600))/86400)+25569)

 

    * E2 = cell reference

    * 6 = Timezone Offset (this is Central Standard time)

    * 3600 = Number of seconds in an hour

    * 86400 = Number of seconds in a year

    * 25569 = Excel hack because excel counts epoch from 1/1/1900 and most others start at 1/1/1970.

[firedrop84]

Thanks everyone for your reply.

 

I just would like to make it clearer of what I am trying to have.

 

I am trying to change the date format in excel from dd/mm/yyyy to timestamp in order I can import at later stage in the database.

I tried =(((E2-(6*3600))/86400)+25569) but it didn't work. I had in field B2 the date and I used the formula in C2. It changed it from 1/1/2010 to 1/1/1970!!.

 

[JustLikeIcarus]

Ok I messed around in Excel and came up with this formula which worked fine for me.

 

=(A1-25569)*86400

[fenway]

Yeah, it's the whole 1970 vs epoch thing.