2

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 ;
  • I think you can put \N for NULL. – ypercubeᵀᴹ Aug 14 '15 at 12:49
  • I've seen that on other threads but I don't know how to do it. However, if I do this will I solve the problem of upload the table? – Sebas Aug 14 '15 at 12:50
  • By editing the file? And if that column is char or varchar, you can put "" for an empty string. – ypercubeᵀᴹ Aug 14 '15 at 12:50
  • Sure, I would edit it manually if I could but the file is 1048576 rows... is there a way to do it "automatically" like awk command or something? – Sebas Aug 14 '15 at 12:55
  • Perhaps is that column hh is integer, I tried to change it to varchar(20) and it worked to upload. But still this field should be integer... – Sebas Aug 14 '15 at 13:28
2

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).

0

Ok, firts thanks all for your help. I managed to solve the problem by simply setting the last column to "varchar(10)". It turns out that integer columns can not accept blank values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.