Extract Data From Text File into Excel using VBA or a Macro?

cyliyu · Jul 10, 2015

I need to extract a test.log file and I only need to extract certain information from it and input it into an Excel file.
I may have 1000 such log files and is very tedious to open each file and copy and paste into Excel.

Can any help. The info I need was
1) Extract the Date
2) Extract the Serial no, but only 4 digits number. SN: 91233342516672813155301
3) A range of data (50 of them)
4) Pass or Fail at the end of each set of data (50), skip if not available.

If the text file got 2 or more set of 50 data, it should extract all into the Excel

Vivek D · Jul 10, 2015

Sounds like a very solid use case for using the Power Query addin by Micorsoft.

I was able to extract all the data points you needed (SN, Date, results) in appropriate form using some simple steps in Power Query.

The grouping by 50 can then be done using formulas. The sample you provided only had 50 entries but I suppose you can work that part out.

Key is to start using Power Query because these are very easily done using Power Query. Power query will also be able to handle multiple log files placed within a folder.

Marc L · Jul 10, 2015

A VBA demonstration to paste in worksheet module :

Code:

Sub Demo1()
  Const LOG = "D:\Tests4Noobs\Test.log", CRIT = "PASS" & vbTab & "*"
If Dir(LOG) = "" Then Beep: Exit Sub
         F% = FreeFile
Open LOG For Input As #F
SPQ = Split(Input(LOF(F), #F), vbNewLine)
Close #F
V = Split(SPQ(0), vbTab)(0)
If UBound(SPQ) < 3 Or Not IsDate(V) Then Beep: Exit Sub
Range("A9", Cells(Application.Max([B7].End(xlDown).Row, 9), 2)).ClearContents
[B5].Value = Left(Right(SPQ(1), 7), 4)
[B7].Value = V
ReDim VA(1 To UBound(SPQ) - 1, 1 To 2)

For R& = 2 To UBound(SPQ)
    If SPQ(R) Like CRIT Then
                  V = Split(SPQ(R), vbTab)
        If UBound(V) > 1 Then
            VA(L& + 1, 2) = V(2)
                        V = Split(V(1))
              If UBound(V) > 0 Then L = L + 1: VA(L, 1) = V(1)
        End If
    End If
Next

[A9:B9].Resize(L).Value = VA
End Sub

Do you like it ? So thanks to click on bottom right Like !

cyliyu · Jul 10, 2015

Thanks for your quick reply.
I may not have explained clearly what I want.
e.g.
1) I have said 1000 log files (as shown in the zip file 3155.log & 3055.log only 2 samples)
2) I need to extract the information, date, serial number, 50 sets of data and pass or fail from the log file.
3) And input them into Excel as shown in the Book1.xlsx.

What most important is the 50 sets data, as I only need the VALUES to copy into Excel. "A Column" in the excel i can fixed it.

So user can just run the VB or a button in the Excel sheet to prompt for the log files directory and once user selected the directory, the information will auto extract into Excel sheet.

Vivek D · Jul 10, 2015

I'm sure it is possible with VBA, as pretty much anything is possible with VBA but my suggestion was to use Power Query because it's a much shorter learning curve as opposed to VBA and should work much faster too. Given you have 1000s of log files I guess performance will be key.

I'd therefore suggest that you research on Power Query. I'm sure within a day or 2 you will be able to build the solution yourself.

Power Query can pick up multiple files from a folder, transform the data in whatever way you need and give an output. The sets of 50 is a little trickier but I think it can be done. Just a little more advanced so will have to research that further.

By the way, what's the exact reason for having sets of 50?

cyliyu · Jul 10, 2015

Thanks. Will read out power query and hopefully it can help to automate the retrieving of the data process into excel form to plot the graph.
Initially was thinking VBA or macros will be the easiest.

Marc L · Jul 10, 2015

No VBA difficulty, just needs a bit of logic …

Code:

Sub Demo2()
Dim COL()
 
With Application.FileDialog(msoFileDialogFolderPicker)
         .ButtonName = "Select"
    .InitialFileName = ThisWorkbook.Path & "\"
        .InitialView = msoFileDialogViewList
              .Title = Space$(27) & "Choose the log files directory :"
    If .Show Then D$ = .SelectedItems(1) & "\" Else Exit Sub
End With
                  N$ = Dir$(D & "*.log")
                If N = "" Then Beep: Exit Sub
                  F% = FreeFile
                  VA = Application.Trim(Range("A9", [A9].End(xlDown)).Value)
 
With [A6].CurrentRegion.Columns
      If .Count > 1 Then .Item(2).Resize(, .Count - 1).Clear
End With
                    Cells.NumberFormat = "General"
Do Until N = ""
        C& = 0
    Open D & N For Input As #F
    SPQ = Split(Input(LOF(F), #F), vbNewLine)
    Close #F
    V = Split(SPQ(0), vbTab)(0)
 
    If IsDate(V) Then
        For R& = 2 To UBound(SPQ) - 5
            If SPQ(R) Like "*PROCESS_DATA*" Then
                If C Then
                    Cells(5, C).Resize(UBound(COL)).Value = COL
                                                        C = C + 1
                Else
                    C = [A6].CurrentRegion.Columns.Count + 1
                End If
 
                ReDim COL(1 To UBound(VA) + 4, 0)
                      COL(1, 0) = Split(N, ".")(0)
                      COL(3, 0) = V
                      COL(4, 0) = Split(SPQ(UBound(SPQ) - 1), vbTab)(0)
 
            ElseIf C Then
                If SPQ(R) Like "*CHECK_DATA*" Then
                    S = Split(SPQ(R), vbTab)
                    M = Application.Match(Split(S(1))(1), VA, 0)
                    If IsNumeric(M) Then COL(M + 4, 0) = S(2)
                End If
            End If
        Next
 
        If C Then Cells(5, C).Resize(UBound(COL)).Value = COL
    End If
 
    N = Dir
Loop
 
With [A6].CurrentRegion.Columns
         .AutoFit
    With .Rows(4)
         .FormatConditions.Delete
         .FormatConditions.Add xlCellValue, xlEqual, "=""FAIL"""
         .FormatConditions(1).Interior.ColorIndex = 22
    End With
End With

MsgBox "Done !", vbInformation, "  Log import"
End Sub

Thanks to click on bottom right Like !

cyliyu · Jul 11, 2015

Thanks, Marc L.

Your code work, just that I am facing some issues and still trying to figure out.

Issue 1) if the log file came without the date stamp, it will not input into the excel. need to skip the date/pass or fail if the log file came without them.

Issue 2) Facing a Run-Time error 9. something to do with Subscript out of range. still trying to debug.

Issue 3) Log file with a filename came with 3 digits or more were not auto trim.

I appreciate your great help.
Thanks again.

Marc L · Jul 11, 2015

3581 is in fact 3582 (according to inner SN) that you cut ?
How can be possible to have so erratic log files ?
~~What file raises issue 2 ?~~ Found it and why, again erratic file …

My code works as you said but you seems not to like it !

cyliyu · Jul 11, 2015

yes, 3581 was duplicated from 3582 for testing purposes.
The erratic log files were due to too many people make changes to the program. a good example was the reading recorded. earlier log file without USL/LSL but later log came with it. that could be one of the reasons why I'm getting run time error.

Marc L · Jul 11, 2015

Avoided but not far from a gas factory code !

Code:

Sub Demo2a()
Const TEST1 = "Test completed", TEST2 = TEST1 & vbTab & vbTab
  Dim COL(), SP$()
  
With Application.FileDialog(msoFileDialogFolderPicker)
         .ButtonName = "Select"
    .InitialFileName = ThisWorkbook.Path & "\"
        .InitialView = msoFileDialogViewList
              .Title = Space$(27) & "Choose the log files directory :"
    If .Show Then D$ = .SelectedItems(1) & "\" Else Exit Sub
End With
                  N$ = Dir$(D & "*.log"):  If N = "" Then Beep: Exit Sub
                  F% = FreeFile
                  VA = Application.Trim(Range("A9", [A9].End(xlDown)).Value)
  
With [A6].CurrentRegion.Columns
      If .Count > 1 Then .Item(2).Resize(, .Count - 1).Clear
End With
            Application.ScreenUpdating = False
                    Cells.NumberFormat = "General"
Do
     Open D & N For Input As #F
    SP = Split(Input(LOF(F), #F), vbNewLine)
                       Close #F
    C& = 0:  R1$ = ""
                           M = Application.Match(TEST1, SP, 0)
        If IsError(M) Then M = Application.Match(TEST2, SP, 0)
    If IsNumeric(M) Then R4$ = Split(SP(M), vbTab)(0) Else R4 = ""
    R3$ = Split(SP(0), vbTab)(0):   If Not IsDate(R3) Then R3 = ""
  
    For Each V In SP
        If V Like "*CHECK_DATA*" Then
            S = Split(V, vbTab)
            M = Application.Match(Split(S(1))(1), VA, 0)
            If IsNumeric(M) Then COL(M + 4, 0) = S(2)
  
        ElseIf V Like "*PROCESS_DATA*" Then
            If C Then Cells(5, C).Resize(UBound(COL)).Value = COL: C = C + 1 _
                 Else C = [A6].CurrentRegion.Columns.Count + 1
            ReDim COL(1 To UBound(VA) + 4, 0)
            COL(1, 0) = R1:  COL(3, 0) = R3:  COL(4, 0) = R4
  
        ElseIf V Like "SN:*" Then
            R1 = Left$(Right$(Split(V, vbTab)(0), 7), 4)
        End If
    Next
            If C Then Cells(5, C).Resize(UBound(COL)).Value = COL
           N = Dir$
Loop Until N = ""
  
With [A6].CurrentRegion
         .Columns.AutoFit
                    .Rows(1).NumberFormat = "0000"
         .Rows("1:4").HorizontalAlignment = xlCenter
    With .Rows(4).FormatConditions
         .Delete:  .Add xlCellValue, xlEqual, "=""FAIL"""
         .Item(1).Interior.ColorIndex = 22
    End With
End With
End Sub

You like ? So thanks to …

cyliyu · Jul 11, 2015

Thanks, Marc L.
You have solved my problem. a great help. really appreciate.
I was tasked to retrieve 6 months data (~ 5000). and more to come as time goes by.

Marc L · Jul 11, 2015

Oh really, I was expecting other bad log files surprise !

You're lucky in the way of inner file markers to process data and
separate in columns : without them that would not has been as easy …

If you remove in code Application.Trim statement, you will get
few data rows because of bad column A entries !
For example in A15 cell (and many others !) there is a space ending.
So "NMR_AVG " is not equal to "NMR_AVG" …
And A46 cell begins with space !
Should be an issue with other way like formula, pivot,
power pivot and power query ! Keep that in mind …

cyliyu · Jul 11, 2015

Noted. Was surprise too when I came to know the in consistency entries in the log file.
I have yet to test out thousand records as I left all the files in my office. But your program giving a great help. Before that I spent almost 2 days just to manually transfer the data into Excel for 1000 records.
By the way, if I will to start the data from column "C" or "D" instead of "B", possible? Trying to modify your code but not yet successful.
I also notice you make the cells.number format to be "General", this will affect the date cell as well. No problem, I think I can figure out how to change the particular row to be "date" format.

Marc L · Jul 11, 2015

Do you keep former data in columns C & D or you just need some room ?

In second case just insert columns at end of process :

Columns("B:C").Insert …

cyliyu · Jul 11, 2015

I just want to have some room or be flexible in case I need to add more information or remarks after column "A".

Marc L · Jul 11, 2015

So when it's necessary, easy way is just to insert column manually
or like in my previous post …

If it's a true need (performing a code retro analysis is good to learn) :

Code:

Sub Demo2b()
Const FC = 4, TEST1 = "Test completed", TEST2 = TEST1 & vbTab & vbTab
  Dim COL(), SP$()
 
With Application.FileDialog(msoFileDialogFolderPicker)
         .ButtonName = "Select"
    .InitialFileName = ThisWorkbook.Path & "\"
        .InitialView = msoFileDialogViewList
              .Title = Space$(27) & "Choose the log files directory :"
    If .Show Then D$ = .SelectedItems(1) & "\" Else Exit Sub
End With
                  N$ = Dir$(D & "*.log"):  If N = "" Then Beep: Exit Sub
                  F% = FreeFile
                  VA = Application.Trim(Range("A9", [A9].End(xlDown)).Value)
 
With Me.UsedRange.Columns
    If .Count > 1 Then .Item(2).Resize(, .Count - 1).Clear
End With
            Rows("5:" & UBound(VA) + 8).NumberFormat = "General"
                                                  C& = FC - 1
                          Application.ScreenUpdating = False
Do
     Open D & N For Input As #F
    SP = Split(Input(LOF(F), #F), vbNewLine)
                       Close #F
    K& = C + 1:  C = 0
    ReDim CR(1 To 4)
                              M = Application.Match(TEST1, SP, 0)
           If IsError(M) Then M = Application.Match(TEST2, SP, 0)
     If IsNumeric(M) Then CR(4) = Split(SP(M), vbTab)(0)
                          CR(2) = Split(SP(0), vbTab)(0)
    If IsDate(CR(2)) Then CR(3) = CR(2)
 
    For Each V In SP
        If V Like "*CHECK_DATA*" Then
            S = Split(V, vbTab)
            M = Application.Match(Split(S(1))(1), VA, 0)
            If IsNumeric(M) Then COL(M + 4, 0) = S(2)
 
        ElseIf V Like "*PROCESS_DATA*" Then
            If C Then Cells(5, C).Resize(UBound(COL)).Value = COL: C = C + 1 Else C = K
            ReDim COL(1 To UBound(VA) + 4, 0)
            COL(1, 0) = CR(1):  COL(3, 0) = CR(3):  COL(4, 0) = CR(4)
 
        ElseIf V Like "SN:*" Then
            CR(1) = Left(Right(Split(V, vbTab)(0), 7), 4)
        End If
    Next
            If C Then Cells(5, C).Resize(UBound(COL)).Value = COL
           N = Dir$
Loop Until N = ""
 
With Cells(6, FC).CurrentRegion
                    .Columns.AutoFit
                    .Rows(1).NumberFormat = "0000"
         .Rows("1:4").HorizontalAlignment = xlCenter
    With .Rows(4).FormatConditions
         .Delete:  .Add xlCellValue, xlNotEqual, "=""PASS"""
         .Item(1).Interior.ColorIndex = 22
    End With
End With
End Sub

Enjoy it !

cyliyu · Jul 11, 2015

Notice the lines
With [A6].CurrentRegion.Columns
If .Count > 1 Then .Item(2).Resize(, .Count - 1).Clear
End
was replaced by
With Me.UsedRange.Columns
If .Count > 1 Then .Item(2).Resize(, .Count - 1).Clear
End With

and have an error as shown.

Marc L · Jul 12, 2015

For safety and as written for first demo (see post #3),
must past code to "Sample" worksheet module !
Read also Me and UsedRange VBA help …

cyliyu · Jul 12, 2015

got it. thanks.

Marc L · Jul 12, 2015

And ? Did you try ? Is it at your like ?

cyliyu · Jul 12, 2015

I tested. just that Column A was gone. data input from "A" onward.

Marc L · Jul 12, 2015

Odd 'cause works like a breeze on my side !
I just tested it again with your attached workbook (post #4) :
can't reproduce issue …

Did you amend my code ? Which Excel version ?

cyliyu · Jul 12, 2015

No change to your code, I just copy and pasted on the sample worksheet.
Are you seeing the date displayed as 5 digits number as mine too?
using Excel 2010

Marc L · Jul 12, 2015

Weird ‼ 'Cause in my code nothing writes on column A ! …
Same sample log files ? Demo2a still works ?

Last try : remove code everywhere, save workbook and close Excel.
Restart computer, re-Open workbook and paste code to worksheet module …

Extract Data From Text File into Excel using VBA or a Macro?

Member

Attachments

Member

Attachments

Excel Ninja

Member

Attachments

Member

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja