title: Comma's causing a problem using BULK INSERT and a Format File
body: <p>I'm trying to import .CSV files using BULK INSERT on SQL Server and a Format File. I have created the format file and set the correct field terminators for each value.  The format file has been correctly validated and I have successfully imported some test data.</p>
  
  <p>However I have a problem when encountering comma's in the middle of quoted text, such as the following:</p>
  
  <p>"SB","7Y4386429Y0351805","B5503","","Chris",1,10,"Paypal","Hi sorry for the delay in payment, I will be sending payment soon."</p>
  
  <p>(example data)</p>
  
  <p>I've set the format file to correctly identify fields which are quoted and non-quoted, but the last field has a comma in the middle, and this is creating an error whilst importing.</p>
  
  <p>I have the FieldTerminator set to "," and have tried leaving it out completely too, but shouldn't the terminator be ignored and instead the ones in the format file used? </p>
  
  <p>I don't understand why the comma would cause the field to end, as the field is surrounded by quotation marks. Is this by design? Surely the comma should be ignored as it doesnt match any of the field terminators in the format file?</p>
  
  <p>What are my options here? The data in question is coming from Paypal so there's not much chance of them changing the way it's exported.</p>
  
  <p>Any ideas?</p>
  
  <p>If it matters, I have shown the first part of my format file below (the real file contains 56 rows). All 56 rows have the same SQLCHAR datatype, the same length and the same end value.</p>
  
  <pre><code>10.0                            
  56
  1   SQLCHAR 0   0   "\""    0   DUMMY   SQL_Latin1_General_CP1_CI_AS                            
  2   SQLCHAR 0   100 "\",\"" 1   CH  SQL_Latin1_General_CP1_CI_AS
  3   SQLCHAR 0   100 "\",\"" 2   TransactionID   SQL_Latin1_General_CP1_CI_AS
  4   SQLCHAR 0   100 "\",\"" 3   InvoiceID   SQL_Latin1_General_CP1_CI_AS
  5   SQLCHAR 0   100 "\",\"" 4   PayPalReferenceID   SQL_Latin1_General_CP1_CI_AS
  </code></pre>
  
  <p>Any help would be much appreciated.</p>
  
  <p>Thanks,</p>
  
  <p>Chris.</p>