MySQL Load File Invalid Integer

Question

I am trying to import the following CSV data into a fresh clean table with the below settings. And keep getting two entries added and then warnings. Why is it giving an error? I have changed it from Inno to MyISAM as I thought that might be the issue, but no luck.

16,"Data point 1"
15,"Data point 2"
14,"Point 3a"
13,"Data total"

After running this SQL

LOAD DATA LOCAL INFILE 'C:/Users/Me/CSV.csv' INTO TABLE points FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r'

I get the following warning

2 row(s) affected, 2 warning(s): 1366 Incorrect integer value: ' 15' for column 'id' at row 2

The table is setup as follows.

id INT(11) Default 0 PK
value VARCHAR(45) Default NULL

Maybe your lines are not actually terminated by \r, but \r\n instead? That would explain the whitespace character in Incorrect integer value: ' 15' – it would just be the \n. So try LINES TERMINATED BY '\r\n' instead. — C3roe
– C3roe, Commented Mar 18, 2013 at 15:58
Put that as an answer and I will accept it as that simple error I missed was the problem. Thanks! — Somk
– Somk, Commented Mar 18, 2013 at 16:01

jesse_galley · Accepted Answer · 2013-03-18 16:01:37Z

1

It may be that your lines are not actually terminated by just '\r' but '\r\n'.

If this is the case, you will most likely get the errors you are describing

Perhaps try something like:

load data local infile '<path2csv>' into table test.test_table fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n'

I would also check the actual text file with an editor which has the option to show hidden characters, such as Vi (I believe you can get a graphical version of Vim for windows).

answered Mar 18, 2013 at 16:01

jesse_galley

1,6864 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

C3roe · Accepted Answer · 2013-03-18 16:08:32Z

0

Maybe your lines are not actually terminated by \r, but \r\n instead? That would explain the whitespace character in Incorrect integer value: ' 15' – it would just be the \n.

So try LINES TERMINATED BY '\r\n' instead.

answered Mar 18, 2013 at 16:08

C3roe

97.6k15 gold badges100 silver badges172 bronze badges

Collectives™ on Stack Overflow

MySQL Load File Invalid Integer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related