CODEWITHSUNDEEP
mysqlcsvdatetimetype-conversion
dragonfly
dragonfly

Reputation: 21

MySql Workbench csv Import Datetime DATATYPE as TEXT

Trying to import data from csv to Mysql database using mysql Workbench Table Data Import Wizard. The csv contains a dattime field in the format dd/mm/yyyy hh:mm:ss (eg. 28/01/2017 10:55:00 PM). If I go by default settings, workbench considers the datetime as 'TEXT' datatype and imports data successfully. However, If I change the datatype to 'DATETIME', the import completes with '0 records imported'.

Upvotes: 2

Views: 5795

Answers (2)

user7571182
user7571182

Reputation:

The datetime format for mysql database is YYYY-MM-DD HH:MM:SS... Check out the documentation here. Hope this helps.....

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521299

Dates in the format dd/mm/yyyy are not standard, hence MySQL is treating it as text. And forcing it to load such data as datetime is also wrong.

This problem is well suited to LOAD DATA along with the STR_TO_DATE() function to convert the date strings into proper dates:

LOAD DATA INFILE 'input.csv'
INTO TABLE t1
FIELDS TERMINATED BY ',' 
(column1, @var1, column3, ...)
SET column2 = STR_TO_DATE(@var1,'%d/%m/%Y %h:%i:%s %p')

The above LOAD DATA would be appropriate if the second column in your CSV file contained the date data. If not, then just rearrange it so that the correct column gets parsed.

Upvotes: 0

Related Questions