MYSQL ERROR 1366 (HY000): Incorrect integer value: ''

Question

I downloaded a few databases from Census - Fact Finder:

https://factfinder.census.gov/faces/tableservices/jsf/pages/productview.xhtml?pid=ACS_pums_csv_2012_2016&prodType=document

And started working with California Housing Database, using CVed I took out all the columns that I didn´t need and then proceed to create a Model in MySQL Workbench which I later turn into a DB via command lines:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema Census
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema Census
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Census` DEFAULT CHARACTER SET utf8 ;
USE `Census` ;

-- -----------------------------------------------------
-- Table `Census`.`Housing`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Census`.`Housing` (
  `SERIALNO` BIGINT(13) NOT NULL,
  `DIVISION` INT(9) NULL,
  `PUMA` INT(4) NULL,
  `REGION` INT(1) NULL,
  `ST` INT(1) NULL,
  `ADJHSG` INT(7) NULL,
  `ADJINC` INT(7) NULL,
  `FINCP` INT(6) NULL,
  `HINCP` INT(6) NULL,
  `R60` INT(1) NULL,
  `R65` INT(1) NULL,
  PRIMARY KEY (`SERIALNO`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

After the DB was created I proceeded to import the data:

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/Housing_California.csv"
INTO TABLE housing
CHARACTER SET latin1
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n';

But it doesn´t work as this error message appears:

ERROR 1366 (HY000): Incorrect integer value: '' for column 'FINCP' at row 15

Now, I now that at that row my I have a blank value, that column refers to the Family Income which in some cases was 0 so Census left it as blank:

2012000000135,9,6515,4,6,1045360,1056030,,43100,1,0

I could change all the blank cells to 0 but I feel like there has to be a better way to handle things.

Hope you can give me a tip.

See: https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data — KIKO Software, Apr 01 '18 at 07:50
Why not changing `FINCP` INT(6) NULL, to `FINCP` VARCHAR(20) and after importing - use UPDATE to convert strings to integer, or even convert on PHP layer or whatever you use. — AndrewShmig, Apr 01 '18 at 07:50
@KIKOSoftware buddy, I posted an answer for a message I`m gettint following the link you posted, hope you can give it a look — René Martínez, Apr 02 '18 at 03:16

score 0 · Accepted Answer · answered Apr 01 '18 at 08:58

This could help you
Instead of change the table definition you could change the import

in this way the val for FINCP is stored in a var before inserting and you can check for proper result
In mi suggestion if null is assigned to '' but you can assign the (proper ) value you prefere

  LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/Housing_California.csv"
  INTO TABLE housing
  CHARACTER SET latin1
  COLUMNS TERMINATED BY ','
  LINES TERMINATED BY '\n'
   ( `SERIALNO`,  `DIVISION`,  `PUMA`,  `REGION`,
    `ST`,  `ADJHSG`,  `ADJINC`,  @var_fincp,
    `HINCP`,  `R60`,  `R65`)
  set `FINCP` = nullif(@var_fincp,'')
  ;

Is it the sames as this? https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data — René Martínez, Apr 02 '18 at 03:31

score 0 · Answer 2 · answered Apr 02 '18 at 03:15

Kiko Software,

Ok, so I read the the link you posted and my command line ended up to be;

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/Housing_Illinois.csv"
INTO TABLE housing
CHARACTER SET latin1
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@SERIALNO, @DIVISION, @PUMA, @REGION, @ST, @ADJHSG, @ADJINC, @FINCP, @HINCP, @R60, @R65)
SET
SERIALNO = nullif(@SERIALNO,''),
DIVISION = nullif(@DIVISION,''),
PUMA = nullif(@PUMA,''),
REGION = nullif(@REGION,''),
ST = nullif(@ST,''),
ADJHSG = nullif(@ADJHSG,''),
ADJINC = nullif(@ADJINC,''),
FINCP = nullif(@FINCP,''),
HINCP = nullif(@HINCP,''),
R60 = nullif(@R60,''),
R65 = nullif(@R65,'');

And I`m gettint this message:

' for column 'R65' at row 12t integer value: '

What does that meann?

This is the data of row12:

2012000000318,3,1602,2,17,1045360,1056030,,,,

I can't tell. I suggest to post this as a new question, not as an answer to an old question. Please try to provide enough information so a reader can try out the loading of data. That is: Which version of MySQL you're using on which OS, the creation of your table, your load command, a reasonable amount of lines (>> 10), the exact error you get and what you've tried so far. See: https://stackoverflow.com/help/mcve — KIKO Software, Apr 02 '18 at 09:44

score 0 · Answer 3 · answered Apr 24 '19 at 14:13

When you try to insert data in a mysql table you will receive this error if is set that is requesting all fields to be filled.

You can configure your mysql to set NULL if you don't send values. temporary solution (until you restart the server) to run this query: SET @@GLOBAL.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

or you can set it in you my.ini file. Here are mode details: http://webnetkit.com/error-sqlstatehy000-general-error-1366-incorrect-integer-value-for-column/

MYSQL ERROR 1366 (HY000): Incorrect integer value: ''

3 Answers3