title: How to bulk insert from CSV when some fields have new line character?
body: <p>I have a CSV dump from another DB that looks like this (id, name, notes):  </p>
  
  <blockquote>
    <p>1001,John Smith,15 Main Street<br>
    1002,Jane Smith,"2010 Rockliffe Dr.<br>
    Pleasantville, IL<br>
    USA"<br>
    1003,Bill Karr,2820 West Ave.  </p>
  </blockquote>
  
  <p>The last field may contain carriage returns and commas, in which case it is surrounded by double quotes. And I need to preserve those returns and commas.  </p>
  
  <p>I use this code to import CSV into my table:  </p>
  
  <pre><code>BULK INSERT CSVTest
  FROM 'c:\csvfile.csv'
  WITH
  (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
  )
  </code></pre>
  
  <p>SQL Server 2005 <strong>bulk insert cannot figure out that carriage returns inside quotes are not row terminators</strong>.<br>
  How to overcome?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>:<br>
  Looks like the only way to keep line breaks inside a field is to use different row separator. So, I want to mark all row separating line breaks by putting a pipe in front of them. How can I change my CSV to look like this?   </p>
  
  <blockquote>
    <p>1001,John Smith,15 Main Street|<br>
    1002,Jane Smith,"2010 Rockliffe Dr.<br>
    Pleasantville, IL<br>
    USA"|<br>
    1003,Bill Karr,2820 West Ave.|  </p>
  </blockquote>