title: SQL Server bulk insert type mismatch error from csv
body: <p>I am trying to import data from a tsv (tab separated values) file into a table using SQL Server and Visual Studio, and I am encountering an error that I can't seem to fix. I was able to recreate a small example of how my DB is, and to sort of mimic the issue I am encountering.</p>
  
  <p>I created a new table in my database with the following properties from the .sql file:</p>
  
  <pre><code>CREATE TABLE [dbo].[TestTable]
  (
      [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
      [variable1] INT NULL, 
      [variable2] INT NULL, 
      [variable3] INT NULL
  )
  </code></pre>
  
  <p>I'd like the ID to be auto-generated whenever a row is added to the table. For my post-deployment script I am reading in the tsv file:</p>
  
  <pre><code>BULK INSERT TestTable
  FROM 'C:\Users\me\Documents\projects\testData.txt'
  WITH
  (
      FIELDTERMINATOR = '\t',  --CSV field delimiter
      ROWTERMINATOR = '\n',   --Use to shift the control to next row
      ERRORFILE = 'C:\Users\me\Documents\projects\testDataError.txt'
  )
  </code></pre>
  
  <p>And this is my data file separated by tabs:</p>
  
  <pre><code>11  11  11
  22  22  22
  33  33  33
  </code></pre>
  
  <p>And lastly the errors I am getting:</p>
  
  <blockquote>
    <p>Msg 4864, Level 16, State 1, Line 12<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (variable2).</p>
    
    <p>Msg 4832, Level 16, State 1, Line 12<br>
    Bulk load: An unexpected end of file was encountered in the data file.</p>
    
    <p>Msg 7399, Level 16, State 1, Line 12<br>
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.</p>
    
    <p>Msg 7330, Level 16, State 2, Line 12<br>
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".</p>
  </blockquote>
  
  <p>I'm mostly concerned with the first error, by any advice on the other errors as well would be greatly appreciated. Thanks!</p>
  
  <p>edit: Hex file added <a href="http://i.stack.imgur.com/TBXkr.png" rel="nofollow">hex info for data file</a></p>
  
  <p>ErrorFile printed to testDataError.txt</p>
  
  <pre><code>11  11  11
  22  22  22
  33  33  33
  33  33  33
  </code></pre>
  
  <p>Edit: Second hex file added for data file
  <a href="http://i.stack.imgur.com/vfWre.png" rel="nofollow">Second Hex Dump</a></p>