MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ‘ ‘

csvMySQLtable

I've been trying to upload a CSV table into my remote server database but I find an error. I use MySQL in a RedHat Linux distrib. My mycsv.csv file looks like this:

aa,ProductDescription,country,potato,L,11/18/2013,N,05

bb,ProductDescription,country,tomato,L,12/31/9999,N,05

cc,ProductDescription,country,curry,M,01/01/2014,Y,05

dd,ProductDescription,country,spicy,V,05/01/2015,N,

As you may see, last line ends with comma "," without having any field (it is empty and should be like this or even better if I can change it to NULL). However when I type the following query:

LOAD DATA INFILE "/home/mycsv.txt" 
INTO TABLE inbound.master_data  
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

I get the following error in the command line

Error Code: 1366. Incorrect integer value: '' for column
'product_life_cycle' at row 4

Does anybody know how to set a null value after the comma in the last line?
(I think this is the problem why I can't upload my table). Thank you very much in advance.

The table definition:

CREATE TABLE master_data
  ( aa varchar(6) NOT NULL, 
    bb varchar(50) NOT NULL, 
    cc varchar(3) NOT NULL, 
    dd varchar(2) NOT NULL, 
    ee varchar(1) DEFAULT NULL, 
    ff varchar(20) NOT NULL, 
    gg varchar(1) NOT NULL, 
    hh int(11) DEFAULT NULL, 
    PRIMARY KEY (aa), 
    UNIQUE KEY aa_UNIQUE (aa) 
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 ;

Best Answer

To set NULL you just need your table to have allow nulls.

If you want to change it to some default value in case of nulls/missing values, you need to specify individual column names as below:

LOAD DATA INFILE "/home/mycsv.txt" 
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
    (name,proddescr,country,thing,charA,datecol,charB,@VAL)
    set numberCol=if(@VAL is null, 0, @VAL);

You can change that 0 to any other default value.

This blog post has many other interesting load data scenarios you might want to learn. (go through the comments as well).

Related Question