title: Using BULK INSERT to map columns
body: <p>I have a CSV file with 6 millions rows.  Each line is made up of the same format eg/</p>
  
  <pre><code>I,h,q,q,3,A,5,Q,3,[,5,Q,8,c,3,N,3,E,4,F,4,g,4,I,V,9000,0000001-100,G9999999990001800000000000001,G9999999990000001100PDNELKKMMCNELRQNWJ010, , , , , , ,D,Z
  </code></pre>
  
  <p>I have 2 columns in a table.</p>
  
  <p>The first column should be field 27 in the CSV and the second column should be the whole line in the CSV file.</p>
  
  <p>I have tried to set up a format file but cannot get it working.</p>
  
  <p>Is it even possible to do this sort of mapping?</p>
  
  <p>Here is what I have:</p>
  
  <pre><code>BULK INSERT Staging FROM 'C:\Data.txt' 
     WITH 
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR ='\n',
        KEEPNULLS,
        formatfile='C:\format.fmt'
     )
  </code></pre>
  
  <p>This is my format file</p>
  
  <pre><code>9.0
  2
  1       SQLCHAR       0       40     ","     27     Col27               SQL_Latin1_General_CP1_CI_AS
  2       SQLCHAR       0       200     "\r\n"  1     Col1               SQL_Latin1_General_CP1_CI_AS
  </code></pre>
  
  <p>In terms of comparison, I have this working in SQLite which takes 2min 35secs.</p>