MySQL I need to import data from a txt file but in the file I have different datatype.
This is how it looks in workbench:
load data local infile 'C:/Users/user/Desktop/visits.txt'
into table Docs
character set utf8
fields terminated by ','
enclosed by '"'
ignore 4 lines
Your date data (
22/04/2005) is not in a format acceptable to MySQL, which is what your warning message is all about. To get around this, you can use the
SET statement along with
STR_TO_DATE to convert your dates into a format which MySQL can handle.
LOAD DATA LOCAL INFILE 'C:/Users/user/Desktop/visits.txt' INTO TABLE Docs FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 4 LINES (@var, EntranceHour, FirstName, LastName, DocName, DocLastName, Hospital) SET VisitDate = STR_TO_DATE(@var, '%d/%m/%Y')
See this SO question for more information.