0

i'm trying to import a csv file into MySQL under CentOS.
The csv file comes from a conversion of excel under Windows. Here it is:

1,Colombo

Then I create a table with the command:

create table testcity2 (id Integer, name_eng varchar(100), primary key(id)) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

After that I try to import it:

LOAD DATA LOCAL INFILE '~/citytmp.csv' INTO TABLE testcity2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

For now everything seems fine.

However, when I try to import csv file containing multiple lines, everything is mess up. I mean when I select * from testcity2, I get messy codes.

Here is the warning that I get:

Warning | 1366 | Incorrect integer value: '1' for column 'id' at row 1

Yves
  • 11,597
  • 17
  • 83
  • 180
  • Since the source file was made in windows, did you try using '\r\n' as line terminator? – ivospijker May 05 '17 at 08:00
  • @DrIvol yes I did. – Yves May 05 '17 at 08:17
  • Then I wouldn't know how to fix this. It seems that there are a lot of other related questions regarding the 1366 error, maybe you can find a solution there. – ivospijker May 05 '17 at 08:19
  • @DrIvol oh I got it: http://stackoverflow.com/questions/31546890/getting-error-1366-incorrect-integer-value-1-when-importing-file – Yves May 05 '17 at 08:34

0 Answers0