Ballack Ballack - 3 months ago 8
MySQL Question

MySQL I need to import data from a txt file but in the file i have different datatype

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
(VisitDate,EntranceHour,FirstName,LastName,DocName,DocLastName,Hospital)
;


When I proceed I get warnings for VisitDate, in txt is this format 22/04/2005.

Thanks!

Answer

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.

Comments