title: Problem with SQL bulk insert tab delimited file
body: <p>I am having a problem with using bulk insert. The issue is that the source files (tab delimited) that I'm dealing with contain rows that end in <code>cr/lf</code> without filling in values of the empty columns with tab for the rest of the row. So when the data is pulled into SQL Server, it's combining those shortened lines into the previous line. so basically it's combining multiple rows into one rather than writing it as two separate rows with nulls at the end of the first row.</p>
  <p>Example to illustrate the problem: sample .txt file</p>
  <pre><code>column1 column2 column3 column4 column5
  1   2   3   4   5
  2   5   4   6
  4   4   6   4   
  4   5   6   4   6
  </code></pre>
  <p>SQL to create table and bulk insert</p>
  <pre><code>CREATE TABLE test (
  [column1] varchar(MAX) NULL,
  [column2] varchar(MAX) NULL,
  [column3] varchar(MAX) NULL,
  [column4] varchar(MAX) NULL,
  [column5] varchar(MAX) NULL
  )
  
  BULK INSERT test
  FROM 'c:\temp\testimport.txt'
  WITH
  (
      FIRSTROW = 2,
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '\r'
  );
  </code></pre>
  <p>The really strange thing is that I can use the data import wizard and it imports the data perfectly, without any issue, and handles the lack of tabs for the columns just fine. But I don't know what the wizard is doing behind the scenes to make this happen. I would love to have the code it uses to create the table and do the insert as that would probably answer my question for me. At the end of the day I can't use the wizard as this will eventually be part of an automated task I'll be running against an SQL Server Express database on multiple files with different names but the same column header.</p>
  <p>Maybe bulk insert isn't the way to go here? Or there is something obvious I'm missing that someone else might know off the top of their head. Either way, all help is appreciated and thanks in advance.</p>
  <hr />
  <p>As Tim H suggested I've made a few attempts at creating a format file to accommodate the data. Results so far are as follows.</p>
  <p>Using
  <code>bcp temp.dbo.test format nul -x -f test_format.xml -n -T</code></p>
  <p>produces</p>
  <pre><code>&lt;BCPFORMAT xmlns=&quot;http://schemas.microsoft.com/sqlserver/2004/bulkload/format&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;
   &lt;RECORD&gt;
    &lt;FIELD ID=&quot;1&quot; xsi:type=&quot;CharPrefix&quot; PREFIX_LENGTH=&quot;2&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;2&quot; xsi:type=&quot;CharPrefix&quot; PREFIX_LENGTH=&quot;2&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;3&quot; xsi:type=&quot;CharPrefix&quot; PREFIX_LENGTH=&quot;2&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;4&quot; xsi:type=&quot;CharPrefix&quot; PREFIX_LENGTH=&quot;2&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;5&quot; xsi:type=&quot;CharPrefix&quot; PREFIX_LENGTH=&quot;2&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
   &lt;/RECORD&gt;
   &lt;ROW&gt;
    &lt;COLUMN SOURCE=&quot;1&quot; NAME=&quot;column1&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;2&quot; NAME=&quot;column2&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;3&quot; NAME=&quot;column3&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;4&quot; NAME=&quot;column4&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;5&quot; NAME=&quot;column5&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
   &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  <p>Using this temp file as is produces......</p>
  <p>Msg 4866, Level 16, State 7, Line 31
  The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.</p>
  <p>My attempt to edit the XML to work.....</p>
  <pre><code>&lt;BCPFORMAT xmlns=&quot;http://schemas.microsoft.com/sqlserver/2004/bulkload/format&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;
   &lt;RECORD&gt;
    &lt;FIELD ID=&quot;1&quot; xsi:type=&quot;CharTerm&quot; TERMINATOR=&quot;\t&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;2&quot; xsi:type=&quot;CharTerm&quot; TERMINATOR=&quot;\t&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;3&quot; xsi:type=&quot;CharTerm&quot; TERMINATOR=&quot;\t&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;4&quot; xsi:type=&quot;CharTerm&quot; TERMINATOR=&quot;\t&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
    &lt;FIELD ID=&quot;5&quot; xsi:type=&quot;CharTerm&quot; TERMINATOR=&quot;\r\n&quot; MAX_LENGTH=&quot;100&quot; COLLATION=&quot;SQL_Latin1_General_CP1_CI_AS&quot;/&gt;
   &lt;/RECORD&gt;
   &lt;ROW&gt;
    &lt;COLUMN SOURCE=&quot;1&quot; NAME=&quot;column1&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;2&quot; NAME=&quot;column2&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;3&quot; NAME=&quot;column3&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;4&quot; NAME=&quot;column4&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
    &lt;COLUMN SOURCE=&quot;5&quot; NAME=&quot;column5&quot; xsi:type=&quot;SQLVARYCHAR&quot;/&gt;
   &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  <p>Which does insert the data, but unfortunately still produces the same jumbled insert with overlapping lines in the same row.</p>