title: SqlBulkCopy and StreamReader handling Large CSV with Quotes for text qualifier
body: <p>I have a 400mb <code>.CSV</code> and wrote a routine to <code>SqlBulkCopy</code> the text records into SQL Server 2015. I have not found a way with <code>Split()</code> or the <code>StreamReader</code> to tell them that my <code>.CSV</code> has quotes for text qualifiers. Many records have commas in field content like a company name with a comma in it like "Acme Brick, Inc."</p>
  
  <p>As a result of not telling the <code>Split</code> or the <code>StreamReader</code> I have quotes for text qualifiers (all fields are string type), the split is not able to discern that the comma in the field content is not intended to be a delimiter.</p>
  
  <p>I want to load the large <code>.csv</code> into SQL Server via a C# console program per a requirement. Research indicates I could invoke a <strong>VB Text field parser</strong> and set the attributes to understand quotes are used as a text qualifier in the csv text records and comma outside the quotes is a delimiter.</p>
  
  <pre><code>parser.HasFieldsEnclosedInQuotes = true;
  parser.SetDelimiters(",");
  </code></pre>
  
  <p>Research also indicated that the VB text parser method was quite a bit slower that the C# Split and could not handle large files well. I read this I believe on openstack.</p>
  
  <p>So for a high performance large csv file processing routine to load the records into SQL Server programmatically in c# it would be great if I could set an attribute or <code>Split</code> for the <code>StreamReader</code> that would set them to handle quotes as  text delimiters in the case. Could I tell the split() something like </p>
  
  <pre><code>reader.ReadLine().Split('",'); 
  </code></pre>
  
  <p>where I tell it a quote and a comma together is really the delimiter. I presume I would need to escape the quote ...Split('/",'). Doubt that will pass syntax rules but just thinking wouldn't that be nice to be able to set the split to deal with the quotes as a text delimiter, so my field content with a comma in it would not throw the program off.</p>
  
  <p>Is there such a thing? If not, what would be the best approach to dealing with these records in the large csv. I have attached my code and I get an errors on the first record where a comma is in the field content indicating the number of columns  does not match the destination.</p>
  
  <pre><code>    using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
      using System.Data;
      using System.Data.SqlClient;
      using System.IO;
      using Microsoft.VisualBasic;
  
      namespace ConsoleApplication5
      {
          class Program
          {
              static void Main(string[] args)
              {
                  System.Diagnostics.Stopwatch elapsed = new System.Diagnostics.Stopwatch();
                  elapsed.Start(); Int64 rows = 0;
  
                  using (SqlBulkCopy bulkcopy = new SqlBulkCopy("server=CHKNOWLAN-PC\\SQLEXPRESS;database=MarketData;trusted_connection=yes",
                      System.Data.SqlClient.SqlBulkCopyOptions.TableLock)
                  {
                      DestinationTableName = "tblMarket",
                      BulkCopyTimeout = 0,
                      BatchSize = 100000
                  })
                  {
                      using (System.IO.StreamReader reader = new System.IO.StreamReader("C:\\ProgramData\\FMCSA_CENSUS1_2016May_1195.csv"))
                      {
                          using (DataTable datatable = new DataTable())
                          {
                              var columns = datatable.Columns;
                              columns.Add("DOT_NUMBER", typeof(System.String));
                              columns.Add("LEGAL_NAME", typeof(System.String));
                              columns.Add("DBA_NAME", typeof(System.String));
                              columns.Add("CARRIER_OPERATION", typeof(System.String));
                              columns.Add("HM_FLAG", typeof(System.String));
                              columns.Add("PC_FLAG", typeof(System.String));
                              columns.Add("PHY_STREET", typeof(System.String));
                              columns.Add("PHY_CITY", typeof(System.String));
                              columns.Add("PHY_STATE", typeof(System.String));
                              columns.Add("PHY_ZIP", typeof(System.String));
                              columns.Add("PHY_COUNTRY", typeof(System.String));
                              columns.Add("MAILING_STREET", typeof(System.String));
                              columns.Add("MAILING_CITY", typeof(System.String));
                              columns.Add("MAILING_STATE", typeof(System.String));
                              columns.Add("MAILING_ZIP", typeof(System.String));
                              columns.Add("MAILING_COUNTRY", typeof(System.String));
                              columns.Add("TELEPHONE", typeof(System.String));
                              columns.Add("FAX", typeof(System.String));
                              columns.Add("EMAIL_ADDRESS", typeof(System.String));
                              columns.Add("MCS150_DATE", typeof(System.String));
                              columns.Add("MCS150_MILEAGE", typeof(System.String));
                              columns.Add("MCS150_MILEAGE_YEAR", typeof(System.String));
                              columns.Add("ADD_DATE", typeof(System.String));
                              columns.Add("OIC_STATE", typeof(System.String));
                              columns.Add("NBR_POWER_UNIT", typeof(System.String));
                              columns.Add("DRIVER_TOTAL", typeof(System.String));
  
                              int batchsize = 0;
  
                              while (!reader.EndOfStream)
                              {
  
                                  string[] line = reader.ReadLine().Split(',');
                                  // Maybe use VB Text field parser here to parse
  the line before adding to the datatable by setting the parser
   parser.HasFieldsEnclosedInQuotes = true;
   parser.SetDelimiters(",");
                                  datatable.Rows.Add(line);
                                  batchsize += 1;
                                  if (batchsize == 100000)
                                  {
                                      bulkcopy.WriteToServer(datatable);
                                      datatable.Rows.Clear();
                                      batchsize = 0;
                                      Console.WriteLine("Flushing 100,000 rows");
                                  }
                                  rows += 1;
                              }
                              bulkcopy.WriteToServer(datatable);
                              datatable.Rows.Clear();
                          }
                      }
                  }
                  elapsed.Stop();
                  Console.WriteLine((rows + " records imported in " + elapsed.Elapsed.TotalSeconds + " seconds."));
                  Console.ReadLine();
              }
          }
      } 
  </code></pre>