CODEWITHSUNDEEP
mysqlcsvimport
Akshay Jagadale
Akshay Jagadale

Reputation: 151

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

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,

Upvotes: 2

Views: 8050

Answers (2)

Jonatas
Jonatas

Reputation: 108

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)

Upvotes: 2

MOCKBA
MOCKBA

Reputation: 1790

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

Upvotes: 2

Related Questions