Date from excel changes when uploaded into mysql?

Databases
1

Hi,

can’t believe i haven’t come across this issue before but i am trying to upload an excel spreadsheet with a column that contains dates and also other text.

the date is in the format 08/09/2011 but when i load it into mysql it changes it to some decimal format e.g. 40804 ???

how can i just upload it as is, without it changing? or is there a bit of php i can run to turn the number back into a date?

I wouldn’t normally mix dates and text but this table already exists and i haven’t got time to change 700+ records manually. Its a varchar column but i have tried Text as well with no luck. I can’t use a Date column as the text will cause a problem.

I even tried tricking excel by adding a word before the date in a new column but even in excel it changed it to the stupid decimal date.

What can i do i’m really stuck on this one!!!

any help would be most appreciated. not even sure what to search for.

2

finally bit the bullet and did it properly. changed the field type to decimal 10,2 in mysql.

had to do a find and replace in excel and remove any text that had been added.

Now just have to get rid of the 0.00’s which i’ll just do on the fly with php easy enough.

got to make sure next time i agree to do something to make sure i set up the original spreadsheet with the correct date/time fields without text etc.

thanks for your help.

3

hi thanks.
finally got something vaguely acceptable date by setting the cell type to text.

But OMG it is the most infuriating thing ever. excel just seems hell bent on doing what it wants and thinking it knows best.

I’m now trying to do the time column where people have entered things like 9.30 for 09:30:00 excel removes the last 0 when i upload so i get 9.3 ??? not very useful.

I’ve tried pretty much all of the cell types and its still refusing to keep the .00’s
The Text type promises to keep the text as it is typed in but when it changes it from numerical it handily removes the 0’s. very useful excel! if i wanted to remove the 0’s i would change the decimal places!!!

aaaahhh. sorry for the rant! i’ve spent all morning on what i was thinking was an hours work if that.

I’m thinking of doing a nasty work around and using php to add the 0’s back on when they are displayed on the page. thankfully its just an events listing thats needed for a couple of weeks otherwise i’d build it properly from scratch with a proper date and time field.

thanks

4

if you’re loading 9.30 into a numeric column, no wonder you aren’t seeing the trailing zero

my advice is to load your data into a “landing” table, and then run an INSERT SELECT statement to copy the data into its final destination table, applying conversion expressions as required

5

change the cell format in excel from date to string