asked on VB script to convert text file to a database
Hi Experts,
I've got a script that runs every morning at 6am to ping and trace each server on our network and then sends the output to a text file. Now, what I want to do is to convert this data into a database (MS Access, MS Excel, or any other you can point me in the direction of). What I am looking to do with this script is to take the IP/server name and time:
(ex: 10.60.1.4 and 26ms) from a line like this
Reply from 10.60.1.4: bytes=32 time=26ms TTL=123
OR
the number of packets sent/received plus the % lost and the averages from below
Ping statistics for 10.20.1.23:
Packets: Sent = 25, Received = 25, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 18ms, Maximum = 68ms, Average = 31ms
and input them into the table/database so we can keep track of the fluctuation in times. Also, for the trace routes I am looking to see if there were any Request time outs and if so, how many. This might be too specific of a task but we've had some issues with the ISP lately and this would be very helpful.
I guess my question would be, is there any way to pick out exactly what you need from the text file and insert it into a database/spreadsheet if the text is random like a ping/tracert is?
I've got a script that runs every morning at 6am to ping and trace each server on our network and then sends the output to a text file. Now, what I want to do is to convert this data into a database (MS Access, MS Excel, or any other you can point me in the direction of). What I am looking to do with this script is to take the IP/server name and time:
(ex: 10.60.1.4 and 26ms) from a line like this
Reply from 10.60.1.4: bytes=32 time=26ms TTL=123
OR
the number of packets sent/received plus the % lost and the averages from below
Ping statistics for 10.20.1.23:
Packets: Sent = 25, Received = 25, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 18ms, Maximum = 68ms, Average = 31ms
and input them into the table/database so we can keep track of the fluctuation in times. Also, for the trace routes I am looking to see if there were any Request time outs and if so, how many. This might be too specific of a task but we've had some issues with the ISP lately and this would be very helpful.
I guess my question would be, is there any way to pick out exactly what you need from the text file and insert it into a database/spreadsheet if the text is random like a ping/tracert is?
Visual Basic Classic
Last Comment
ryanjames3
If you would like to use VBA or a macro in Excel, then you could open the textfile and look at each line searching for instr(line,"timeout"). If it occurs, send line to worksheet cell. Is this a concept that may work? If so, will need to see example of text file.
ASKER
That sounds like what I am looking for but does it have to be an exact text like a name or can it be searched by location i.e. in between 2 particular words? I'm not sure how it would work with a ping reply but I have the text output attached, with modifications of course. Any thoughts?
IPedit.txt
IPedit.txt
OK so if we find 4 * * * Request timed out. in a trace what detail about that trace do you want to return to the spreadsheet?
ASKER
Well the thing with the "Request timed out." is that the ones present right now are actually there for specific reasons and are not a problem at all. This will be to mainly monitor the ping times to document their consistency throughout a period of time to see if they raise any and to make sure they do not show "Request timed out.", ultimately I would like to make it into a graph or chart. I guess what I need is a column for server IPs/names, the # of packets sent/received and/or lost %, the average ping time, and the total number of request time outs present in the whole document, if possible. If it sounds confusing to you at all then I apologize.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That should work for what I'm trying to do, thanks. Is there a way to append the new data to an existing document to keep a running tally on everything?
This code will append and add a little more functionality
Private Sub CommandButton1_Click()
On Error GoTo errorhandle
Dim filenum As Integer
filenum = FreeFile
Open "c:\experts_exchange\pingFile.txt" For Input As filenum
rowOffset = 0
'Look for first empty cell from top
Do Until IsEmpty(Cells(1, 1).Offset(rowOffset, 0)) Or rowOffset = 65535
rowOffset = rowOffset + 1
Loop
If rowOffset <> 65535 Then
'Put date as header
Worksheets("Sheet1").Cells(1, 1).Offset(rowOffset, 0) = Format(Now, "yyyy/mm/dd HH:MM:SS")
rowOffset = rowOffset + 1
'parse file into sheet
Do Until EOF(filenum)
Line Input #filenum, entry
If InStr(1, entry, "Pinging") Then
entry = Replace(entry, "Pinging", "")
entry = Trim(Left(entry, InStr(1, entry, "with") - 1))
Worksheets("Sheet1").Cells(1, 1).Offset(rowOffset, 0) = entry
rowOffset = rowOffset + 1
End If
If InStr(1, entry, "Packets:") Then
entry = Trim(Right(entry, Len(entry) - InStr(1, entry, "(") + 1))
Worksheets("Sheet1").Cells(1, 1).Offset(rowOffset - 1, 1) = entry
End If
If InStr(1, entry, "Average") Then
entry = Trim(Right(entry, Len(entry) - InStr(1, entry, "Average") + 1))
Worksheets("Sheet1").Cells(1, 1).Offset(rowOffset - 1, 2) = entry
End If
If InStr(1, entry, "timed") Then
'entry = Trim(Right(entry, Len(entry) - InStr(1, entry, "Average") + 1))
Worksheets("Sheet1").Cells(1, 1).Offset(rowOffset - 1, 3) = "Timed out"
End If
Loop
Else
MsgBox "Length of Sheet Exceeded", vbOKOnly, "Row Limit"
End If
Close filenum
Exit Sub
'End
errorhandle:
Resume Next
End Sub
ASKER
You are the amazing, I appreciate your help with this. All I need to figure out now is how to get it to append automatically with the vbs that runs every morning and everything will be perfect. Thanks for everything.
Is that script a macro in an application? From where and and how is it initiated?
ASKER
It is initiated by a batch script that is set as a scheduled task on our e-mail server that runs the script to send everything to file, sends the file via e-mail using blat, and then deletes the file. It is just a stand alone file, it is not a part of a macro.
Well that may rbe a whole new question, since it will require adjustments to the batch file script to shell start Excel and within Excel probably need an autostart macro. May want to consider, parsing file inside batch file to another text file and sending both files to emails and avoiding excell all together.
ASKER
I was thinking about maybe doing something with a csv file by extracting just the values that i need and creating a spreadsheet that way, might be a little more practical to do it that way.