splitting csv file w/commas in the field

[jl39775]

I have a comma delimited file with fields that have commas. I don't know how to get the split function form splitting when a field has a comma in it. I tried to put quotes around string values but the split function still splits into an element in an array.

my code

strInput="123,testdata,testdata with a comma ,,last field"
arrArray = strInput.Split(",")

what I want is:
arrArray(0)=123
arrArray(1)=testdata
arrArray(2)=testdata with a comma ,
arrArray(3)=last field

what I get is:
arrArray(0)=123
arrArray(1)=testdata
arrArray(2)=testdata with a comma
arrArray(3)=
arrArray(4)=last field

Can someone help?

Thanks,

James

[rdove]

I do believe you need 2 commas for each comma you want. try this:

strInput="123,testdata,testdata with a comma,,,last field"

[angiras]

best was is to export you csv first replacing commas

[jl39775]

Here's the solution to this problem. I passed the string and delimiter and qualifier to the function below. The function parses the string into an arrayList. Next, I assign the arrayList values into the strInput string removing the quotes then adding a tab delimiter between each field. Finally, I split the strInput string into an array. This works with string field that have commas.

Dim strValues As New ArrayList()
Dim obj As New Object()
Dim strInput as String
Dim count As Integer

strInput="""123"",""testdata"",""testdata with a comma ,"",l""ast field"""
strValues = SplitDelimitedLine(strInput, ",", """")
count = 0
strInput = ""
For Each obj In strValues
If strInput = "" Then
strInput = strValues(count).section.ToString
ElseIf strValues(count).section = Nothing Then
strInput += vbTab
Else
strInput += vbTab + strValues(count).section.ToString
End If
If Left(strInput, 1) = """" Then
strInput.Remove(0, 1)
End If
If Right(strInput, 1) = """" Then
strInput.Remove(0, 1)
End If
count += 1
Next

arrArray = strInput.Split(vbTab)

Private Function SplitDelimitedLine(ByVal CurrentLine As String, ByVal Delimiter As String, ByVal Qualifier As String) As ArrayList ' Collection
Dim i As Integer
Dim SplitString As New ArrayList() 'Collection()
Dim CountDelimiter As Boolean
Dim Total As Integer
Dim Ch As Char
Dim Section As String
' We want to count the delimiter unless it is within the text qualifier
CountDelimiter = True
Total = 0
Section = ""
For i = 1 To Len(CurrentLine)
Ch = Mid(CurrentLine, i, 1)
Select Case Ch
Case Qualifier
If CountDelimiter Then
CountDelimiter = False
Else
CountDelimiter = True
End If
Case Delimiter
If CountDelimiter Then
' Add current section to collection
SplitString.Add(New MySection(Section))
Section = ""
Total = Total + 1
Else
Section = Section & Ch
End If
Case Else
Section = Section & Ch
End Select
Next
' Get the last field - as most files will not have an ending delimiter
If CountDelimiter Then
' Add current section to collection
SplitString.Add(New MySection(Section))
End If
SplitDelimitedLine = SplitString
End Function

Private Class MySection

Public Section As String
Sub New(ByVal newName As String)
Section = Replace(newName, Chr(10), "")
End Sub

End Class