1366 incorrect integer value: error when importing data from csv into mysql

Question

I am trying to import data from csv into MySQL using LOAD DATA LOCAL INFILE. For an integer column called "count" that has all null values I am getting the error 1366 incorrect integer value:

I have tried count = IF(@count='',NULL,@count)

CREATE TABLE MySchema.response:

CREATE TABLE MySchema.response
(
`id` int,
`name` varchar(500), 
`count` int,
);

loading data into table:

LOAD DATA LOCAL INFILE 'C:/response.csv' 
INTO TABLE MySchema.response
FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' 
ignore 1 lines
(id, name, @count)
SET count = IF(@count='',NULL,@count)

sample csv file data

1,abc,

2,xyz,

I am encountering the same problem right now. Have you managed to solve the issue? — Amir Tugi
– Amir Tugi, Commented Jan 23, 2017 at 8:07

MOCKBA · Accepted Answer · 2016-03-06 01:01:17Z

2

you forgot to define the var, see correct LOAD DATA INFILE ... statement below:

root@localhost [test]> SHOW CREATE TABLE `Response`\G
*************************** 1. row ***************************
       Table: Response
Create Table: CREATE TABLE `Response` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(500) DEFAULT NULL,
  `count` int(11) DEFAULT NULL
)

root@localhost [test]> LOAD DATA INFILE '~/Desktop/data.csv' INTO TABLE Response FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'   (id, name, @count) SET count = IF(@count = '', NULL, @count);

root@localhost [test]> SELECT * FROM `Response`;
+------+------+-------+
| id   | name | count |
+------+------+-------+
|    1 | abc  |  NULL |
|    2 | xyz  | 12345 |
+------+------+-------+

File data.csv

1,abc,
2,xyz,12345

edited Mar 6, 2016 at 1:01

answered Mar 6, 2016 at 0:56

MOCKBA

1,79011 silver badges20 bronze badges

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

1 Comment

Akshay Jagadale Over a year ago

Sorry that was a typo... I have defined the var

Jonatas · Accepted Answer · 2021-01-20 21:44:41Z

A bit late, but it may help someone with this same issue.

You should check for line breaks (somehow MySQL parse the third text column this way). Also make sure your CSV file doesn't have BOM (bit order mark).

LOAD DATA LOCAL INFILE 'd:/response.csv' 
INTO TABLE response
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
IGNORE 0 LINES
(id, NAME, @count)
SET COUNT = IF(@count='' OR @count='\r' OR @count='\n' OR @count='\r\n' ,NULL,@count)

1 queries executed, 1 success, 0 errors, 0 warnings

Query: LOAD DATA LOCAL INFILE 'd:/response.csv' INTO TABLE response FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' ignore 1 lines...

1 row(s) affected

Execution Time : 0.004 sec Transfer Time : 0.006 sec Total Time : 0.011 sec

If your CSV file has all null values for COUNT, this simpler way will work:

LOAD DATA LOCAL INFILE 'd:/response.csv' 
INTO TABLE response 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES
(id, NAME, @dummy)

Collectives™ on Stack Overflow

1366 incorrect integer value: error when importing data from csv into mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related