Solved

read data stream. from aaa.bbb.ccc.ddd port e in vba and convert to text file

Posted on 2014-01-17
27
2,072 Views
Last Modified: 2014-01-25
Hi all knowing experts,
I have 4 PCs that are logging events and basically streaming these in text format from an ip address and a specific port.  I would like another pc on same LAN to connect to each of these ports and create 4 expanding text files.   After 24 hours I want to close each file and start again.
All of this needs to happen in vba if at all possible, although I am open to creating a vb project.
I have done some investigation around net and it seems there are a couple of ways to do this :

Winsock control
Windows sockets
Tcpclient
Udpclient

Cant remember if its udp or tcp transmission.

As stated this does not have to be particularly clever, just create a text file.

I have implemented a solution using telnet to file but my batch file is temperamental and I feel with vba in excel or word I can autoformat as it arrives making a much neater solution.

Most of the code I have seen seems to be too expansive, as stated I just want to replicate what telnet is doing.

Any pointers for a very simple listener really really appreciated.

Kind regards

Clay
0
Comment
Question by:clay258
  • 16
  • 11
27 Comments
 
LVL 2

Author Comment

by:clay258
ID: 39790564
By the way, I have found out most machines either Win XP or Win 7 judging by the PCs in my house don't have MSWinsock.ocx installed as default so how do I get that and package it for deployment?

Ultimately the stream of data is ASCII text that will be formatted into csv and parsed into MS Access fields or MS Excel Cells.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39790573
In this excellent article:

"Create a Syslog Sender/Receiver Using the MS Winsock Control"

http://www.devx.com/vb/Article/42242

you'll see a link on page 1 "several Places". The link is still live.
I don't know if it will work with Access 2013, but it could be worth to try.

The demo uses the default port for syslog, but you can easily change that.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39790887
Hi,

Just spent a few hours trying to track down the winsock ocx and to remove the license issue.  This is harder than it looks.  While I persevere with that is it possible to use the system.net.sockets methods  within vba?

Backdooring the winsock seems underhand if there is a system component that I can program easily to accept connections from within excel or access.

I have bookmarked the devx page that tutorials the syslog article so I can reverse engineer what they have done.

For instance, I am presuming that the code at top of page 2 will allow me to connect to my IP address and port and as it stands, each time the DataArrival fires it would show the received bytes.  A simple append via ampersand would allow the box to accumulate the data?  Not a lot of code so a great start from that point of view.

The next problem I foresee is that each of my 4 senders have different IP addresses but same port.  How do I capture each independently?

Ta

Clay
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39791025
I can't tell. The WinSock control is pre Windows Vista so it may require a little tweaking with user rights, and will only work with 32-bit Access.

You could probably create a dll using system.net.sockets and reference that from VBA.

One receiver will listen to one port and accept data from many sources. However, using UDP as syslog does, is "one-way" only so while it is extremely fast, you will have no confirmation. That is discussed in the article, and a simple method is to send everything three times, another is to use TCP.

Creating the four files is really simple. You need to identify the sender (IP address) and then write to the file and append a CrLf. The syslog demo writes to a database but that isn't much different. It should be easy to adopt. Or you could keep the database and then filter and export the data to the text files when needed.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39791905
Hi Gustav,

I dont have an issue with user rights, these are all closed systems that I manage and after the tweaks I made to my telnet batches, the data is being captured.  Now to do it more elegantly and parse it into a database too.

So I have finally loctaed winsock and got it to ignore the license error and ready to start programming.  I also found this on the net for examples within Access 2k:
 http://support.microsoft.com/kb/209905

So I think I am well on my way now to sorting out a little prog within Access to catch and parse into fields.

Many Thanks,

Will let you know how I get on but it may take all week as this is essentially a little side project....  I am also in UAE so replies take a while with time zone differences.

Clay
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39792056
OK, fine. That was the tricky part.

You may still be able to use the application from DevX. I guess you could just replace the syslogencode and -decode function with those of yours.

Also, be aware of the Unicode issue:
Option Compare Database
Option Explicit

Private Sub btnSend_Click()
  
  Dim strPackage  As String
  
  strPackage = SyslogPackageEncode()
  Me!Winsock0.SendData CStr(strPackage)

End Sub

Open in new window


Figure 2. Test Form to Send a Syslog Message: This is the simple test form to send a syslog message with the current time.


This is the minimal code to create a form for sending a syslog message. Note the missing equal sign following SendData. But why, you may ask, is CStr used? The variable strPackage is a string, so what's the deal? The reason is that Access handles strings as Unicode, thus if you passed the string variable directly like this:

Me!Winsock0.SendData strPackage

The WinSock control would send a Unicode byte stream when a clean ASCII byte stream is what you expect. CStr converts the Unicode string variable to a simple ASCII byte stream. For testing, you could send any string. However, by calling the function SyslogPackageEncode from the basSyslog module, you generate a fully qualified syslog message with the current time and default values for all other parameters, for example:  
Figure 3. Configure a Typical Router to Send Out Syslog Messages: Here is how to configure a router to send syslog messages.
/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39792149
Hi Gustav,

Dont forget my 'servers' are already transmitting this data on a specific IP Address and Port, so I just need 4 instances of client each collecting data and parsing it into something I can manipulate.

MY pseudo code would go something like this:

A    Start time check

B    Connect to IP A port E
C    Connect to IP B port E
D    Connect to IP C port E
E    Connect to IP D port E

F    On trigger of DataArrival for A, convert to ascii (using CSTR as shown above) add a Cr Lf and append to FileA.txt
G    Do same for B, C, D, and append to FileB,C,D.txt

H    24 hours complete?
I     Start a new capture for 4 files (so we dont lose data)
J    Close all 4 previous and rename as DateFileA,B,C,D
K   Read in each line from DateFileA and extract data for each specific field (using rules to be defined which should be reasonably simple as we know what each line should contain and how it is delimited) and import into accdb table
L    Repeat for DateFileB,C,D

M    Loop


How does that sound?

Clay
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39792208
I'm not sure. F sounds like each of your four machines initiates the transmission to a given IP address and deliver data; then starts collecting new data and, later, opens the transmission again to deliver, and so on.
If so, you need only one receiving server (one address, one port) which sits waiting for the four machines to call. This is much like syslog operates.

However, if the four machines are real servers, they don't do anything before called.
If so, item F must be changed, so that your collecting machine at some points calls the four machines one by one and retrieves data, writes data, waits some time, then call each machine, and so on.

In neither case I see a reason to use different ports.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39792604
Ok

I will try one step at a time by receiving from 1 server and getting that to work then progress onto 2, 3 and 4 once I have proven the steps with just 1 machine.

I should be able to accept at least 2 streams at a time because I already do that with a telnet session.

Presumably a separate winsock connected TCP will be able to collect the data from each source or I am in multithreading territory there...?

Oh well time to start coding... well tomorrow anyway.

Thanks for the guidance so far.

Clay
0
 
LVL 2

Author Comment

by:clay258
ID: 39798986
Hi,

So some quite quick success.  I created a server and client form in Access. Populated with IP Address, Port, received message and send message text boxes. A connect cmd, send cmd and disconnect cmd buttons.

The only difference between the 2 being connect was on client replaced with Listen on Server.  So opening server first set it to listen, connected with client.  then able to send messages between the 2.  --- SUCCESS.

So next I applied my rationale.  All 4 servers in the real world are setup to send message streams already so I just need a passive client , who once connected, just reads the incoming data and saves to file and after 24 hours starts a new one.

I forgot to bring the code with me but essentially, my winsock client has 3 routines, one to connect, one to collect data, parse to a textbox and then when it reaches a certain size parse to a .txt file, and one form load to see if the file exists and if it does rename it to something related to date and create a new file.

So the concept works for one winsock and one server.  Next step is to expand this to all 4 servers.  So do I create 4 instances of form or 1 form with 4 winsocks?

Next,  and more importantly, I am not doing any error checking or failure trapping.  What connection/disconnection errors should I look for?  Is there any state that the client could get into that would affect the server?  If so what is it and how would I mitigate.  I want this client to be as a passive as possible with little or no replies going back to server other than the initial connect.

On Thursday I will post my code for the benefit of anyone else who steps into the world of winsocks.

Regards,
0
 
LVL 2

Author Comment

by:clay258
ID: 39799002
Man I must have been tired last night, as I did save the code to stick before leaving here it is:

Option Compare Database

Private Sub cmdConx_Click()
   txtIP.SetFocus
   sockMain.RemoteHost = txtIP.Text
   txtPort.SetFocus
   sockMain.RemotePort = txtPort.Text
   sockMain.Connect
End Sub

Private Sub cmdDis_Click()
sockMain.Close

End Sub

Private Sub cmdSend_Click()
   txtSendText.SetFocus
   sockMain.SendData txtSendText.Text
End Sub

Private Sub Form_Close()
sockMain.Close

End Sub

Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   Dim tmpData As String
   FNold = "C:\Documents and Settings\All Users\Documents\DataCapture\DataCap.txt"
   sockMain.GetData strData, vbString
   txtStatus.SetFocus
   txtStatus.Text = txtStatus.Text & strData & vbCrLf
   If Len(txtStatus.Text) > 1024 Then
     tmpData = txtStatus.Text
     Open FNold For Append As #1
     Write #1, tmpData
     Close #1
     txtStatus.Text = ""
   End If
End Sub

Private Sub Form_Load()
  
Dim fso As Object
Dim fsoFile As Object
Dim FNold As String
Dim FNnew As String

FNold = "C:\Documents and Settings\All Users\Documents\DataCapture\DataCap.txt"
'*******Check whether output file exists**********
If Len(Dir(FNold)) = 0 Then
'*******This does not exist so create it**********
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set fsoFile = fso.CreateTextFile(FNold, True)
  fsoFile.Close
Else
'*******File exists so rename it**********
  FNnew = Left(FNold, (Len(FNold) - 4)) & "-" & Day(Now) & Month(Now) & Year(Now) & "-" & CStr(Hour(Now)) & CStr(Minute(Now)) & ".txt"
  Name FNold As FNnew
'*******Now Create a new one**********
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set fsoFile = fso.CreateTextFile(FNold, True)
  fsoFile.Close
End If

End Sub

Open in new window


Connect Sub
using 2 text boxes for IP and Port
Then simple call to winsock method

Disconnect Sub
simple call to winsock to close port  (don't know if this is the best way to do this but all I could find)

Send Sub
Not required any longer but shows how a text box can be used to send a message

DataArrival Sub
sets up a couple of variables to tidy the received data before first displaying in the status text box.  Then once the data has mostly filled the box, it then sends it to file (hopefully with the extra line breaks included).  Important to close the file each time.
Clear the text box and wait for next set of data

Form Load Sub
Just a test of concept.  Ultimately this will become a function triggered every 24 hours to see if its the first time run or subsequent.  If first time then create datacap.txt as the file for storage, otherwise rename it as 24 hours has lapsed and we need to create a new one for the next 24 hours.

And there you have it.

As stated above, now I want to make it bullet proof and ensure the client doesn't do anything to disrupt the server by handling any exceptions.  This is where in depth knowledge from an expert is essential.  What do I need to look out for and how would I trap it and mitigate any errors?

Many thanks,

Clay
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39799283
Great!

I still believe one receiver will do. It is extremely unlikely that two or more of the senders will send at the same time. At the receiver, each sender can be identified by their IP address.

As for error handling, you will need some at the sender in case it cannot connect, and if you use UDP you may have to send each message several times as UDP packets are not guarantied to reach the receiver. With TCP, however, you first establish a connection and only then you start transmission.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39799628
Okay,

I have read a bit about multi receive by setting up an array of sockets.  I presume a slight change such as :

'*************This to connect to multiple servers stored in Access Table *******
Private Sub cmdConx_Click()
dim db as database
dim rs as dao.recordset
dim socket as integer

set db= currentdb
set rs= "Select * FROM tblServer", dbdynaset)
socket = 0
rs.MoveFirst
do while not rs.eof

   sockMain(socket).RemoteHost = rs![IPAddr]
   sockMain(socket).RemotePort = rs![IPPort]
   sockMain(socket).Connect
socket = socket +1
rs.MoveNext
Loop

Open in new window


Sorry if that's slightly wrong but that's access code written from memory but serves the purpose.

Now the tricky part... How do I determine which server is talking to me if I have four sockets now waiting for data called
sockMain(0)
sockMain(1)
sockMain(2)
sockMain(3)

How do I determine within my dataArrival Code which one has arrived?

Is it 4 subs such as :
Private Sub sockMain(0)_DataArrival(ByVal bytesTotal As Long)
.....
End Sub

AND
Private Sub sockMain(1)_DataArrival(ByVal bytesTotal As Long)
....
End Sub
 
etc

I will carry on looking but any pointers gratefully received...

Ta

Clay
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 49

Expert Comment

by:Gustav Brock
ID: 39799649
You should use one port only. To listen to several ports, you would need as many receiver instances running.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39799695
Okay

so how does that look in code.....

Do I raise 4 connects as shown above or 4 data receives and if so how do I know which is which?  In the TCP/IP data header will be a MAC address and a IP Address and Port from originator but I  cant see anywhere to capture that just a data packet....

Regards,

Clay
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39799732
I believe you should catch the connection as you already do. It will hold the sending IP address which you will need to route the data to the corresponding file.

Then receive and store the data. Finally the sender will close the connection.

As I recall, the syslog receiver holds the received data in a buffer to not slow things down, then saves the data. I guess your data volume per transmission is quite low so you could do the same.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39799827
That still doesn't answer how I setup 4 connections to the 4 sending devices
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39799969
If you really want this - just launch the app four times with different parameters.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39800080
I think we misunderstand each other.  My client will need to receive 4 streams and direct each to a specific file.  if I am able to use the one winsock receiving on its 1 port then how do I determine which server it is listening to?  Is there info in the data packet that the winsock knows which one the data is from?

Is that a better rephrase of the question?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39800169
Sorry. When the receiver detects a connection and raises the event, a property holds the IP address of the sender which you can use to identify the sender.

If you study the syslog demo, the property RemoteHostIP holds the sending IP address.

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39800555
Fantastic thank you.  I will devise some code and try it out after the weekend.

Thanks for understanding.
0
 
LVL 2

Author Comment

by:clay258
ID: 39805916
Gustav,

I decided to concentrate on receiving everything properly and getting it stored so that I can then parse it properly into an accdb table.

However, with my data arrival only slightly changed, I now throw an error 7878 Data has been changed.  Sometimes the code will carry on but if I close and open my form again I get the same thing.

7878 appears to be an issue when 2 users are writing to the db but there is no writing to the table at this point, I am just receiving the data and sending to file.

Could you look at the code and see if there is anything I have missed?  We did say that I have no error handling included yet.  Is this one of those things that needs including?

The line that gets highlighted as error 7878 is txtStatus.Text = txtStatus.Text & strData

Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   Dim tmpData As String
   strData = ""
   FNold = txtPath & txtItem & "DataCap.txt"
   sockMain.GetData strData, vbString
   txtStatus.SetFocus
   
   txtStatus.Text = txtStatus.Text & strData
   If Len(txtStatus.Text) > 1024 Then
     tmpData = txtStatus.Text
     Open FNold For Append As #1
     Write #1, tmpData
     Close #1
     txtStatus.Text = ""
   End If
End Sub

Open in new window


Is there a need to do a DoEvents() after showing the data?  Thinking about it, maybe I should change the code to this instead so that the dta gets written to file straight away and the text box is not holding up proceedings?
Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   strData = ""
   FNold = txtPath & txtItem & "DataCap.txt"
   sockMain.GetData strData, vbString

     Open FNold For Append As #1
     Write #1, strData
     Close #1

   txtStatus.SetFocus
   txtStatus.Text = txtStatus.Text & strData
   If Len(txtStatus.Text) > 1024 Then
     txtStatus.Text = ""
   End If
End Sub

Open in new window


Incidentally what's the difference between receiving as bytes and receiving as vbstring?

Kind regards,
0
 
LVL 2

Author Comment

by:clay258
ID: 39806277
To answer my own question about bytes and strings the answer is nothing as far as I can see.

After a lot of head scratching it was down to an update to the datacapture table using currentdb.execute on the connect sub.  I see no reason for this so I have fixed issue by removing the flag.  I just wanted to show the user that data for this server was being captured but I will find another way.
So now all is well and I can parse the data to file no issues.

Except, why am I receiving data with quotes at start and end?  Not really a problem if I just strip them out but its just odd....
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39808728
In VBA you should do something like this:
Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   Dim tmpData As String

   FNold = txtPath & txtItem & "DataCap.txt"
   sockMain.GetData strData, vbString
   
   tmpData = Nz(txtStatus.Value) & strData
   txtStatus.Value = tmpDate
   If Len(tmpData) > 1024 Then
     Open FNold For Append As #1
     Write #1, tmpData
     Close #1
     txtStatus.Value = Null
   End If
End Sub

Open in new window

/gustav
0
 
LVL 2

Author Comment

by:clay258
ID: 39808932
Thanks Gustav.  I will take that as the final piece of the puzzle.  Incidentally, I have now constructed a form that is able to receive UDP or TCP data, correctly connecting and disconnecting as required.
I will put this into action back at work tomorrow with the added timer function that decides a day has gone by and a new file can be created.
I have a little work to do to break up each line of data and parse it to a new table but that's the easy bit :-)

Thanks for guiding me through.  Maybe it will be beneficial to others who are taking their first steps in winsocks although I wonder what a similar operation would look like in sockets.net

A project for another day.
0
 
LVL 2

Author Closing Comment

by:clay258
ID: 39808936
Anybody looking for a solution should read the whole saga as the final answer is only a part of the overall solution.  Gustavs guidance was key to keeping on the right track
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39808956
I'm glad you worked it out, and thanks for the feedback.

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

11 Experts available now in Live!

Get 1:1 Help Now