Retrieve data from changing text file into Excel 1

[Hermanator]

Hello all,

I hope that someone likes to help me with this 'problem'. I think that I need to create a macro for this, that's why I have placed this question here. This is what I would like to do:

I would like to create an Excel sheet that can import data from a series of e-mails. In my mail-box, I receive a message every day with stock-quotes. All these mails go into a specific mail folder. As a mail-client, I use Mozilla Thunderbird. This program places all the e-mails of a mail-folder into one text-file.
So the macro needs to read that text file, and then search for a certain word (for example "subject") to locate the start of an e-mail. Then import certain values to the excel sheet, and then move on in the file to the next e-mail, till the end of the file.
Each day, a new e-mail is added to the file, so the number of data to read increases.

Does anyone have a few pointers?

 

[longhair]

Hermanator,
this won't be easy with just excel. i would suggest looking at awk or sed to do the initial read, via a .bat job, and put the data you need into a new file (this way it's formatted easier to read into excel). then import this new file into excel via another .bat job.
you can get awk or sed for free for windose (just google for it) and there is also a tek-tip forum for awk.
hth
regards,
longhair

 

[bkclaw113]

This is a function that I put together to do a similar task. You pass in the name of the file that you want to parse and some other information that you can probably cut out of your solution. The program then goes on to store general info about the file being imported and then goes though an array of camparison identifiers to see if I want to take further action. If any of the identifiers are found then the code grabs the text from the file.

The data that I was working with sometimes spanned multiple lines so that is why I have the unusual section about Do Until Left(strLine(1), 6) <> " "

Public Function ParseLog(strLogFile As String _
                            , strUser As String _
                            , Optional strGrp As String = "" _
                            , Optional strCustom As String = "~!@#$") As Boolean
On Error GoTo ParseLog_Error

Dim ts As TextStream
Dim fso As New FileSystemObject
Dim f As File
Dim strLine(1) As String
Dim aryMsgType As Variant
Dim strSQL As String
Dim rs As New ADODB.Recordset
Dim lngLogID As Long

    Set f = fso.GetFile(strLogFile)
    Set ts = fso.OpenTextFile(f.Path, ForReading)
    
    aryMsgType = Array("", "Error:", "Warning:", "Note:", strCustom)
    
    'Append Log info to LOG table
    rs.Open "Log", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs.AddNew
    lngLogID = rs!Log_ID
    rs!dteCreate = f.DateCreated
    rs!Path = f.ParentFolder
    rs!Name = f.Name
    rs!Group = strGrp
    rs!UserID = strUser
    rs.Update
    rs.Close
    
    rs.Open "Message", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    strLine(1) = ts.ReadLine
    Do Until ts.AtEndOfStream
        strLine(0) = strLine(1)
        strLine(1) = ts.ReadLine
        
        'Cycle through each possible message type to check against input line
        For i = 1 To UBound(aryMsgType)
            If Left(strLine(0), Len(aryMsgType(i))) = aryMsgType(i) Then
                'textstream begins with Message Type text
                'save message line
                rs.AddNew
                rs!Type_ID = i
                rs!Log_ID = lngLogID
                rs!Message = strLine(0)
                Do Until Left(strLine(1), 6) <> "      "
                    rs!Message = rs!Message & Chr(13) & Chr(10) & strLine(1)
                    strLine(0) = strLine(1)
                    strLine(1) = ts.ReadLine
                Loop
                rs.Update
            End If
        Next i
    Loop
    
    'clean up objects
    rs.Close
    Set rs = Nothing
    Set f = Nothing
    ts.Close
    Set ts = Nothing
    
ParseLog_Exit:
    Exit Function
ParseLog_Error:
    MsgBox Err.Number & " " & Err.Description
    GoTo ParseLog_Exit

End Function

Hope this is helpful

 

[longhair]

bkclaw113,
a star for some good (and well commented code). i think i might snag a copy for future ref - if it's ok. i still think that using sed or awk would be much quicker than doing it this way - but i guess that depends on the user.
regards,
longhair

 

[bkclaw113]

Forgot to mention that you need a reference to Microsoft Scripting runtime, or use createobject("Scripting.filesystemobject")

 

[bkclaw113]

Feel free to use the code, I would not publish it if I had any expectations of keeping it private.

 

[Hermanator]

Thanks for your help everyone! It works!