title: MySQL 5.6: Error on loading empty string
body: <p>I am using <code>LOAD DATA LOCAL INFILE</code> and I have tried to handle empty strings (the data is from a csv generated in excel) and converted in comma separated windows file format. </p>
  
  <p>My approach is to replace with NULL using a trigger. The code I am using is below and the data has the following form:</p>
  
  <pre><code>PERMNO,date,EXCHCD,SICCD,TICKER,COMNAM
  10001,20000131,,,EWST,ENERGY WEST INC
  10001,20000229,,4920,EWST,ENERGY WEST INC
  10001,20000331,3,,EWST,ENERGY WEST INC
  10001,20000428,3,4920,EWST,ENERGY WEST INC
  10001,20000531,3,4920,EWST,ENERGY WEST INC
  </code></pre>
  
  <p>I have read and searched for two days. Tried a bunch of things, so I turn to you. </p>
  
  <p>The manual indicates that </p>
  
  <blockquote>
    <p>Treatment of empty or incorrect field values differs from that just
    described if the SQL mode is set to a restrictive value. For example,
    if sql_mode='TRADITIONAL, conversion of an empty value or a value such
    as 'x' for a numeric column results in an error, not conversion to 0.
    (With LOCAL, warnings occur rather than errors, even with a
    restrictive sql_mode value, because the server has no way to stop
    transmission of the file in the middle of the operation.)</p>
  </blockquote>
  
  <p>In my case (using mySQL Workbench as the client) transmission <strong>does stop</strong> and I get the following error message (<strong>not a warning</strong>): Error code 1366 incorrect integer value for column exchange_code. One last note, my code and trigger work like a charm with <strong>any other cell value</strong> but not on the empty string I need it to catch.</p>
  
  <p>Any help you can provide will be extremely appreciated!</p>
  
  <pre><code>DROP TABLE IF EXISTS dum;
  
  CREATE TABLE dum (
  permno INT,
  date date,
  exchange_code INT(2),
  sic INT(4),
  ticker CHAR(10),
  fname VARCHAR(200));
  
  delimiter //
  CREATE TRIGGER test BEFORE INSERT ON dum
          FOR EACH ROW
          BEGIN
              IF NEW.exchange_code = "" THEN SET NEW.exchange_code = NULL;
              END IF;
              IF NEW.sic = "" THEN SET NEW.sic = NULL;
              END IF;
  END;//
  delimiter ;
  
  LOAD DATA LOCAL INFILE '~/Desktop/dum.csv'   
  REPLACE
  INTO TABLE dum
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  OPTIONALLY ENCLOSED BY '"'
  
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
  </code></pre>