title: MS SQL Server, Bulk Insert failing insert file in UTF-16 BE
body: <p>I have a problem with Bulk Insert on MS SQL Server 2012. Input file is saved in UTF-16 BE.</p>
  
  <pre><code>BULK INSERT Positions
  FROM 'C:\DEV\Test\seq.filename.csv'
  WITH
    (
      DATAFILETYPE = 'widechar',
      FORMATFILE = 'C:\DEV\Test\Format.xml'
    );
  </code></pre>
  
  <p>Fortmat file:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt;
  &lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;RECORD&gt;
      &lt;FIELD ID="ActionCode" xsi:type="NCharFixed" LENGTH="4" /&gt;
      &lt;FIELD ID="T1" xsi:type="NCharFixed" LENGTH="2" /&gt; 
      &lt;FIELD ID="ReofferCounter" xsi:type="NCharFixed" LENGTH="6" /&gt;
      &lt;FIELD ID="T1" xsi:type="NCharFixed" LENGTH="2" /&gt; 
      ... other fields....
  
    &lt;/RECORD&gt;
    &lt;ROW&gt;
      &lt;COLUMN SOURCE="ActionCode" NAME="DT" xsi:type="SQLNCHAR" LENGTH="255" /&gt;
      &lt;COLUMN SOURCE="ReofferCounter" NAME="NO" xsi:type="SQLNCHAR" LENGTH="255" /&gt;
    &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  
  <p>Input file sample:</p>
  
  <pre><code>02|+00|... other cols.... 
  02|+00|... other cols....
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/RLEJE.png" alt="enter image description here"></p>
  
  <p>I have two problems:</p>
  
  <p>1) If the input file has encoding UTF-16 BE, I get only chinesee characters instead of numbers. </p>
  
  <p>2) If I convert the input file to the UTF-16 LE, I see correct characters, but the character data are shifted one character to the left - as if BOM was parsed (and counted as 1 character), but not transformed to the output (which I do not desire). </p>
  
  <p><img src="https://i.stack.imgur.com/F4yiJ.png" alt="first column strange data"></p>
  
  <p>Questions:</p>
  
  <p>1) I there a way, how to import a file in UTF-16 BE withou conversion to LE?</p>
  
  <p>2) What causes the shift and how to avoid it?</p>