VB Split Function

[jfergy]

Hello all-

I have a split function for seperating out a csv formatted text file. (Fields seperated by commas with Double Quotes around text). The issue is that many of the fields have descriptive text where someone uses a comma. How do I make sure the split function ignores those commas?

-----Code that does the initial split
aTmp = Split(aData(b), chr(44))

-----Code that runs the sql string and gets rid of the double quotes.
sValueList = sValueList & ", " & SQLString(aTmp(aFieldPositionList(c) - 1))

-----Function being called above
function SQLString(sString)

sTmp = Trim(sString)
if Len(sTmp) then
SQLString = "'" & Replace(Replace(sTmp, chr(34), ""), "'", "''") & "'"
else
SQLString = "null"
end if

end function

[si_the_geek]

that is an awkward part of the split - you can't tell it which parts to ignore. There are two possible solutions that spring to mind:

1) Write your own split function that ignores the commas that are between quotes. (or search this forum for a replacement)

2) Convert the comma characters between quotes to a character that you know isn't going to be inside the quotes, then split it, and convert the character back to commas afterwards in your SQLString function.


The first will always give you the right values (in the second, if the character you choose is in the string, it will be changed to a comma).


Luckily for you, VB 5 didn't have Split, so several people wrote their own; here's one version of it, which will be a good starting point for option 1:

Function VB5_Split(TheString, Optional Delim, Optional Limit As Long = -1, Optional ignored) As Variant
'FROM PSC. - Duplicates the functionality of the vb6 counterpart.
'Unfortunately, I was unable to include the vbcompare part _
 of the vb6 funtionality.
'Just use Option Campare at the beggining of this module.
 
Dim dynArray() As Variant
  If Not (IsNull(TheString)) And Len(Delim) > 0 Then
      Dim ArrCt%
      Dim CurPos%
      Dim LenAssigned%
      Dim CurStrLen%
      ArrCt% = 0
      CurPos% = 1
      LenAssigned% = 1
      CurStrLen% = Len(TheString)
      Do
          ReDim Preserve dynArray(0 To ArrCt%)
          CurStrLen% = (InStr(CurPos%, TheString, Delim) - CurPos%)
          If CurStrLen% < 0 Then
              dynArray(ArrCt%) = Right$(TheString, (Len(TheString) - (LenAssigned% - 1)))
              Exit Do
          Else
              dynArray(ArrCt%) = Mid$(TheString, CurPos%, CurStrLen%)
          End If
          LenAssigned% = LenAssigned% + (Len(dynArray(ArrCt%)) + Len(Delim))
          CurPos% = LenAssigned%
          ArrCt% = ArrCt% + 1
          If Not Limit = -1 Then
              If ArrCt = Limit Then Exit Do
          End If
      Loop
      VB5_Split = dynArray
  Else
      'duplicate the functionality more acuratley
      ReDim dynArray(0 To 0)
      dynArray(0) = TheString
      VB5_Split = dynArray
  End If
 
End Function

[jfergy]

Hello all-

Found the fix.

aTmp = Split(aData(b), """,""")

This was much simpler than creating some replace object before and after the input also makes it easier to know the difference between a seperator or a comma in a description type field.

Thanks
James F.

[si_the_geek]

Originally posted by jfergy

aTmp = Split(aData(b), """,""")

That's fine as long as ALL the fields have quotes around them

[jfergy]

Yep it does work with double quotes. The other suggestions would not work. Run through what they were then test it out and you can see why.

[transio]

There is no simple solution to this. CSV only creates quotes on string data. Besides that, even if you're using all strings, your leading and trailing quotes will be ignored on each line.

The only way to accurately parse the CSV file for its data is to create a parser (or instantiate a spreadsheet object that will parse it for you).

EDIT: Try the Microsoft Excel Object Library