Comma delimited files

[LostCreekDavid]

I know this has been talked about, but how do you read CSV files when some of the columns have embedded commas? For example

1,4,"Jones,John",1/1/1960
2,4,"Smith,Bob",2/2/1966
3,6,johnson,3/1/1963

When I use split, it puts the first and last names in two columns, but sometimes there are not two names. Is there an easy way to not split commas between quotes?

 

[RiverGuy]

You might try using an ODBC or OLEDB text driver to read the data into a DataSet. It should be able to be configured to use double quotes as a text qualifier.

 

[mmilan]

That's fairly sensible advice if all you want to do is read the data...

Personally, back in VB6 land, I wrote a class to parse such lines...

Martin

 

[LostCreekDavid]

Thanks for your thoughts. What I ended up finding in the documentation (after a LOT of searching) was:
---------------------------------------------------
Public Class Form1

Public Sub ReadDelimit()
Using MyReader As New _
Microsoft.VisualBasic.FileIO.TextFieldParser("C:\testfile.csv")
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters(",")
Dim currentRow As String()
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
Dim currentField As String
For Each currentField In currentRow
MsgBox(currentField)
Next
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
End While
End Using

End Sub

Private Sub TextBox1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Click
ReadDelimit()
End Sub
End Class

-------------------------------------
David