grabbing "text,moretext" from csv file

[dustydee]

I have a comma seperated file (.csv) and I am seperating the elements using the string::split function, but I need a way to specify text that may contain commas but needs to be kept as one element in my array. Is there an easy way to do this? I know when you view a csv file in excel you can specify a text deliminator (") which is what is used in the csv file, I just gotta figure out how to keep that "te,xt" in one array element in my VB.net program.

 

[JohnH]

Your profile is .Net 1.1 but I just have to say that .Net 2.0 got a new class TextFileParser that handles this real easy.
With .Net 1.1 you have to do string manipulation and parsing yourself. There are only two 'tokens' you have to care about, the field quotes (") and the field separator (,). Here is some code that does this:

Sub csv2()
 Dim filename As String = "sample.csv"
 Dim fields As New Collections.Specialized.StringCollection
 Dim delimiter As String = ","
 Dim fieldqualifier As String = """"
 Dim infield As Boolean = False
 Dim line As String, from, i As Integer
 Dim fs As New IO.FileStream(filename, IO.FileMode.Open, IO.FileAccess.Read)
 Dim sr As New IO.StreamReader(fs)
 While sr.Peek <> -1
  fields.Clear()
  line = sr.ReadLine
  For i = 0 To line.Length - 1
    If line(i) = fieldqualifier Then 
     infield = Not infield
    ElseIf line(i) = delimiter And infield = False Then
     fields.Add(clean(line.Substring(from, i - from), [SIZE=2]fieldqualifier[/SIZE]))
     from = i + 1
    End If
  Next
  fields.Add(clean(line.Substring(from, i - from), [SIZE=2]fieldqualifier[/SIZE]))
  display(fields)
  from = 0
 End While
[SIZE=2] sr.Close()
 fs.Close()
[/SIZE]End Sub
 
Function clean(ByVal field As String, ByVal fieldqualifier As String) As String
 field = field.Trim()
 If field.StartsWith([SIZE=2]fieldqualifier[/SIZE]) = True And field.EndsWith(fieldqualifier) = True Then
  Return field.Substring(1, field.Length - 2)
 Else
  Return field
 End If
End Function
 
Sub display(ByVal fields As Collections.Specialized.StringCollection)
 Dim sb As New System.Text.StringBuilder
 For Each str As String In fields
  sb.AppendLine("'" & str & "'")
 Next
 MessageBox.Show(sb.ToString)
End Sub

 

[JohnH]

Just remembered that the StringBuilder AppendLine method is new in .Net 2.0, this code does the same thing:

sb.Append("'" & str & "'" & vbNewLine)

 

[dustydee]

thanks

Thanks for the response. Maybe it's time to upgrade to 2.0! I just finished writing my own function that's almost identical to what you posted. Well, time to start testing... (this was my last item to program in the application!). Wish me luck in ironing out the bugs!