[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Difficult email parsing question

I recieve the following information in an email regularly.  

_________________________________________________________________________

ITEM: ALERT

This alert is from My Computer
Time:     11:30 AM 10/15/07
Monitor:  Monitor: ALERT
Group:    ALERT
Status:   2 matches in 25 entries
Sample #: 848071

Time:     11:30 AM 01/01/07

Event Description(s):
IBS.General:70
The description for Event ID ( 70 ) in Source ( IBS.General ) could not be found. It contains the following insertion string(s): Login from new IP address range, Cust = "Sample; User = Sample: IP Address = 70.144.105.132, , Create Date: 01/01/2007 11:33:32".

IBS.General:70
The description for Event ID ( 70 ) in Source ( IBS.General ) could not be found. It contains the following insertion string(s): Login from new IP address range, Cust = "Sample2; User = Sample2; IP Address = 70.149.131.247, , Create Date: 01/01/2007 11:33:33".
_______________________________________________________________________________________

I manually copy out the data in quotes into a spreadsheet but I would like to do i programatically.  The information in the quotes is all i need and nothing else.  I would like them to be parsed into a spreadsheet with the following headers;

Cust      User      Date      Time     IP Address
-----          -----         ------         ------     -------------

Also, there could be up to 10 of these;

The description for Event ID ( 70 ) in Source ( IBS.General ) could not be found. It contains the following insertion string(s): Login from new IP address range, Cust = "Sample2; User = Sample2; IP Address = 70.149.131.247, , Create Date: 01/01/2007 11:33:33".

in each email so I would like it to stop on the last one.  I use Outlook 2003.

Thanks


0
farmingtonis
Asked:
farmingtonis
  • 2
1 Solution
 
RayAtVittoriaCommented:
This is a rather tricky item you are asking for.
1) You need to export the email into a text file.
2) Parse the text file automatically , getting rid of unwanted data... and saving it in another file in a  CSV format.
3) Import that parsed file.

This could be done using a scripting language... In fact I have done some very tricky stuff with AutoIt v3. This is a free  and quite comprehensive scripting language. If you are willing to spend a day I am sure you could create executable that will do exactly what you need. I can give pointers, but unfortunately one really has to be in front of the target computer with live data in order to program this correctly.
Here is the website.
http://www.autoitscript.com/autoit3/

Hope this helps.
Cheers
Ray
0
 
farmingtonisAuthor Commented:
Can't do that at my work.  However, let me ask the question another way.  Say i am using VBScript on a text file how would i get all of the pieces of data (starting after the Cust = part) into a spreadsheet.  

Event Description(s):
IBS.General:70
The description for Event ID ( 70 ) in Source ( IBS.General ) could not be found. It contains the following insertion string(s): Login from new IP address range, Cust = Sample; User = Sample: IP Address = 70.144.105.132, , Create Date: 01/01/2007 11:33:32.
0
 
RayAtVittoriaCommented:
You will have to save email as a text file (c:\temp\test.txt)

Using VBA in excel add the following three subs in a module
it will do exactly what you asked for.
You can play around with it if you know VBA.

Option Explicit

Public Sub ImportText()
    Dim ws As Worksheet
    Dim Cust As String
    Dim User As String
    Dim dtString As String
    Dim dt As Date
    Dim IP As String
    Dim pos As Integer
    Dim tString As String
    Dim txt As String
    Dim r As Integer 'start row
    Dim FileName As String
   
    Set ws = Application.ActiveSheet
    r = 1
    ws.Cells(r, 1) = "CUST"
    ws.Cells(r, 2) = "USER"
    ws.Cells(r, 3) = "IP ADDRESS"
    ws.Cells(r, 4) = "DATE"
    ws.Cells(r, 5) = "TIME"

    FileName = "C:\temp\test.txt" 'Change this to the filename you want to import
   
    Open FileName For Input As #1
    While Not EOF(1)
        Line Input #1, txt
        Cust = GetString(txt, "Cust =")
        If Cust <> "" Then
            User = GetString(txt, "User =")
            IP = GetString(txt, "IP Address = ")
            dtString = GetDateString(txt, "Create Date: ")
            dt = dtString
            r = r + 1
            ws.Cells(r, 1) = Cust
            ws.Cells(r, 2) = User
            ws.Cells(r, 3) = IP
            ws.Cells(r, 4) = dt 'Format this colum to show Date part of field
            ws.Cells(r, 5) = dt 'Format this colum to show Time part of field
        End If
       

    Wend
   
    Close #1

End Sub


Private Function GetString(ByRef txt As String, ByVal SearchString As String) As String
    Dim t, tC, RetString, tChar, sChar As String
    Dim sLen, i, pos As Integer
    t = txt
    RetString = ""
    sChar = Mid(SearchString, 1, 1)
    sLen = Len(SearchString)
    For i = 1 To Len(t)
        tChar = Mid(t, 1, 1)
        If tChar = sChar Then
            tC = Mid(t, 1, sLen)
            If tC = SearchString Then
                t = Mid(t, sLen + 1, Len(t))
                t = LTrim(t)
                tChar = Mid(t, 1, 1)
                While tChar <> ";" And tChar <> ":" And tChar <> ","
                    If tChar <> """" Then
                        RetString = RetString & tChar
                    End If
                    t = Mid(t, 2, Len(t))
                    tChar = Mid(t, 1, 1)
                Wend
                GetString = RetString
                txt = t
                Exit Function
            End If
        End If
        t = Mid(t, 2, Len(t))
    Next i
End Function

Private Function GetDateString(ByRef txt As String, ByVal SearchString As String) As String
    Dim t, tC, RetString, tChar, sChar As String
    Dim sLen, i, pos As Integer
    t = txt
    RetString = ""
    sChar = Mid(SearchString, 1, 1)
    sLen = Len(SearchString)
    For i = 1 To Len(t)
        tChar = Mid(t, 1, 1)
        If tChar = sChar Then
            tC = Mid(t, 1, sLen)
            If tC = SearchString Then
                t = Mid(t, sLen + 1, Len(t))
                t = LTrim(t)
                tChar = Mid(t, 1, 1)
                While tChar <> ";" And tChar <> "."
                    If tChar <> """" Then
                        RetString = RetString & tChar
                    End If
                    t = Mid(t, 2, Len(t))
                    tChar = Mid(t, 1, 1)
                Wend
                GetDateString = RetString
                txt = t
                Exit Function
            End If
        End If
        t = Mid(t, 2, Len(t))
    Next i
End Function
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now