Solved

Recieving a delimited string via RS232 port and split by CR/LF

Posted on 2003-10-22
17
782 Views
Last Modified: 2012-05-04
I'm trying to write a delimited string to a MySQL database.  The incoming string looks like this:

161003 1247 00056 9                               2889  801 02 1924279178       4     0{cr-lf}161003 1247 00056 9                               2889  801 02 1924279178       4     0{cr-lf} etc... etc... etc...

The repitition is varried, some times 4 lines could be outputted some times 2 or 6 etc.

I'd like to break this incoming data into strings and write them to the db, i've got the database bit figured out I just need to figure out some way to split the data and loop through inserting each line at a time into the DB.

I'm not very good with this stuff so if some one had any examples, that would help loads.

Could anyone please help figure out how I do this? :-)
Thanks
Tim
0
Comment
Question by:Tim_mj22
  • 7
  • 3
  • 3
  • +3
17 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9601030
let me see...

0
 

Expert Comment

by:eliaslopezgtz
ID: 9601130
try using the split function in an array spliting with spaces
then debug the array keeping only the items that are no spaces
and then you have an array filled with all the important data,
see if there is some pattern to put together some items in the array
in another array...
for example...
items 1, 2 and 3... into item 1 on the second array.
0
 
LVL 3

Expert Comment

by:TheQuestion
ID: 9601184
Can you Delimate  the info with  a pipe or something  |  and then you can use the split
dim Fun as Variant
fun = Split(Value,"|")

Then you can refeence the data by Fun(NumOfInfo)
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 9601366
How about if you use mscomm control

global section
public Tmp as string


To get str from mscomm

Tmp = Tmp & MSComm1.Input

Process()


private sub process()
dim arr, i

If (Tmp = "") Then Exit Sub
arr = Split(Tmp, vbCrLf)
For i = 0 To UBound(arr) - 1
' add arr(i) to database
Next
Tmp = arr(UBound(arr))
0
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 500 total points
ID: 9601399
Here is an example of how to parse an input line:

Create a new project and add a listbox to it.  (The location and size does not matter as it will simply get resized)

Paste this code into the form and run the project.  Hope it illustrates the concept well enough.  The code could obviously be made a bit more efficient, but it does the job.

Private Sub Form_Load()
    Dim inputString As String
   
    Dim splitParts As Variant
    Dim lowerbound As Integer
    Dim upperbound As Integer
    Dim a As Integer
    Dim b As Integer
   
    Dim inputLines As New Collection
    Dim inputWords As New Collection
   
    Dim token As String
    Dim words As String
    Dim collectionItem As Variant
       
    inputString = "161003 1247 00056 9                               " & _
        "2889  801 02 1924279178       4     0" & vbCrLf & "161003 12" & _
        "47 00056 9                               2889  801 02 192427" & _
        "9178       4     0" & vbCrLf
   
    ' Parse into seperate lines
    splitParts = Split(inputString, vbCrLf)
    lowerbound = LBound(splitParts)
    upperbound = UBound(splitParts)
    For a = lowerbound To upperbound
        token = Trim(splitParts(a))
        If Len(token) > 0 Then
            inputLines.Add token
        End If
    Next
   
    List1.AddItem "Number of Lines: " & inputLines.Count
    List1.AddItem ""
    For a = 1 To inputLines.Count
        List1.AddItem "Line " & a & " Raw Data: " & inputLines.Item(a)
        ' Parse each line into seperate words
        Set inputWords = New Collection
        splitParts = Split(inputLines.Item(a), " ")
        lowerbound = LBound(splitParts)
        upperbound = UBound(splitParts)
        For b = lowerbound To upperbound
            token = Trim(splitParts(b))
            If Len(token) > 0 Then
                inputWords.Add token
            End If
        Next b
        words = ""
        For Each collectionItem In inputWords
            If words = "" Then
                words = collectionItem
            Else
                words = words & ", " & collectionItem
            End If
        Next collectionItem
        words = "Line " & a & " had " & inputWords.Count & " Words: " & words
        List1.AddItem words
        List1.AddItem ""
    Next a
End Sub

Private Sub Form_Resize()
    List1.Move 0, 0
    List1.Width = Form1.Width
    List1.Height = Form1.Height
End Sub
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9601429
What a stupid i am! EDDYKT is right but since you already have the data, just use:
arr= split(sIncommingStr,vbcrlf)
and contine with EDD's code.
0
 

Author Comment

by:Tim_mj22
ID: 9608524
Another twist in the tail...

Sorry about this but I was trying to implement both idle_minds and EDD's code, but when I ran the VB app I noticed that my output was not displaying correctly after further looking into it, for some reason my application does not receive the string as

161003 1247 00056 9                               2889  801 02 1924279178       4     0{cr-lf}161003 1247 00056 9                               2889  801 02 1924279178       4     0{cr-lf} etc... etc... etc...

Basically I've setup a TimerPort to listen on the Comm 1 port and this keeps looping (please see code example below) but for some reason it only seems to be getting some of the information for example..

It loops around if there is something in the buffer then it writes the input it to the file and also displays in a list box, but this information may be:-

161003 1247 00056 9                               2889  801

It then carries on going around in the loop and the next part will then be in the buffer as so

02 1924279178       4     0{cr-lf}

If I was writing to a file only then this wouldnt be a problem as this will appear as

161003 1247 00056 9                               2889  801 02 1924279178       4     0{cr-lf}

But as I'm trying to write this to a database it's going into the database as seperate entries...

Could anyone tell me where I'm going wrong I'm really at my limits with VB on this and am desperate for help?

=====Sample Code=====
If myportopen Then
   'If there is something in the buffer then..
    If GPort.InBufferCount > 0 Then
        On Error Resume Next
       'Get the input from the serial port
        gi$ = GPort.Input
        If Len(gi$) Then
                   
            LastInput = Timer
         
            FileOpen
           'Add the information to the file
            Print #oplfn, gi$;
           'Display the information to the screen
            txtResponse.SelText = gi$
                                                       
        End If
    End If
   
End If
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 9608666
Try this

to simply what i had

Private Sub GPort_OnComm()
    Static Tmp As String
    Dim Arr, I
   
    On Error Resume Next
    Select Case MSComm1.CommEvent
        Case comEvReceive
            MSComm1.InputLen = 0
            If MSComm1.InBufferCount Then
                Tmp = Tmp & GPort.Input
                If (Tmp) Then
                    Arr = Split(Tmp, vbCrLf)
                    For I = 0 To UBound(Arr) - 1
                        FileOpen
                        'Add the information to the file
                         Print #oplfn, Arr(I)
                        'Display the information to the screen
                         txtResponse.SelText = Arr(I)
                    Next
                    Tmp = Arr(UBound(Arr))
                End If
            End If
        Case Else
       
    End Select
End Sub
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9608698
Ok, no problem...

Receving data in chunks is typical in "streaming data" type programs.  All you have to do is add your incoming data to a temp variable each time there is something sitting in your buffer.  Then, check each time for the presence of a vbCrLf character in the temp variable indicating that you have received an entire line of data.  Then you simply extract the complete line and pass it to a parsing function like the one I wrote, and leave any remaining data in the temp variable to be appended by any subsequent data received in the buffer.

Create a global string to hold your buffer data:
dim bufferData as string

then each time you receive data, append it to your buffer:
bufferData = bufferData & gi$

now look for the marker:
dim markerPos as integer
dim completeLine as string

markerPos = InStr(1, bufferData, vbCrLf)
If markerPos > 0 Then ' Found a marker
        ' Extract complete line from buffer
        completeLine = Mid$(bufferData, 1, markerPos - 1)

       'now do something with completeLine......

        ' Reset buffer to what's after the marker
        bufferData = Mid$(bufferData, markerPos+ 2) ' leaves out vbCrLf
End if

Good luck!
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9608842
Another caveat....

You can't simply split the buffer using vbCrLf and assume that all resulting strings are complete lines.  Try this example to convince yourself of this:

Private Sub Form_Load()
    Dim a As String
    a = "abcd" & vbCrLf & "efgh" & vbCrLf & "ij"
   
    Dim b As Variant
    b = Split(a, vbCrLf)
   
    Dim c As Integer
    For c = LBound(b) To UBound(b)
        Debug.Print c & ": " & b(c)
    Next c
End Sub

The last string in the array is "ij" but it does not end in a vbCrLf so it is not yet a complete line!

My code is actually incomplete in this respect since it could leave a complete line in the buffer unprocessed as I only extracted the first complete line found and it is possible to receive more than one complete line in the same chunk of data.

To remedy this type of situation you would have to send the entire buffer after each receive to a seperate function and then continue extracting complete lines from the buffer until no more vbCrLf's are found in it.

You would still use this type of convention:

markerPos = InStr(1, bufferData, vbCrLf)
If markerPos > 0 Then ' Found a marker
        ' Extract complete line from buffer
        completeLine = Mid$(bufferData, 1, markerPos - 1)

       'now do something with completeLine......

        ' Reset buffer to what's after the marker
        bufferData = Mid$(bufferData, markerPos+ 2) ' leaves out vbCrLf
End if

except that you would have to put the whole thing into some kind of while loop until no more vbCrLf's are found.

Hope this helps...
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9608962
So you receive more data and add it to your buffer:
gi$ = GPort.Input
bufferData = bufferData & gi$

then process all complete lines in it with a loop:
Do
    markerPos = InStr(1, bufferData, vbCrLf)
    If markerPos > 0 Then ' Found a marker
        ' Extract complete line from buffer
        completeLine = Mid$(bufferData, 1, markerPos - 1)

       'now do something with completeLine......

        ' Reset buffer to what's after the marker
        bufferData = Mid$(bufferData, markerPos + 2) ' leaves out vbCrLf
    End If
Loop While markerPos > 0 ' go back and process all complete lines in buffer

Sorry for posting 3 messages in a row.
0
 

Author Comment

by:Tim_mj22
ID: 9609611
Thanks for your speedy response Idle_Mind I've implement your proposed solution first into my app (See below) but won't be able to try this until tomorrow, I'll let you know how I get on ASAP. Thanks for your help :-)

====Sample Code====
Private Sub timerPort_Timer()

Dim markerPos As Integer
Dim completeLine As String

If myportopen Then
    If GPort.InBufferCount > 0 Then
        On Error Resume Next
        gi$ = GPort.Input
        If Len(gi$) Then
           
            LastInput = Timer
           
           
       
            FileOpen
            Print #oplfn, gi$;
           
            bufferData = bufferData + gi$
           
            Do
                markerPos = InStr(1, bufferData, vbCrLf)
            If markerPos > 0 Then ' Found a marker
                ' Extract complete line from buffer
                completeLine = Mid$(bufferData, 1, markerPos - 1)
               
                ' Output the complete line to my list box
                List1.AddItem "Line :" & completeLine
               
                ' Reset buffer to what's after the marker
                bufferData = Mid$(bufferData, markerPos + 2) ' leaves out vbCrLf
            End If
           
            Loop While markerPos > 0 ' go back and process all complete lines in buffer
           
        End If
    End If
   
End If



If oplfn > 0 Then
    Ctimer = Timer
    If Ctimer < LastInput Then
        LastInput = Ctimer
    End If
   
    If Ctimer - LastInput > 15 Then
        FileClose
    End If
End If
If timerStatus.Interval = 0 Then
    timerStatus.Interval = 1000
End If
End Sub

0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 9613347
>>The last string in the array is "ij" but it does not end in a vbCrLf so it is not yet a complete line!


Doesn't matter both method will work
I just try to pt out the last message is always put back into my tmp buffer

if the last message is completed, tmp will be equal to ""


For I = 0 To UBound(Arr) - 1
      'Display the information to the screen
     Next
                    Tmp = Arr(UBound(Arr))   ' this line will take care of it


It is simple and straight forward, No checking is needed

8->
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9614297
Your absolutely right EDDYKT.  Your code works great and I didn't mean to imply that it wouldn't.  I was simply showing a pitfall of using the split function.  You carefully wrote your For loop to handle the case!

I personally like both methods but have found that most people can visualize what is happening to the buffer better when the InStr function is used.

Tis all just a matter of coding style and preference. =)
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9636298
Just wondering how development was coming along.  =)
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9688088
Have you made any progress Tim?
0
 

Author Comment

by:Tim_mj22
ID: 9692738
sorry not been back, I've been so busy jut not had time. Thank you all so much and a big thank you to Idle_Mind for helping me out so much :-)

Thanks, kind regards,

Tim
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now