title: Automate the Import of CSV file into SQL Server
body: <p>I am trying to import a number of different csv files into a SQL Server 2008R2 database</p>
  
  <p>The data in the  files is comma delimited.
  I have no say over the file format.</p>
  
  <p>Some columns are text and are delimited with double quotes ("like in excel").
  Those columns contain text that may have additional commas within the text ("However, it drives me crazy")..</p>
  
  <p>I tried to use Bulk Insert to loaded the text files into a number of SQL tables.   However, the embedded commas in the text columns cause it to crash.  SQL Server 2017 includes the option to set  <code>FORMAT =CSV and FIELDQUOTE = '"'</code> but I am stuck with SQL Server 2008R2.  </p>
  
  <p>I could use DTS/SSIS but it links a VS version to a SQL version.  So if I write the SSIS in VS2012 or VS2010 it may not work with our SQL Server 2008R2.  And I don't' think we have any VS2008 laying around.</p>
  
  <p>So what is the next best way to import these CSV files.  </p>
  
  <p>Bulk upload is the cleanest method to uploading half a dozen different csv files into different tables.</p>
  
  <p>Do I pre-process the csv files and replace commas with pipes. If so how do I know which commas to replace  (Regex?)?
  Or do I do the entire importation in .Net?   Which is messy and Time consuming.</p>