Split text loop

[matt beamish]

Hi all,
long time no see from me as my job changed a bit, and I'm not doing anything near the amount of VBA I used to do. So apologies, but I am just a bit rusty and would appreciate a hand.

I need to split a field that is consistently delimited by commas, and contains numeric strings between the commas. which I need to append into another table along with another field.

I have incorporated Allen Browne's ParseWord function
Parseword()
into a db, and by using this in a query, and then applying the Int() function
Int()
to the results, I get what I need.

However, Allen Brownes adaptation of Split() requires the position of the string to be extracted from the overall string ("ByVal iWordNum As Integer") but I need to loop through the complete string and break all the data into chunks. In the query I have am needing to specify postion 1, position 2, position 3 etc for the outputs, but this means guessing the number of postential outputs, and the number of values separated by commas in the field Description is not constained.

Here is some example data as an input, and the output I need

Input

Dwg Description
3.13 Posthole [590]
3.14 Posthole [590]
3.15 Relationship of ditch [600], ditch [598], ditch [596]
3.16 Relationship of ditch [600], ditch [598], ditch [596]

Output

Dwg Context
3.13 590
3.14 590
3.15 600
3.15 598
3.15 596
3.16 600
3.16 598
3.16 596

So I need to adapt the ParseWord() or Split() function to loop through all my data, with all the extracted values in one column. Might anyone help me with this please? I always struggled with loops

thanks

 

[Gasman]

Might be better replacing , [ and ] with nothing, and then split on space. ?
Then loop through all segments and test if numeric from 2 onwards, as you know the first is always numeric

 

[MajP]

This is not super efficient but seems to work.

Public Function RemoveNonNumeric(StrInput As String) As String
  Dim I As Long
  Dim char As String
  Dim newString As String
 'Keep 0-9, Period and Spaces 
 For I = 1 To Len(StrInput)
    char = Mid(StrInput, I, 1)
    If (Asc(char) > 47 And Asc(char) < 58) Or Asc(char) = 46 Or Asc(char) = 32 Then
      newString = newString & char
    End If
  Next I
 'remove double spaces 
 Do
    newString = Replace(newString, "  ", " ")
  Loop Until InStr(newString, "  ") = 0
  RemoveNonNumeric = newString
End Function

 

[moke123]

Not sure of the consistancy of your data but you could try something like this to extract just the numbers and commas and then use split.

Sub test()

    Debug.Print ExtractString("Relationship of ditch [600], ditch [598], ditch [596]")

End Sub

Function ExtractString(StrIn As String) As String

    Dim i As Integer
    Dim x As Variant

    For i = 1 To Len(StrIn)
    
        x = Mid(StrIn, i, 1)
        
        If Asc(x) >= 48 And Asc(x) <= 57 Or Asc(x) = 44 Then
            
            ExtractString = ExtractString & Chr(Asc(x))
            
        End If
        
    Next

End Function

600,598,596

 

[MajP]

The difference is I kept the spaces and got rid of the commas
3.16 600 598 596
Moke's will require the user to always have a comma after a number or it will not work
Relationship of ditch [600], ditch [598] and ditch [596]
600,598596

 

[matt beamish]

Thanks for replies. I'm sort of confident about just getting the numbers out somehow, it is more how to get the results into just one column, and so how I get split() to loop.

From

RemoveNonNumeric(StrInput As String)

I get

but I need single values in the context column and the 'number' column repeated for as many instances as there 'context' values

 

[matt beamish]

Here is some of my data as a text file - thanks for help

 

[moke123]

Run the sub "TestSplit" in Mod1

You could also use MajP's code and split on the space.

 

[matt beamish]

When running from Immediates, I'm getting this compile error:

 

[matt beamish]

Might this be a reference library issue - perhaps I need DAO3.6?

 

[matt beamish]

I've just read that DAO (which I did used to need years ago) is effectively replaced with MIcrosoft Office 16.0 Access.....which is loaded on my machine

 

[jdraw]

Late to the party, but for whatever it may be worth, another approach to deal with "splitting" a string.

I added a record to the original data to allow a varied number of digits
3.13 Posthole [590]
3.14 Posthole [590]
3.15 Relationship of ditch [600], ditch [598], ditch [596]
3.16 Relationship of ditch [600], ditch [598], ditch [596]
3.17 testing different pattern [3456], ditch [24798], ditch [26]

' ----------------------------------------------------------------
' Procedure Name: testMatt
' Purpose: Routine in response to Matt Beamish
'     Split a string separating values between [ and ].
'     Stream may have 1 or more values separated by commas
'    ?? Numeric Values may have 3 or varied number of digits
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 01-Apr-23
'https://www.access-programmers.co.uk/forums/threads/split-text-loop.327199/
' ----------------------------------------------------------------
Sub testMatt()
          Dim rs As DAO.Recordset
          Dim sIn As String
          Dim i As Integer
          Dim inumCommas As Integer
          Dim badchars As String
10        badchars = "abcdefghijklmnopqrstuvwxyz[] "
20        Set rs = CurrentDb.OpenRecordset("mattb", dbReadOnly)
30        Do While Not rs.EOF
              'remove text and [ ] leave numbers and commma
40            For i = 1 To Len(rs!desc)
                  'single value
50                If InStr(badchars, Mid(rs!desc, i, 1)) = 0 Then
60                    sIn = sIn & Mid(rs!desc, i, 1)
70                End If
                  'multi values
80                If Mid(rs!desc, i, 1) = "," Then
                      'print the chars
90                    Debug.Print rs!DWG & " " & Mid(sIn, 1, Len(sIn) - 1)
100                   sIn = ""
110               End If
120           Next i
130           Debug.Print rs!DWG & " " & sIn
140           sIn = ""
150           rs.MoveNext
160       Loop
      
End Sub

Result:
3.13 590
3.14 590
3.15 600
3.15 598
3.15 596
3.16 600
3.16 598
3.16 596
3.17 3456
3.17 24798
3.17 26

 

[The_Doc_Man]

I have a full-blown text parser that would help you do the job using a loop within a loop. It is fairly simple as a basic tool but will still take a bit of work.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

The download is a ZIP file with a VBA class module to establish a Parser object plus a Word document describing how to use it. In overview you would have a loop structure like this:

Create a recordset for output
While {more lines in the file}
    Read an input line from whatever source you have.
    Prime the parser with the whole input line
    Parse to find & store the first digit string --- presumably 1 digit, but the parser doesn't care
    Parse to find & verify & store the "." --- if anything but a dot, badly formatting input?
    Parse to find & store the next digits string --- parser will return the digit string
    (IF you did not do so in the three previous parser steps but just stored each string separately, 
        concatenate the first digit, dot, and second digit string to become the current "Number" column content)
    While {not at end of line}             --- prepare to look for your context markers
        While {the most recent parse is NOT a "["}   --- You are looking for the start of the [nnn] sequence)
            Parse to find the next line element
            If parsed to EOL Then Exit Loop    --- Hit end of line before next "["?
            End While
        If parsed to EOL Then Exit Loop  --- Hit end of line before next "["?  note that you didn't parse, so still on same (EOL) token
        Parse to get the digit string    --- To exit the previous loop AND make it here, previous parse found "["
        Create a new record with the "Number" prefix and the current digit string, write it
        End While    --- Go back looking for another "[" UNLESS it is EOL, in which case go for another line
    End While   --- Tried to read next line but there isn't one
Close the recordset for output

Every one of the steps is only one or two lines of VBA code.

 

[moke123]

When running from Immediates, I'm getting this compile error:
View attachment 107309

It's a sub not a function.
to call from the immediate window try -

call TestSplit

or click somewhere in the procedure and press F5

 

[matt beamish]

Run the sub "TestSplit" in Mod1

You could also use MajP's code and split on the space.

I now have this running successfully. Thanks for help Moke123 and everyone else for quickly chipping in. Good to know that the Forum is still here and that all you helpful people are there to help.

 

[CJ_London]

this is a sql solution using a couple of simple functions

SELECT MGBdrawingcontextstosplit.Number, getval([description],"[",[num]) AS Expr2
FROM MGBdrawingcontextstosplit, usysCounter
WHERE (((usysCounter.num)>0 And (usysCounter.num)<=countchar([description],"[")));

Modify as required to be a make table or append query

these are the two functions

Function countChar(ByVal s As String, c As String) As Integer

        countChar = UBound(Split(s, c))

End Function


Function getVal(ByVal s As String, c As String, i As Integer) As Integer

    getVal = Val(Split(s, c)(i))

End Function

it also uses a simple 'counter' table - a single field table populated with 0-9

I linked to your.txt file as tab delimited which keeps the first value in it's own column. You can do this using the import spec file or create a schema.ini file if you want to use code.

On my machine, took about 3 seconds to return 528 records

Edit: seem to be to late!

 

[Josef P.]

An example with regex

Private Sub Test()

   Dim InsertSql As String

   Const Dwg As String = "3.15"
   Const TestString = "Relationship of ditch [600], ditch [598], ditch [596]"

   Dim Ditch As Variant

   For Each Ditch In GetDitchNumbers(TestString)
      InsertSql = "insert into YourTab (Dwg, Context) values ('" & Dwg & "', " & Ditch & ")"
      Debug.Print InsertSql
   Next

End Sub

Private Function GetDitchNumbers(ByVal StringToCheck As String) As Long()

   Static RegEx As Object ' ... RegExp

   Dim Matches As Object  ' ... MatchCollection
   Dim N() As Long
   Dim i As Long

   If RegEx Is Nothing Then
      Set RegEx = CreateObject("VBScript.RegExp") ' or with earlybinding: = New RegExp
      RegEx.Global = True
      RegEx.Pattern = "\[(\d*[^\]])\]"
   End If

   Set Matches = RegEx.Execute(StringToCheck)

   If Matches.Count > 0 Then

      ReDim N(Matches.Count - 1)
      For i = 0 To Matches.Count - 1
         N(i) = Matches.Item(i).SubMatches(0)
      Next

   End If

   GetDitchNumbers = N

End Function

 

[matt beamish]

this is a sql solution using a couple of simple functions

SELECT MGBdrawingcontextstosplit.Number, getval([description],"[",[num]) AS Expr2
FROM MGBdrawingcontextstosplit, usysCounter
WHERE (((usysCounter.num)>0 And (usysCounter.num)<=countchar([description],"[")));

Modify as required to be a make table or append query

these are the two functions

Function countChar(ByVal s As String, c As String) As Integer

        countChar = UBound(Split(s, c))

End Function


Function getVal(ByVal s As String, c As String, i As Integer) As Integer

    getVal = Val(Split(s, c)(i))

End Function

it also uses a simple 'counter' table - a single field table populated with 0-9

I linked to your.txt file as tab delimited which keeps the first value in it's own column. You can do this using the import spec file or create a schema.ini file if you want to use code.

On my machine, took about 3 seconds to return 528 records

Edit: seem to be to late!

Thanks for this which is very light weight and does just what I need to do - and you've written it into a query for me so I can adapt to all the appends I need to do which is appreciated. There are multiple tables that I have coming from field recording devices where the data is getting exported in multi value fields.

 

[Isaac]

Hi all,
long time no see from me as my job changed a bit, and I'm not doing anything near the amount of VBA I used to do. So apologies, but I am just a bit rusty and would appreciate a hand.

I need to split a field that is consistently delimited by commas, and contains numeric strings between the commas. which I need to append into another table along with another field.

I have incorporated Allen Browne's ParseWord function
Parseword()
into a db, and by using this in a query, and then applying the Int() function
Int()
to the results, I get what I need.

However, Allen Brownes adaptation of Split() requires the position of the string to be extracted from the overall string ("ByVal iWordNum As Integer") but I need to loop through the complete string and break all the data into chunks. In the query I have am needing to specify postion 1, position 2, position 3 etc for the outputs, but this means guessing the number of postential outputs, and the number of values separated by commas in the field Description is not constained.

Here is some example data as an input, and the output I need

Input

Dwg Description
3.13 Posthole [590]
3.14 Posthole [590]
3.15 Relationship of ditch [600], ditch [598], ditch [596]
3.16 Relationship of ditch [600], ditch [598], ditch [596]

Output

Dwg Context
3.13 590
3.14 590
3.15 600
3.15 598
3.15 596
3.16 600
3.16 598
3.16 596

So I need to adapt the ParseWord() or Split() function to loop through all my data, with all the extracted values in one column. Might anyone help me with this please? I always struggled with loops

thanks

Use combinations of Split assign to array
dim str() as string

 

[arnelgp]

another alternative, run fnSplitTheDitch on Module1.
the "splitted" records will be saved to Output table.