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
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:
- Select the column which contains the date
- 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 !!