Help with strings

N

New2VB

Registered User.
Local time
Today, 20:21
Joined
Jun 9, 2010
Messages
131
Greetings Gurus,

I have run out of ideas on how to perform some string manipulations and am hoping you may help. I am trying to extract substrings from a logfile and insert them into my db.

Example strings:
12:14:56 - bob - logon - bobspc - 192.168.0.1 - 192.168.0.2 - thisserver - 1,2,3,4,5
12:15:11 - harry - logoff - harryspc - 1.12.13.15 - 192.168.0.5 - theotherserver - 1,8,11,13,100
so, the only thing that the strings have in common is that the substrings are "delimited" by hyphens (-).

I have tried using InStr and Mid functions but I either chop off some text that I want or overflow into the next substring.

What I am trying to achieve on my form is:
Text1 = time
Text2 = name
Text3 = event
Text4 = source
Text5 = logonIP
Text6 = serverIP
Text7 = servername
Text8 = whatever

Any ideas please?
 
There are a couple of ways to address an issue like this. Please give us examples of what you have tried and tell why it did not work, so that we can determine whether we can help you fix the solution that you are trying.
 
Using the Import & Export wizard will allow you to specify a delimeter during the import process.
 
@vbaInet - the data comes from a nvarchar field in a SQL db which is populated from an email body, no import export facility, just a control source or record source
@MSAccessRookie - I started going along the lines of (swMessage being the filed name):
"swName = Mid(swMessage, InStr(33, swMessage, "-", vbTextCompare), 25)" and ""swEvent = Mid(swMessage, 35, 20)" but because the substrings are not all the same length bits get chopped off or added on. I need to somehow turn them into variable-length strings (for want of a better term).
 
But you said the records reside in a log file. Isn't this log file a text file?
 
Sorry if I didn't explain properly, the "logfile" is emailed to a mailbox. It is then harvested into a SQL db table. Using Access 2003 with that table as a control source, I am trying to split the string into several fields.

I think the principal is the same, but I should have mentioned that the source wasn't a .log (or similar)file. My apologies vbaInet.
 
Once you have a single line of data in a string variable, e.g DataLine try:

Code: 
Dim FieldEntries As Variant
FieldEntries = Split(DataLine, "-")

The Split() function will create an Array of data from a string of delimited data.

For the line "12:14:56 - bob - logon - bobspc - 192.168.0.1 - 192.168.0.2 - thisserver - 1,2,3,4,5"

You would get an Array:
FieldEntries(0) = "12:14:56 "
FieldEntries(1) = " bob "
FieldEntries(2) = " logon "
FieldEntries(3) = " bobspc "
FieldEntries(4) = " 192.168.0.1 "
FieldEntries(5) = " 192.168.0.2 "
FieldEntries(6) = " thisserver "
FieldEntries(7) = " 1,2,3,4,5"

However the entries may have leading and trailing spaces so you will need to use the trim$() function to get rid of them when you use the data.

The following code would ennumerate the array.

Code: 
Dim strMsg as string, varArrayEntry as variant

strMsg = "FieldEntry Array:" & vbCrLf

For each varArrayEntry in FieldEntries
    If strMsg <> "" Then strMsg = strMsg & vbCrLf
    strMsg = strMsg  & trim$(varArrayEntry)
Next varArrayEntry

MsgBox strMsg


I've learnt something new as well. ;)
 
Last edited:
New2VB said:
Greetings Gurus,

I have run out of ideas on how to perform some string manipulations and am hoping you may help. I am trying to extract substrings from a logfile and insert them into my db.

Example strings:
12:14:56 - bob - logon - bobspc - 192.168.0.1 - 192.168.0.2 - thisserver - 1,2,3,4,5
12:15:11 - harry - logoff - harryspc - 1.12.13.15 - 192.168.0.5 - theotherserver - 1,8,11,13,100
so, the only thing that the strings have in common is that the substrings are "delimited" by hyphens (-).

I have tried using InStr and Mid functions but I either chop off some text that I want or overflow into the next substring.

What I am trying to achieve on my form is:
Text1 = time
Text2 = name
Text3 = event
Text4 = source
Text5 = logonIP
Text6 = serverIP
Text7 = servername
Text8 = whatever

Any ideas please?
Click to expand...

Hi.. ;)


Put this function in a module..:

Code: 
'Author:    Allen Browne. http://allenbrowne.com. June 2006.
Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer, Optional strDelimiter As String = " - ", _
    Optional bRemoveLeadingDelimiters As Boolean, Optional bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler

    
    Dim varArray As Variant     'The phrase is parsed into a variant array.
    Dim strPhrase As String     'varPhrase converted to a string.
    Dim strResult As String     'The result to be returned.
    Dim lngLen As Long          'Length of the string.
    Dim lngLenDelimiter As Long 'Length of the delimiter.
    Dim bCancel As Boolean      'Flag to cancel this operation.

    '*************************************
    'Validate the arguments
    '*************************************
    'Cancel if the phrase (a variant) is error, null, or a zero-length string.
    If IsError(varPhrase) Then
        bCancel = True
    Else
        strPhrase = Nz(varPhrase, vbNullString)
        If strPhrase = vbNullString Then
            bCancel = True
        End If
    End If
    'If word number is zero, return the whole thing and quit processing.
    If iWordNum = 0 And Not bCancel Then
        strResult = strPhrase
        bCancel = True
    End If
    'Delimiter cannot be zero-length.
    If Not bCancel Then
        lngLenDelimiter = Len(strDelimiter)
        If lngLenDelimiter = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Process the string
    '*************************************
    If Not bCancel Then
        strPhrase = varPhrase
        'Remove leading delimiters?
        If bRemoveLeadingDelimiters Then
            strPhrase = Nz(varPhrase, vbNullString)
            Do While Left$(strPhrase, lngLenDelimiter) = strDelimiter
                strPhrase = Mid(strPhrase, lngLenDelimiter + 1&)
            Loop
        End If
        'Ignore doubled-up delimiters?
        If bIgnoreDoubleDelimiters Then
            Do
                lngLen = Len(strPhrase)
                strPhrase = Replace(strPhrase, strDelimiter & strDelimiter, strDelimiter)
            Loop Until Len(strPhrase) = lngLen
        End If
        'Cancel if there's no phrase left to work with
        If Len(strPhrase) = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Parse the word from the string.
    '*************************************
    If Not bCancel Then
        varArray = Split(strPhrase, strDelimiter)
        If UBound(varArray) >= 0 Then
            If iWordNum > 0 Then        'Positive: count words from the left.
                iWordNum = iWordNum - 1         'Adjust for zero-based array.
                If iWordNum <= UBound(varArray) Then
                    strResult = varArray(iWordNum)
                End If
            Else                        'Negative: count words from the right.
                iWordNum = UBound(varArray) + iWordNum + 1
                If iWordNum >= 0 Then
                    strResult = varArray(iWordNum)
                End If
            End If
        End If
    End If

    '*************************************
    'Return the result, or a null if it is a zero-length string.
    '*************************************
    If strResult <> vbNullString Then
        ParseWord = strResult
    Else
        ParseWord = Null
    End If

Exit_Handler:
    Exit Function

Err_Handler:

    Resume Exit_Handler
End Function


use this way..:

Text1 control source =parseword([field_name],1)
Text2 control source =parseword([field_name],2)
Text3 control source =parseword([field_name],3)
Text4 .....
...................
...........................
 
How huge is the file?

You could use the code above to split it and run an APPEND query from it.
 
nanscombe & Taruz, that's great thank you.
@vbaInet - the "file" isn't very big at the moment but it is growing at roughly 10 entries per hour

nanscombe, I'm trying your method but I'm getting an odd result,

I'm doing...
Code: 
Private Sub Form_Load()' I have tried _Open & _Current as well 
Dim dataline As String
    dataline = swMessage
Dim FieldEntries As Variant
    FieldEntries = Split(dataline, "-")
Dim varArrayEntry As Variant
    For Each varArrayEntry In FieldEntries
        Me.swDTime = FieldEntries(0)
        Me.Name = FieldEntries(1)' etc,etc to FieldEntries(7)
    Next varArrayEntry
End Sub
but all the entries are the same when viewed in datasheet, cont. forms or single forms. If I pull up an auto-form from the table then I get the proper values.

I am using unbound textboxes to insert the values and I haven't seen this behaviour before. Any ideas?
 
Still only use the code to split the fields and save it into your table.
 
Thank you guys, got that part working but I have come across a problem with one of the array fields (field no 4)

Field 4 usually look like 192.168.x.y, aa, bb, cc for which I have used the commas to split the field and allocate each value in the array listing
Code: 
Dim cma, cmaArray As Variant
For Each cmaArray In cma
    tb1!swSourceName = cma(3)
Next cmaArray
and this works perfectly.

However, I occasionally get a Field 4 which looks like 192.168.x.y, aa, bb. This means that cma(3) doesn't exist so the code errors.

I have tried "bypassing" the error using
Code: 
For Each cmaArray In cma
    If isNull(cma(3)) then // also tried IsNotNull
    tb1!swSourceName = cma(0)
    Else
    tb1!swSourceName = cma(3)
    End If
    Next cmaArray
but because cma(3) doesn't even exist it can't be null. I have tried using cmaArray.Count in order to count the amount of array entries so that I could use something like:-
If cmaArray.Count <3 then
tb1!swSourceName = cma(0)
else
tb1!swSourceName = cma(3)
but can't get it to work.

Could you help please?
 
It may be better to use
Code: 
If Ubound(cmaArray) <3 then
tb1!swSourceName = cma(0)
else
tb1!swSourceName = cma(3)
End If

Or possibly even

Code: 
tb1!swSourceName = cma(0)
If Ubound(cmaArray) >=3 then tb1!swSourceName = cma(3)

Lbound(ArrayName) - Returns a Long containing the smallest available subscript for the indicated dimension of an array.

Ubound(ArrayName) - Returns a Long containing the largest available subscript for the indicated dimension of an array.
 
Last edited:
Hi again Nigel,

Sorry, doesn't work. The problem is that, if cma(3) doesn't exist, the code does nothing.

I.e. if the string is 192.168.x.y, aa, bb, cc & I do this
Code: 
MsgBox "0: " & cma(0) & "  1 :" & cma(1) & "  2 :" & cma(2) & "  3: " & cma(3)
then it returns 0: 192.168.xx.yy 1: aa 2: bb 3: cc
but if the string is 192.168.x.y, aa, bb then I don't even get a message box.

Do you get such a thing as:-
If NotExist(cma(3)) then swSourceName = cma(0)?

or
Iif(IsError(swSourceName,cma(0),swSourceName)?
 
nanscombe said:
It may be better to use
Code: 
If Ubound(cmaArray) <3 then
tb1!swSourceName = cma(0)
else
tb1!swSourceName = cma(3)
End If
Or possibly even

Code: 
tb1!swSourceName = cma(0)
If Ubound(cmaArray) >=3 then tb1!swSourceName = cma(3)
Lbound(ArrayName) - Returns a Long containing the smallest available subscript for the indicated dimension of an array.

Ubound(ArrayName) - Returns a Long containing the largest available subscript for the indicated dimension of an array.
Click to expand...
Or even:
Code: 
tb1!swSourceName = cma(Abs(Ubound(cmaArray) >=3) * 3)
 
Smaller and smaller ... I hope you aren't descended from the oozlum bird? :D

The oozlum bird, also spelled ouzelum, is a legendary creature found in Australian and British folk tales and legends. Some versions have it that, when startled, the bird will take off and fly around in ever-decreasing circles until it manages to fly up itself, disappearing completely, which adds to its rarity.
Click to expand...
 
vbaInet said:
It sounds like a very interesting bird. I would like to see it in action :D
Click to expand...

Even Frankie Howard didn't manage that in Carry On Up The Jungle. :D

Anyhow back to the coding ...

Code: 
tb1!swSourceName = Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3))

Looks like we seem to have missed that cmaArray was the array name rather than cma. :rolleyes:
 
You must log in or register to reply here.

Similar threads

B
Insert Into Statement problem
Replies
4
Views
362
static
S
J
Access Crashes after sending E-Mail
Replies
5
Views
1,284
jco23
J
rio
subform filter
Replies
7
Views
1,323
Galaxiom
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom