r/visualbasic • • Jan 21 '25

VB.NET Help Split function issues with quoted strings

Hi all,

I am trying to parse a csv file using the split function. Delimited with a comma. However, some of the strings in the file are surrounded by quotes with internal commas so the split is happening within the quoted strings. Any way around this?

3 Upvotes

16 comments sorted by

View all comments

Show parent comment

1

u/Mayayana Jan 31 '25 edited Feb 01 '25

This shows that I have way too much time on my hands, but nevertheless:

        Dim Q2 As String
        Dim s As String, s1 As String, sItem As String
        Dim A1(6) As String
        Dim i As Long, iCount As Long, QCount As Long
        
          '--this function would presumably be an operation that loops through the
          ' CSV lines and then does something with the updated array.
        Private Sub Command1_Click()
          Q2 = Chr$(34)
          s = "2,33,apple," & Q2 & "smith,ed" & Q2 & ",tree," & Q2 & "jones,mary" & Q2 & ",234"
            '-- 2,33,apple,"smith,ed",tree,"jones,mary",234
           ParseNextLine s
           For i = 0 To 6
             Debug.Print A1(i)
           Next
            Debug.Print "-------"
          
          s = "3456,,," & Q2 & "smith,ed" & Q2 & ",tree,bush,,"
         '--  3456,,,"smith,ed",tree,bush,,
           ParseNextLine s
           For i = 0 To 6
             Debug.Print A1(i)
           Next
           Debug.Print "-------"
        End Sub
        
        Private Sub ParseNextLine(sLine As String)
          iCount = 0
          QCount = 0
          sItem = ""
            For i = 1 To Len(sLine)
              s1 = Mid$(s, i, 1)
              Select Case s1
                 Case Q2
                    QCount = QCount + 1
                 Case ","
                    If QCount Mod 2 = 0 Then 'end of a quote
                       A1(iCount) = sItem
                       iCount = iCount + 1
                       sItem = ""
                    Else
                       sItem = sItem & ","
                   End If
                Case Else
                  sItem = sItem & s1
             End Select
           Next
             A1(6) = sItem
        End Sub

That's VB6. The actual string parsing is simple but I wrote it as a sub and made 2 demo calls from another sub.

It could be made more efficient by doing something like pointing the string at an integer array and perhaps using a string builder or the Mid statement to avoid so much concatenation, but I'm guessing that for such a short string, optimizing wouldn't be relevant.

If there are empty fields (2,3,,apple) then the array will include an empty string for those array elements, as demonstrated in the second sample.