title: SQL Server BULK INSERT fixed length char data
body: <p>I use SQL Server 2008 and have a table with 5 char typed columns.</p>
  
  <pre><code>CREATE TABLE [dbo].[deviceDataBulk](
  [f1] [char](9) NULL,
  [f2] [char](5) NULL,
  [f3] [char](7) NULL,
  [f4] [char](7) NULL,
  [f5] [char](6) NULL)
  </code></pre>
  
  <p>I also have a bcp format file ;</p>
  
  <pre><code>&lt;RECORD&gt;
   &lt;FIELD ID="1" xsi:type="CharFixed" LENGTH="9" COLLATION="Turkish_CI_AS"/&gt;
   &lt;FIELD ID="2" xsi:type="CharFixed" LENGTH="5" COLLATION="Turkish_CI_AS"/&gt;
   &lt;FIELD ID="3" xsi:type="CharFixed" LENGTH="7" COLLATION="Turkish_CI_AS"/&gt;
   &lt;FIELD ID="4" xsi:type="CharFixed" LENGTH="7" COLLATION="Turkish_CI_AS"/&gt;
   &lt;FIELD ID="5" xsi:type="CharFixed" LENGTH="6" COLLATION="Turkish_CI_AS"/&gt;
  &lt;/RECORD&gt;
  &lt;ROW&gt;
   &lt;COLUMN SOURCE="1" NAME="f1" NULLABLE="YES" xsi:type="SQLCHAR"/&gt;
   &lt;COLUMN SOURCE="2" NAME="f2" NULLABLE="YES" xsi:type="SQLCHAR"/&gt;
   &lt;COLUMN SOURCE="3" NAME="f3" NULLABLE="YES" xsi:type="SQLCHAR"/&gt;
   &lt;COLUMN SOURCE="4" NAME="f4" NULLABLE="YES" xsi:type="SQLCHAR"/&gt;
   &lt;COLUMN SOURCE="5" NAME="f5" NULLABLE="YES" xsi:type="SQLCHAR"/&gt;
  &lt;/ROW&gt;
  </code></pre>
  
  <p>My data file contains fixed length char data with no field terminators in each line. So, a full line will be 34 characters long.</p>
  
  <p>My problem is field 4 and field 5 may not be present for each row. I may have 21 characters long line or 28 characters long line in that file.</p>
  
  <p>There is no case that field 5 exists and field 4 not. </p>
  
  <p>Possible scenarios for text file are ;</p>
  
  <pre><code>f1 f2 f3 f4 f5
  f1 f2 f3 f4
  f1 f2 f3
  </code></pre>
  
  <p>I couldn't insert this file with <code>BULK INSERT</code>. I want <code>BULK INSERT</code> to insert nulls when it doesn't have those fields, if the tool reaches end of line, just insert nulls for the rest of the fields.</p>