Split function & embedded quotes

[Glasgow]

I am reading a comma delimited text file and was hoping to use the split function to transfer fields into an array. However, some text fields are quoted and contain commas that are not field separators. To complicate matters, other fields contain multiple consecutive quotes e.g.

Line 1: 1,2,3,4,green pullover
Line 2: 3,4,5,6,"large pullover, blue"
Line 3: 7,8,9,10,"""D"" shaped earrings"

Excel sorts it all out fine when improrting the text file but is there an easy way to separate out these fields in VB?

Thanks in advance

 

[vb5prgrmr]

You can use the replace function to remove the quotes
[tt]
StringResult = Replace(StringLine, """", "&quot
'or
StringResult = Replace(StringLine, Chr(34), "&quot
[/tt]

Good Luck

 

[Glasgow]

Thank you.

However, I am left with problem of 'non-significant' commas embedded in some quoted descriptions. Any thoughts?

Each line has 5 fields - but line 2 has an extra comma. I think my hope of using Split function is fading fast!

 

[vb5prgrmr]

If you know what the ubound of the array is supposed to be then you can check for it and if it exceeds you could run the origional string through a custom function to parse it correctly for you. So you would only need to do the extra processing if it (ubound of array) does not match.

Good Luck

 

[MichaelRed]

A bit of a long way 'round the barn', but (in outline), read the entire 'mess' into something and replace all the 'embeded' commas with another character (I would probably use a semicolon), Then use 'ye olde splitte' to seperate the fields, then (finally) re-replace the substitute char (";"?) with a comma.

In the above, 'embeded' means any comma found between a pair of quotes.

This can be a reasonably tedious exercise, but overall it is rather straight forward.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Glasgow]

Thanks for replies - I do already have a mechanism of sorts but only recently stumbled across Split command and was kicking myself for not having used it before. Looks like it will have to wait until another time.

 

[vb5prgrmr]

not really you could incorporate the following logic in your loop....
[tt]
Option Explicit

Private Sub Form_Load()

Dim S As String, MyArray() As String

S = "1,2,3,4,green pullover"

MyArray = Split(S, ",&quot

S = "3,4,5,6," & """" & "large pullover, blue" & """"

MyArray = Split(S, ",&quot

If UBound(MyArray) > 4 Then MyArray(4) = MyArray(4) & " " & MyArray(5)
MyArray(4) = Replace(MyArray(4), """", " &quot

S = "7,8,9,10," & """" & """" & """" & "D" & """" & """" & "shaped earrings" & """"

MyArray = Split(S, ",&quot
MyArray(4) = Replace(MyArray(4), """", " &quot


End Sub
[/tt]

As you can see, putting in the checks inside of your loop should not be that hard.

Good Luck

 

[CCLINT]

Use something that doesn't usually exist as a delimeter:
(here I've used a Pipe symbol)

myArray=Split(myString, "|&quot

Another simple way is to use ADO and the JET Text ISAM driver to open the text file.
Then you can just use the GetString method to create your string...