Importing a .log file into excel via a Macro.

[moit]

Hi.

Can somebody please tell me how best to import a .log file into an excel worksheet (2002) via a macro. I would like the macro to allow (direct) the user to select the .log file from a directory, just as you would use explore to locate file.

A sample of log data is shown here. The first six lines of the file is simply a summary of the dataset (whish I would like to keep). The main data follows the summary, and although the headers do not look to be inline with the data in this example, if the .log file is opened with excel, the data is presented as desired (each header and appropriate data) incolumns of their own.

Once the data is in excel, I can then manipulate, summarise and plot the data.

Thanks in advance.

C:\Program Files\Data Manager\Datalogs\Main_Data1106101043.log
Unit: Main Back
Started: 10/06/2011 10:43:31
Operator: op
Begin Lot Information:
I000109-49units,testunit-1unit

End Lot Information
Run time, mins. Entry Date Entry Time Setpoint °C Temperature °C O2 Level ppm Hi-Limit Alarm Soak Dev Alarm Alarm Outputs Event Outputs
0.00 10/06/2011 10:43:31 82 77 215822 0 0 0000 0000
0.07 10/06/2011 10:43:35 77 77 215924 0 0 0000 0000
0.60 10/06/2011 10:44:07 77 76 181470 0 0 0000 0000
1.13 10/06/2011 10:44:39 77 76 127552 0 0 0000 0000
1.65 10/06/2011 10:45:10 77 76 90468 0 0 0000 0000
2.18 10/06/2011 10:45:42 77 76 63381 0 0 0000 0000
2.72 10/06/2011 10:46:14 77 76 44616 0 0 0000 0000
3.25 10/06/2011 10:46:46 77 75 31309 0 0 0000 0000
3.77 10/06/2011 10:47:17 77 75 21875 0 0 0000 0000
4.30 10/06/2011 10:47:49 77 75 14790 0 0 0000 0000
4.82 10/06/2011 10:48:20 77 75 9870 0 0 0000 0000
5.32 10/06/2011 10:48:50 77 75 6068 0 0 0000 0000
5.83 10/06/2011 10:49:21 76 75 3475 0 0 0000 0000
6.37 10/06/2011 10:49:53 76 74 1548 0 0 0000 0000
6.90 10/06/2011 10:50:25 76 74 3958 0 0 0000 0000
7.43 10/06/2011 10:50:57 76 74 3355 0 0 0000 0000

 

[Ruddles]

Based on the data you posted, try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub Import_Logfile()[/FONT]
 
[FONT=Fixedsys]  Dim sFileName As String
  Dim sOutFileName As String
  Dim ws As Worksheet
  
  Dim iFH As Integer
  Dim iRow As Long
  Dim iDataStart As Long
  Dim sRecord As String
  Dim vRecord As Variant
  
  Dim dtStart As Date
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  ws.Cells.ClearContents
  
  sFileName = Application.GetOpenFilename(FileFilter:="Log files (*.log), *.log, All files (*.*),*.*")
  If sFileName = "False" Then Exit Sub
   
  dtStart = Now()
  
  Close
  iFH = FreeFile
  
  Open sFileName For Input As iFH
  iRow = 0
  Do Until EOF(iFH) Or sRecord = "End Lot Information"
    Line Input #iFH, sRecord
    iRow = iRow + 1
    ws.Cells(iRow, 1) = sRecord
  Loop
  Do Until EOF(iFH)
    Line Input #iFH, sRecord
    iRow = iRow + 1
    If Left(sRecord, 8) = "Run time" Then
      ws.Range("A" & CStr(iRow) & ":J" & CStr(iRow)) = Array("Run time, mins.", "Entry Date", _
               "Entry Time", "Setpoint °C", "Temperature °C", "O2 Level ppm", "Hi-Limit Alarm", _
               "Soak Dev Alarm", "Alarm Outputs", "Event Outputs")
      iDataStart = iRow + 1
    Else
      vRecord = Split(sRecord, " ")
      ws.Range("A" & CStr(iRow) & ":J" & CStr(iRow)) = vRecord
    End If
  Loop
  Close iFH
  
  MsgBox vbCrLf _
       & Space(5) & CStr(iRow - iDataStart + 1) & " data records imported" & Space(10) & vbCrLf & vbCrLf _
       & Space(5) & "Run time: " & Format(Now() - dtStart, "hh:nn:ss"), _
       vbOKOnly + vbInformation, [/FONT][FONT=Fixedsys]"[COLOR=red]Data Manager Log Import Program[/COLOR]"[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

Change the bits in red to suit.

Shout if you encounter any problems.

 

[moit]

Hi Ruddles.

Thank you for the quick response. Generally, the routine seems to work but there is a bit of a problem.

The data summary is retianed as requested and the headers are placed into individual columns, but unfortunately, the data for all parameters is being entered into each column?

For example cells A10, B10, C10, D10 etc. each contain the data of all paramters of the first entry in one long string.
Like wise, cells A11, B11, C11, D11 etc. each contain the data of all paramters of the second entry in one long string etc...

Looks to be a delimiting type issue?

Any help would be much appreciated.

Kind regards, Moit.

 

[Ruddles]

What's the delimiter in the .log file? If you don't know how to find out, open it in Notepad and post a couple of lines of data here.

If you do know what the delimiter is, modify the Split command in my code which I stupidly set to " "! You probably want something like:-

vRecord = Split(sRecord, [B][COLOR=red]","[/COLOR][/B])

 

[moit]

Hi.

I believe the delimiter is a space. I have opened the file up in notpad and pasted below, which is what I did originally, so I epxect this file will look just the same. I could perhaps send the actual .log file if I had an address.

C:\Program Files\Data Manager\Datalogs\Main_Oven_1106101043.log
Uit: Main Back
Started: 10/06/2011 10:43:31
Operator: op
Begin Lot Information:
I000109-49units,testwafer-1unit

End Lot Information
Run time, mins. Entry Date Entry Time Setpoint °C Temperature °C O2 Level ppm Hi-Limit Alarm Soak Dev Alarm Alarm Outputs Event Outputs
0.00 10/06/2011 10:43:31 82 77 215822 0 0 0000 0000
0.07 10/06/2011 10:43:35 77 77 215924 0 0 0000 0000
0.60 10/06/2011 10:44:07 77 76 181470 0 0 0000 0000
1.13 10/06/2011 10:44:39 77 76 127552 0 0 0000 0000
1.65 10/06/2011 10:45:10 77 76 90468 0 0 0000 0000
2.18 10/06/2011 10:45:42 77 76 63381 0 0 0000 0000
2.72 10/06/2011 10:46:14 77 76 44616 0 0 0000 0000
3.25 10/06/2011 10:46:46 77 75 31309 0 0 0000 0000
3.77 10/06/2011 10:47:17 77 75 21875 0 0 0000 0000
4.30 10/06/2011 10:47:49 77 75 14790 0 0 0000 0000

Regards, Moit.

 

[Ruddles]

Check your private messages.