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

Question

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!

[LOAD DATA INFILE easily convert YYYYMMDD to YYYY-MM-DD?](http://stackoverflow.com/questions/10102167/load-data-infile-easily-convert-yyyymmdd-to-yyyy-mm-dd) — Solarflare, Aug 27 '16 at 10:48
@Solarflare I think he dates are in the wrong format. The year always has to come first AFAIK. — Tim Biegeleisen, Aug 27 '16 at 11:02
@Tim Biegeleisen yes, I know, but I was kinda certain op could figure it out that way on his own with this link. — Solarflare, Aug 27 '16 at 12:08

score 3 · Accepted Answer · edited May 23 '17 at 10:33

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.

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

1 Answers1