Splitting csv file w/commas in a field

[James]

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

[PlausiblyDamp]

strInput="123,testdata,testdata with a comma ,,last field"
strInput=strInput.Replace(",,",",")                     'New line here
arrArray = strInput.Split(",")

[James]

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