title: MySQL : how to load data with fixed-row format into user variables
body: <p>I'm trying to load a file where are all the lines use the same rules. (assume <em>HEADER</em> is a single line)</p>
  
  <pre><code>HEADER1
  HEADER2
  .......
  </code></pre>
  
  <p>But unluckily when I try to use the LOAD DATA INFILE statement I get this error: <strong>Error Code: 1409
  Can't load value from file with fixed size rows to variable</strong>.</p>
  
  <p>This is the code I wrote:</p>
  
  <pre><code>USE test;
  DROP TABLE IF EXISTS EXAMPLE_H;
  CREATE TABLE EXAMPLE_H(
      ID CHAR(20),
      SP CHAR(3),
      IVA CHAR(11) PRIMARY KEY,
      NLP CHAR(6),
      DLP DATE,
      DUVI DATE,
      DELP CHAR(30),
      FILLER CHAR(39),
      VTLP CHAR(3),
      FILL CHAR(49)
  );
  
  LOAD DATA INFILE 'BTILSP.TXT' 
      INTO TABLE test.EXAMPLE_H
      FIELDS TERMINATED BY ''
      LINES TERMINATED BY '\n'
      (ID, SP, IVA, NLP, @var_date_one, @var_date_two, DELP, FILLER, VTLP, FILL)
      SET DLP = str_to_date(@var_date_one, '%Y%m%d',
          DUVI = str_to_date(@var_date_two, '%Y%m%d');
  </code></pre>
  
  <p>I had this idea reading the bottom of <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html" rel="noreferrer">this page</a> (comment by Ramam Pullella), and I found the same explained on some websites, but I can't understand why I'm getting this error.</p>
  
  <p>If I don't use the @var_date_one and @var_date_two variables, and so the STR_TO_DATE function, the date isn't rendered as MySql needs - the date in the file is something like "20100701" - then that field would contain all zeros or a different date than what I'm expecting. If I change DLP and DUVI to be represented by CHAR(8), then it works, but I won't use the SQL DATE comparisons and similar tools.</p>
  
  <p>Can you help me please? :)
  Thank you very much.</p>
  
  <p>EDIT: </p>
  
  <p>It seems the problem is given by the LINE TERMINATED BY '', since this kind of line is a "fixed row (undelimited)". Maybe it can't be assigned to variable for an unknown reason, but it's this way it works.
  The documentation says:</p>
  
  <blockquote>
    <p>User variables cannot be used when
    loading data with fixed-row format
    because user variables do not have a
    display width.</p>
  </blockquote>
  
  <p>Any suggestion?</p>
  
  <p>RE-EDIT:
  I've read the comment by Ryan Neve at bottom of that page. He gives a trick to read fixed-row into variables:</p>
  
  <pre><code>LOAD DATA LOCAL INFILE '&lt;file name&gt;' INTO TABLE &lt;table&gt;
  (@var1)
  SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'),
  Time=SUBSTR(@var1,14,8),
  WindVelocity=SUBSTR(@var1,26,5),
  WindDirection=SUBSTR(@var1,33,3),
  WindCompass=SUBSTR(@var1,38,3),
  WindNorth=SUBSTR(@var1,43,6),
  WindEast=SUBSTR(@var1,51,6),
  WindSamples=SUBSTR(@var1,61,4);
  </code></pre>
  
  <p>Do you think it's a good way to do it? :)</p>