title: SQL to import certain comma separated fields from a text file
body: <p>I'm trying to import data from a number of text files into a SQL-Server Database.</p>
  
  <p>I'm happy with the batch side of running SQL on all the files, but I'm struggling to get the SQL to work.</p>
  
  <p>The format of the text files is:</p>
  
  <pre><code>UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  
  Field1,Field2,Field3,Field4
  
  UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
  </code></pre>
  
  <p>The complication is the fields in the text file do not match up in order with the table fields, and I don't want to import all the data.</p>
  
  <p>My table looks like:</p>
  
  <pre><code>ID (int - autoincrement) | Field3 (nvarchar) | Field1 (datetime) | ImportDate (datetime) | Text from whole text file for reference (nvarchar)
  </code></pre>
  
  <p>Therefore my requirements are for Field 3 to be placed in Column 2 of the table and Field 1 to be placed into Column 3 of the table.</p>
  
  <p>My SQL looks like:</p>
  
  <pre><code>USE FOO_DB
  BULK INSERT dbo.BarTable
  WITH
  (
      FORMATFILE = 'C:\format.txt'
      FIELDTERMINATOR = ',',
      FIRSTROW = 13
  )
  </code></pre>
  
  <p>My format.txt file looks like:</p>
  
  <pre><code>10.0
  5
  1       SQLCHAR             0       24      ","    3        Field1                        Latin1_General_CI_AS
  2       SQLCHAR             0       100     ""     0        ID                            ""
  3       SQLCHAR             0       100     ","    2        Field3                        Latin1_General_CI_AS
  4       SQLCHAR             0       100     ""     0        ImportDate                    ""
  5       SQLCHAR             0       24      ""     0        FileText                     ""
  </code></pre>
  
  <p>When attempting to import, I get:</p>
  
  <pre><code>0 rows affected
  </code></pre>
  
  <p>If I fiddle around the the FIRSTROW value, I occasionally get loads of:</p>
  
  <pre><code>Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Field 3)
  </code></pre>
  
  <p>I read that FIRSTROW doesn't count from the first row of the actual file, but the first row that contains field separators? If so, the only row before my data that has commas is row 4. My data is on row 13 - there are no rows inbetween with commas.</p>
  
  <p>If I delete all the data in the text file apart from the comma separated stuff I want, then adjust FIRSTROW to 1, I get:</p>
  
  <pre><code>Bulk load: An unexpected end of file was encountered in the data file
  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
  </code></pre>