title: Skipping first column with LOAD DATA INFILE
body: <p>I have got such table:</p>
  
  <pre><code>mysql&gt; show create table final\G;
  *************************** 1. row ***************************
         Table: final
  Create Table: CREATE TABLE `final` (
    `id` int(4) NOT NULL AUTO_INCREMENT,
    `cdatetime` varchar(255) NOT NULL,
    `address` varchar(255) NOT NULL,
    `district` varchar(255) NOT NULL,
    `beat` varchar(255) NOT NULL,
    `grid` varchar(255) NOT NULL,
    `crimedescr` varchar(255) NOT NULL,
    `ucr_ncic_code` varchar(255) NOT NULL,
    `latitude` varchar(255) NOT NULL,
    `longitude` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>And I have got csv file which looks like this:</p>
  
  <pre><code>cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
  1/1/06 0:00,3108 OCCIDENTAL DR,3,3C        ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158
  1/1/06 0:00,2082 EXPEDITION WAY,5,5A        ,1512,459 PC  BURGLARY RESIDENCE,2204,38.47350069,-121.4901858
  1/1/06 0:00,4 PALEN CT,2,2A        ,212,10851(A)VC TAKE VEH W/O OWNER,2404,38.65784584,-121.4621009
  1/1/06 0:00,22 BECKFORD CT,6,6C        ,1443,476 PC PASS FICTICIOUS CHECK,2501,38.50677377,-121.4269508
  </code></pre>
  
  <p>What I want to do is load that CSV file into table final. Problem is that csv file doesn't have ID column so I'm thinking <strong>is it possible to somehow tell mysql to skip column ID and load data into the rest columns, but ID must be used</strong>. So ideally it would look like this:</p>
  
  <p>"1/1/06 0:00,3108 OCCIDENTAL DR,3,3C        ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158" gets loaded into columns and mysql automatically add 1 to column ID, then "1/1/06 0:00,2082 EXPEDITION WAY,5,5A        ,1512,459 PC  BURGLARY RESIDENCE,2204,38.47350069,-121.4901858" gets loaded and mysql add 2 to ID column etc etc..</p>
  
  <p>Recently user 'Shadow'  told me that I should specify which column I want to load so I did something like this:</p>
  
  <pre><code>load data infile '/SacramentocrimeJanuary2006.csv' INTO TABLE final (cdatetime, address, district, beat, grid, crimedescr, ucr_ncic_code, latitude, longitude);
  </code></pre>
  
  <p>Mysql returns:</p>
  
  <pre><code>ERROR 1261 (01000): Row 1 doesn't contain data for all columns
  </code></pre>
  
  <p>According to mysql load data infile manual field delimiter is not "," so I tried to change it by adding FIELDS TERMINATED BY ',' at the end of my statement but this breaks query. What is the correct syntax here ?</p>
  
  <p>Thanks</p>
  
  <p><strong>ANSWER</strong></p>
  
  <pre><code>mysql&gt; CREATE TABLE `final` (
      -&gt;   `id` int(4) NOT NULL AUTO_INCREMENT,
      -&gt;   `cdatetime` longtext  NULL,
      -&gt;   `address` longtext  NULL,
      -&gt;   `district` longtext  NULL,
      -&gt;   `beat` longtext  NULL,
      -&gt;   `grid` longtext  NULL,
      -&gt;   `crimedescr` longtext  NULL,
      -&gt;   `ucr_ncic_code` longtext  NULL,
      -&gt;   `latitude` longtext  NULL,
      -&gt;   `longitude` longtext  NULL,
      -&gt;   PRIMARY KEY (`id`)
      -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  Query OK, 0 rows affected (0.17 sec)
  
  mysql&gt; LOAD DATA infile '/SacramentocrimeJanuary2006.csv'  INTO TABLE final FIELDS TERMINATED BY ',' lines terminated by '\r' IGNORE 1 ROWS (cdatetime, address, district, beat, grid, crimedescr, ucr_ncic_code, latitude, longitude);
  Query OK, 7584 rows affected (0.08 sec)
  Records: 7584  Deleted: 0  Skipped: 0  Warnings: 0
  </code></pre>