title: SELECT FROM OPENROWSET( BULK...) changing special characters
body: <p>I've been facing an issue for a few hours, and I can't seem to get my head around this one.</p>
  
  <p>So I have a SQL Server database 2008R2, Collation SQL_Latin1_General_CP1_CI_AS.
  Inside there is a table, with a field named incoming_name. The collation of this field is also SQL_Latin1_General_CP1_CI_AS, and it is a NVARCHAR(255).</p>
  
  <p>I have a .csv file with around 123000 rows. It's a basic csv, no double quotes around text, but no comma inside the fields, so when I run a manual import into my database it works fine. The incoming_name field contains all kind of text, but never longer than 255 characters. And in a few lines there are <strong>french accents</strong> (like 'Ch*<em>â</em>*teau d'Agassac').</p>
  
  <p>Now I try to use the code </p>
  
  <pre><code>select 
      test_file.[INCOMING_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
      as [INCOMING_NAME]
      , test_file.[PRODUCT_CODE] AS [PRODUCT_CODE]
  FROM
                  OPENROWSET(
                  BULK 'INSERT PATH OF THE .CSV HERE',
                  FORMATFILE = 'INSERT PATH OF THE FORMAT FILE HERE',
                  FIRSTROW = 2
                  ) AS test_file
  </code></pre>
  
  <p>With the format file </p>
  
  <pre><code>&lt;?xml version="1.0"?&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="4" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="255" COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS" /&gt;
          &lt;FIELD ID="29" xsi:type="CharTerm" TERMINATOR='\r\n' /&gt;
      &lt;/RECORD&gt;
      &lt;ROW&gt;
          &lt;COLUMN SOURCE="4" NAME="INCOMING_NAME" xsi:type="SQLNVARCHAR"/&gt;
          &lt;COLUMN SOURCE="29" NAME="PRODUCT_CODE" xsi:type="SQLNVARCHAR"/&gt;    
      &lt;/ROW&gt;
  </code></pre>
  
  <p></p>
  
  <p>The import works fine, and I get all my data, with the right values in the right fields, except for the accents... </p>
  
  <p>For example when I add <code>where test_file.incoming_name like '%agassac%'</code> at the end of my query, I get a result like 'ChÃ¢teau d'Agassac' instead of the original data 'Château d'Agassac' in my database.</p>
  
  <p>What I don't understand is that I feel like at every step of the process, I did pick an accent sensitive collation, with a unicode datatype (NVARCHAR), so I really don't understand why the import doesn't pick the accents.</p>
  
  <p>Thanks for reading this long question,</p>
  
  <p>John.</p>
  
  <p>EDIT: Ok, it looks like the .csv file I want to import is encoded with utf-8, and SQL Server 2008 doesn't want to support utf-8 import. Now I have no idea what to do. Any idea welcome...</p>