Link to home
Create AccountLog in
Avatar of ryanjames3
ryanjames3Flag for United States of America

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?
Avatar of TRobinJames
TRobinJames

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.
Avatar of ryanjames3

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
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?
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
Avatar of TRobinJames
TRobinJames

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Open in new window

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?
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.
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.