Separating a custom string into variables

[Cmosquera]

HELP!

I have a string that contains the following:

0,"121"33,"AA"1136,"32"30,"32MPBA"29,"470027009917"

I want to separated into individual fields using VB code as follows

Field 0 = "121"
Field 33 = "AA"
Field 1136 = "32"
Field 30 = "32MPBA"
Field 29 = "470027009917"

As you can see, it is in the following format:
field name,"data"field name,"data"field name,"data"

I'm having a difficult time visualizing in code what I want to do...

 

[woyler]

You can use the Split function. For Example:

Dim vFields as Variant

'this will separate the string by the commas into an array
vFields= Split(0,"121"33,"AA"1136,"32"30,"32MPBA"29,"470027009917&quot,",&quot

vFields(0)'0
vfields(1)'"121"
vFields(2)'33
vfields(3)'"AA"
'and so on
Is this what you were looking for?

 

[daimaou]

Or you can use the Instr function to look for the string ", on your string and then use the mid function to get the part of the string you want.

 

[Cmosquera]

Neither one of the two previous suggestions help because if you look closely, you'll see that the string is not comma delimited. It is delimited by the second quote...

 

[Rejordan]

This little sub will do it. I left the commas in but you can modify this to get rid of them too if you want. You'll also see that for this example I used single quotes to set up the string, so you'll want to modify this to look for the double quotes.


Hope it helps....

Regards,
Richard

Dim strOne As String

Private Type udtTemp
strName As String
strValue As String
End Type

Dim aStrings() As udtTemp

Public Sub StringSplit(strIn As String)

Dim intCount As Integer
Dim strJunk As String

strJunk = strIn
While Len(strJunk)
ReDim Preserve aStrings(intCount + 1)
aStrings(intCount).strName = Left$(strJunk, InStr(1, strJunk, ",&quot)
strJunk = Mid$(strJunk, InStr(1, strJunk, ",&quot + 2, Len(strIn))
aStrings(intCount).strValue = Left$(strJunk, InStr(1, strJunk, "'&quot - 1)
strJunk = Mid$(strJunk, InStr(1, strJunk, "'&quot + 1, Len(strIn))
intCount = intCount + 1
Wend
End Sub

Private Sub Form_Load()

strOne = "0,'121'33,'AA'1136,'32'30,'32MPBA'29,'470027009917'"

StringSplit strOne
End Sub

 

[MichaelRed]

I seem to be missing SOMETHING. I can split the original 'stuff' - but only after (manually) replacing the CHr(34) (double Quote mark) with Chr(39) (Single Quote / apostrophy):

This is just the test function. NOTE the CHANGED "String" used in Calling the 'Real' function

Public Function TestDblSplit()

    Dim MyFieldsArray As Variant

    MyFieldsArray = dblSplit("0,'121'33,'AA'1136,'32'30,'32MPBA'29,'470027009917'")

    For Idx = 0 To UBound(MyFieldsArray)
        Debug.Print Idx, MyFieldsArray(Idx)
    Next Idx

End Function

This is the REAL function.  I don not know HOW to get the arg list /stuff into here without changeing SOMETHING!!!

Public Function dblSplit(StrIn As String) As Variant

Dim varWords As Variant
Dim Idx As Integer

'This just gives us the words. Leaves the Commas in the Words
varWords = Split(StrIn, "'&quot 'Separate on "Quote" Char

Dim MyFields() As Variant
ReDim MyFields(UBound(varWords) / 2)
'This strips the commas and any outsinde spaces
For Idx = 0 To UBound(varWords)
varWords(Idx) = Trim(Replace(varWords(Idx), ",", "&quot) 'Dump Commas
' varWords(Idx) = Replace(varWords(Idx), Chr(34), "&quot 'Dump Quotes
Next Idx

'Now, re-assemble the Field Id and it's corresponding Value
For Idx = 0 To UBound(varWords) - 2 Step 2
MyFields(Idx / 2) = "Field " & varWords(Idx) & " = " & _
Chr(34) & varWords(Idx + 1) & Chr(34)
Next Idx

dblSplit = MyFields

End Function


Results:
TestDblSplit
0 Field 0 = "121"
1 Field 33 = "AA"
2 Field 1136 = "32"
3 Field 30 = "32MPBA"
4 Field 29 = "470027009917"
5

BUT I can't form the given "String" (0,"121"33,"AA"1136,"32"30,"32MPBA"29,"470027009917&quot
into a VALID string without changing the chr(34) to chr(39) - I do not know / understand how "Mr. I Want" gets the object to be represented as a STRING in VB.

Can anyone complete the exercise? Show HOW the object is conformed to being a string?


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[strongm]

MichaelRed makes what appears to be a valid point about the format of the string to be parsed.

However, I think it is safe to assume that just because you cannot easily type it in as a simple string assignment in VB that it is erroneous. It may well have been read in from a text file created by another application, or built by a string building function.

So, working with the string as originally quoted (and demonstrating how such a string is easily built), and keeping the code as small as possible:

Option Explicit

Private Sub InvokingRoutine()
Dim strData As String

' This is how I build the string for the sake of the example. Obviously you use your original source
strData = "0," + Chr$(34) + "121" + Chr$(34) + "33," + Chr$(34) + "AA" + Chr$(34) + "1136," + Chr$(34) + "32" + Chr$(34) + "30," + Chr$(34) + "32MPBA" + Chr$(34) + "29," + Chr$(34) + "470027009917" + Chr$(34)

ParseString strData
End Sub

Private Function ParseString(strData As String)
Dim strBaseSplit() As String
Dim lp As Long
Dim FieldName As String
Dim FieldValue As String

strBaseSplit = Split(strData, Chr$(34))
For lp = LBound(strBaseSplit) To UBound(strBaseSplit) - 1 Step 2
FieldName = Left(strBaseSplit(lp), Len(strBaseSplit(lp)) - 1)
FieldValue = strBaseSplit(lp + 1)
' Do what you like with the above variables. For sake of example I just
' dump them to immediate window
Debug.Print FieldName, FieldValue
Next
End Function

 

[Cmosquera]

Thank you Strongm! That was EXACTLY what I needed... And you guessed it right, the string is the reply from an API call so it includes the " inside the string... Your code was perfect...