1

I'm trying to export Excel sheets to MySQL as new table using the plugin.

The export fails on "MySQL error 1064" when I tried to change the data type to Date.

I have a few columns in Excel that are formatted as Short Date, although some of them are generated by functions like =TODAY(), some are numbers like "42445", etc.

But all of them date type not Date Time.

For example it bothers me that MySQL detects '1/1/2016' in Excel as '1/1/2016 12:00:00 AM' as VarChar(225). And whenever I changed the type to Date the error message mentioned above appears.

I did try to re-format the columns in spreadsheet to YYYY-MM-DD but MySQL always convert them to MM/DD/YYYY HH:MM:SS

Thanks in advance for any help.

MySQL for Excel 1.3.6

Office 2016 32-bit

edit: this looks more like the plugin's bug of this version https://bugs.mysql.com/bug.php?id=80079

1

Kindly Note that MySQL stores date in YYYY-MM-DD format by default.

One way is to convert all dates into YYYY-MM-DD format so that it will be compatible with MYSQL.

We can format the date column in excel before exporting it to MYSQL by following the below steps:

  1. Select the column which contains the date
  2. Right click and select format cells
    i. Choose date under category(on left-hand side)
    ii. Then choose custom under category
    iii. Under type insert the format --> YYYY-MM-DD
    You will see all the dates in the columns get converted into the format --> YYYY-MM-DD

Then you can import the date into MYSQL safely.

Another way is to keep the column datatype as VARCHAR and use TO_DATE() function to parse the string data into DATE format.

Hope this solves your problem !!

  • Hi Bibhuti, thanks for the reply. I tried that before and MySQL always convert them to mm/dd/yyyy no matter what formats are used in Excel – cxwf Jul 14 '16 at 12:26
  • So are you still facing the same issue? – Bibhuti Bhusan Padhi Jul 14 '16 at 14:08
  • yes. see my edit. it could be 1.3.6's bug – cxwf Jul 14 '16 at 14:22
  • If you also need time: YYYY-MM-DD hh:mm:ss – user161095 Sep 19 '18 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.