Excel Macro Text file data extraction

[contactdouglas]

I am trying to extract data from multiple text files in a folder. I have a rough macro but I am having problems determining the line number where my data is located or actually extracting the data. The line number of the data may change from file to file but I have located a key word only present once in the file a few lines before the data I can key off of (CalibrateOffsets). UO and VO appear in other places so I cannot use them to search. The line number changes so I cannot use a set line number. I have not found a good way in the macro to find the line number of my key word or better yet read in my data a few lines after into excel. Here is a snapshot of the text file where I need the data uo and vo :



2011-01-05 10:46:01.93 Step Current Calibration CalibrateOffsets
Error Code:
Results: SUCCESS: Calibrated offsets on 2 tries. Max variation=0 counts between tries, <= 1 limit.
UO=4 VO=6 WO=0



My excel macro below can step through the files but I cannot find away to grab the data.

Thanks!

Sub CalibrationOffset() ' ' Dim dataFolder As String Dim dataFile As String Dim strData As String Dim Dataline As Integer Dim LineNo As Integer Dim UO As Integer Dim VO As Integer Dim SearchString As String Dim fs, f Dim Data As String fs = FreeFile SearchString = CalibrateOffsets Dataline = 1 LineNo = 0 UO = 0 VO = 0 dataFolder = "C:\data\" dataFile = Dir(dataFolder & "*.log") 'Loop through each text file within the folder Do While Len(dataFile) > 0 'Open the current text file 'Set f = fs.OpenTextFile(dataFolder & dataFile, 1, TristateFalse) Open dataFolder & dataFile For Input As #fs 'Import the data from the current text file Do Until EOF(1) ' Loop until end of file 'CalibrateOffsets = search term just before actual UO and VO values to be extracted 'LineNo = StringExistsInFile("CalibrateOffsets") 'LineNo = InStr(SearchString, c) Line Input #fs, Data ActiveSheet.Cells(5, 4) = Data LineNo = LineNo + 1 Loop UO = 0 VO = 0 dataFile = Dir 'Call the next text file Loop MsgBox "Completed!", vbInformation End Sub

<TBODY>
</TBODY>

 

[Jeffrey Smith]

I think this is a better start. I'm making some assumptions. I can help you refine it.

"CalibrateOffsets" needed to be put in quotes.

Sub CalibrationOffset()
'
'
Dim dataFolder As String
Dim dataFile As String
Dim strData As String
Dim Dataline As Integer
Dim LineNo As Integer
Dim UO As Integer
Dim VO As Integer
Dim SearchString As String
Dim fs, f
Dim Data As String
Dim x As Long
Dim found As Integer

fs = FreeFile
SearchString = "CalibrateOffsets"
Dataline = 1
LineNo = 0
UO = 0
VO = 0
dataFolder = "C:\data\"

'Loop through each text file within the folder
Do
  If x = 0 Then
    dataFile = Dir(dataFolder & "*.log")
  Else
    dataFile = Dir("")
  End If
  x = x + 1
  found = 0
  If Len(dataFile) > 0 Then
    'Open the current text file
    Open dataFolder & dataFile For Input As #fs
    'Import the data from the current text file
    Do Until EOF(fs) ' Loop until end of file
    'CalibrateOffsets = search term just before actual UO and VO values to be extracted

      Line Input #fs, Data
      If found = 0 And InStr(SearchString, Data) Then found = 1
      If found = 1 Then
        ActiveSheet.Cells(LineNo + 5, x + 4) = Data  'I guessed that you wanted to put the data starting in Cell D5
        LineNo = LineNo + 1
      End If
    Loop
  End If
  
UO = 0
VO = 0
LineNo = 0
Loop
 
MsgBox "Completed!", vbInformation
End Sub

 

[contactdouglas]

Thanks a lot for the help!! Sometimes those little things really get you!

Now I am having a problem opening the subsequent .log files in the folder. The first .log file opens fine but when the loop goes to get the next file for some reason dataFile gets flagged as Lotusclientupdate.log and cannot be opened. This didn't happen yesterday. I'm sure the lotusclientupdate is some system file that is being flagged. This file is definitely not contained in my data folder.

 

[Jeffrey Smith]

DIR(Path/FileName,Attribute)
'Where attribute could be zero or 1
'Zero is for normal files without attributes
'1 is for Read Only files and will include normal files also
'This should exclude system files

 

[Jeffrey Smith]

I made a couple of mistakes in the code above

Sub CalibrationOffset()
'
'
Dim dataFolder As String
Dim dataFile As String
Dim strData As String
Dim Dataline As Integer
Dim LineNo As Integer
Dim UO As Integer
Dim VO As Integer
Dim SearchString As String
Dim fs, f
Dim Data As String
Dim x As Long
Dim found As Integer

fs = FreeFile
'SearchString = "CalibrateOffsets"
Dataline = 1
LineNo = 0
UO = 0
VO = 0
dataFolder = "C:\data\"

'Loop through each text file within the folder
Do
  If x = 0 Then
    dataFile = Dir(dataFolder & "*.*")
  Else
    dataFile = Dir            'This is changed
  End If
  x = x + 1
  found = 0
  If Len(dataFile) > 0 Then
    'Open the current text file
    Open dataFolder & dataFile For Input As #fs
    'Import the data from the current text file
    Do Until EOF(fs) ' Loop until end of file
    'CalibrateOffsets = search term just before actual UO and VO values to be extracted

      Line Input #fs, Data
      If found = 0 And InStr(Data, SearchString) > 0 Then found = 1
      If found = 1 Then
        ActiveSheet.Cells(LineNo + 5, x + 4) = Data  'I guessed that you wanted to put the data starting in Cell D5
        LineNo = LineNo + 1
      End If
    Loop
    Close #fs
  Else
    Exit Do
  End If
  
UO = 0
VO = 0
LineNo = 0
Loop
 
MsgBox "Completed!", vbInformation
End Sub

 

[contactdouglas]

Thanks! Now I have it working. Its a little different than yours but it works. Now I am pulling in the line of data I want I just need to see if I can split the data (string) up to two different colums. I'll find it I saw it somewhere.

 

[sesgiri]

Nice one Jeff. Can you please help me with fixed length data in text files to Excel?

Thanks,