title: Best way to ignore Comma (,) in CSV files if the Comma(,) is in quotation marks?
body: <p>I have a small program to read CSV files to build datatable out of it. One requirement is to ignore commas (commas in names, etc) if the commas are between quotation marks. Example.  </p>
  
  <pre><code>          Name, Age, Location
            "Henderson, David", 32, London
             John Smith, 19, Belfast
  </code></pre>
  
  <p>The program should ignore the comma after Henderson and read Henderson, David as one field. My current code can't do this job adding extra column at the end. So How can I achieve it? The solution should not replace the comma between the quotation marks. Thanks.</p>
  
  <p>My current code.</p>
  
  <pre><code> Public Function BuildDataTable() As DataTable
  
      Dim myTable As DataTable = New DataTable("MyTable")
      Dim i As Integer
      Dim myRow As DataRow
      Dim fieldValues As String()        
      Dim myReader As StreamReader = New StreamReader(_fileFullPath, Encoding.GetEncoding("iso-8859-1"))
  
      Try           
          fieldValues = myReader.ReadLine().Split(_seperator)
          'Create data columns accordingly
          If _hasheader = False Then
              For i = 0 To fieldValues.Length() - 1
                  myTable.Columns.Add(New DataColumn("Column(" &amp; i &amp; ")"))
              Next
          Else
              'if the file has header, take the first row as header for datatable
              For i = 0 To fieldValues.Length() - 1
                  myTable.Columns.Add(New DataColumn(fieldValues(i).Replace(" ", "")))
              Next
          End If
  
          myRow = myTable.NewRow
  
          If _hasheader = False Then
              For i = 0 To fieldValues.Length() - 1
                  myRow.Item(i) = fieldValues(i).ToString
              Next
              myTable.Rows.Add(myRow)
          End If
  
          While myReader.Peek() &lt;&gt; -1
              fieldValues = myReader.ReadLine().Split(_seperator)
              myRow = myTable.NewRow
              For i = 0 To fieldValues.Length() - 1
                  myRow.Item(i) = fieldValues(i).Trim.ToString
              Next
  
              If Not csv2xml.AreAllColumnsEmpty(myRow) = True Then
                  myTable.Rows.Add(myRow)
              End If
  
          End While
      Catch ex As Exception                   
      End Try     
  End Function
  </code></pre>