COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

[Sinner]

Hi,

File is at link: http://www.savefile.com/files/2029405
File contains sample of below since I was unable to align column
headers here & things were looking wierd.

I have a folder containing system generated log files around 10000.
Each has a detail file also & it is tiresome to complie all from
detail in one sheet. I think I can get same from these log files
instead.
These are five line files starting at start of file & ending at ----
end----. Sample is in sheet "Sample Text File".

I want to compile a log sheet in excel of such multiple files placed
in a folder. I should be able to browse to the folder & select all the
files. The script will cycle through all the files and get the below
required in an excel file.
Below are the requirement:


In second line, after "LOG."date of file is mentioned [Example:
20080901].
Required format of date is dd-mmm-yyyy (01-SEP-2008)

In third line, detect fifteen len number [Example: 000001096300001]
Required is V0963001 (remove first 6 from left, add alphabet "V"(upper
case) to the left, remove 2 zeros at 11th & 12th position and combine
remaining two from right forming V0963001

In third line, detect two ten len numbers [Example: 0004291833 to
0004292832]
Required is "000429183300" and "000429283299".
Add "00" to first number & add "99" to second number from right.

Excel log sheet format is in sheet "Log Sheet".
(Sort ascending "Start#")


A macro solution would be much appreciated.

Thx.

 

[Tim Williams]

What have you go so far?

Tim

 

[WoW]

What have you go so far?

Tim

Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below since I was unable to align column
headers here & things were looking wierd.

I have a folder containing system generated log files around 10000.
Each has a detail file also & it is tiresome to complie all from
detail in one sheet. I think I can get same from these log files
instead.
These are five line files starting at start of file & ending at ----
end----. Sample is in sheet "Sample Text File".

I want to compile a log sheet in excel of such multiple files placed
in a folder. I should be able to browse to the folder & select all the
files. The script will cycle through all the files and get the below
required in an excel file.
Below are the requirement:

In second line, after "LOG."date of file is mentioned [Example:
20080901].
Required format of date is dd-mmm-yyyy (01-SEP-2008)

In third line, detect fifteen len number [Example: 000001096300001]
Required is V0963001 (remove first 6 from left, add alphabet "V"(upper
case) to the left, remove 2 zeros at 11th & 12th position and combine
remaining two from right forming V0963001

In third line, detect two ten len numbers [Example: 0004291833 to
0004292832]
Required is "000429183300" and "000429283299".
Add "00" to first number & add "99" to second number from right.

Excel log sheet format is in sheet "Log Sheet".
(Sort ascending "Start#")

A macro solution would be much appreciated.

Thx.- Hide quoted text -

- Show quoted text -

Well TIM I was hoping to modify a bit from code below that I have.
The code consolidates data from known columns from known file names.
I kinda don't like the browse method though : )

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long


Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long


Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long


bInfo.pidlRoot = 0& 'Root folder = Desktop


bInfo.lpszTitle = Name


bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog


'Parse the result
path = Space$(512)


GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If


End Function

Sub GetData()


Folder = GetFolder & "\"


With Sheets("Data")
.Cells.ClearContents
.Range("A1") = "Category"
.Range("B1") = "Number"
.Range("C1") = "Location"
End With


With Sheets("Temp")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
FName = .Range("A" & RowCount)
Category = .Range("B" & RowCount)
Col = .Range("C" & RowCount)
Location = .Range("D" & RowCount)


If Dir(Folder & FName & ".txt") <> "" Then
With Sheets("Temp1")
.Cells.ClearContents
.Columns("B:C").NumberFormat = "@"
With .QueryTables.Add( _
Connection:="TEXT;" & Folder & FName & ".txt", _
Destination:=.Range("A1"))

.Name = "Test"
.FieldNames = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFileStartRow = 10
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 2, 2, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With


Set LastCell = .Cells(Rows.Count, Col).End(xlUp)
Set CopyRange = .Range(Cells(1, Col), LastCell)

End With


With Sheets("Data")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
CopyRange.Copy Destination:=.Range("B" & NewRow)
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("A" & NewRow & ":A" & LastRow) = Category
.Range("C" & NewRow & ":C" & LastRow) = Location
End With
End If
RowCount = RowCount + 1
Loop
End With

Sheets("Temp1").Cells.ClearContents
Sheets("Data").Cells.EntireColumn.AutoFit

End Sub

 

[WoW]

What have you go so far?

Tim

news:[email protected]...

Hi,
File is at link:http://www.savefile.com/files/2029405
File contains sample of below since I was unable to align column
headers here & things were looking wierd.
I have a folder containing system generated log files around 10000.
Each has a detail file also & it is tiresome to complie all from
detail in one sheet. I think I can get same from these log files
instead.
These are five line files starting at start of file & ending at ----
end----. Sample is in sheet "Sample Text File".
I want to compile a log sheet in excel of such multiple files placed
in a folder. I should be able to browse to the folder & select all the
files. The script will cycle through all the files and get the below
required in an excel file.
Below are the requirement:
In second line, after "LOG."date of file is mentioned [Example:
20080901].
Required format of date is dd-mmm-yyyy (01-SEP-2008)
In third line, detect fifteen len number [Example: 000001096300001]
Required is V0963001 (remove first 6 from left, add alphabet "V"(upper
case) to the left, remove 2 zeros at 11th & 12th position and combine
remaining two from right forming V0963001
In third line, detect two ten len numbers [Example: 0004291833 to
0004292832]
Required is "000429183300" and "000429283299".
Add "00" to first number & add "99" to second number from right.
Excel log sheet format is in sheet "Log Sheet".
(Sort ascending "Start#")
A macro solution would be much appreciated.
Thx.- Hide quoted text -

- Show quoted text -

Well TIM I was hoping to modify a bit from code below that I have.
The code consolidates data from known columns from known file names.
I kinda don't like the browse method though : )

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" _
   (ByVal pidl As Long, _
    ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" _
   (lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type
    Function GetFolder(Optional ByVal Name As String = _
                "Select a folder.") As String
    '-------------------------------------------------------------
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim oDialog As Long

        bInfo.pidlRoot = 0&                 'Root folder = Desktop

        bInfo.lpszTitle = Name

        bInfo.ulFlags = &H1                 'Type of directory to
Return
        oDialog = SHBrowseForFolder(bInfo)  'display the dialog

        'Parse the result
        path = Space$(512)

        GetFolder = ""
        If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
            GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
        End If

    End Function

Sub GetData()

Folder = GetFolder & "\"

With Sheets("Data")
   .Cells.ClearContents
   .Range("A1") = "Category"
   .Range("B1") = "Number"
   .Range("C1") = "Location"
End With

With Sheets("Temp")
   RowCount = 2
   Do While .Range("A" & RowCount) <> ""
      FName = .Range("A" & RowCount)
      Category = .Range("B" & RowCount)
      Col = .Range("C" & RowCount)
      Location = .Range("D" & RowCount)

      If Dir(Folder & FName & ".txt") <> "" Then
         With Sheets("Temp1")
            .Cells.ClearContents
            .Columns("B:C").NumberFormat = "@"
            With .QueryTables.Add( _
               Connection:="TEXT;" & Folder & FName & "..txt", _
                  Destination:=.Range("A1"))

               .Name = "Test"
               .FieldNames = True
               .SaveData = True
               .AdjustColumnWidth = True
               .RefreshPeriod = 0
               .TextFileStartRow = 10
               .TextFileParseType = xlDelimited
               .TextFileOtherDelimiter = "|"
               .TextFileColumnDataTypes = Array(1, 2, 2, 1, 1, 1, 1,
1, 1, 1, 1, 1)
               .Refresh BackgroundQuery:=False
            End With

            Set LastCell = .Cells(Rows.Count, Col).End(xlUp)
            Set CopyRange = .Range(Cells(1, Col), LastCell)

         End With

         With Sheets("Data")
            LastRow = .Range("B" & Rows.Count).End(xlUp).Row
            NewRow = LastRow + 1
            CopyRange.Copy Destination:=.Range("B" & NewRow)
            LastRow = .Range("B" & Rows.Count).End(xlUp).Row
            .Range("A" & NewRow & ":A" & LastRow) = Category
            .Range("C" & NewRow & ":C" & LastRow) = Location
         End With
      End If
      RowCount = RowCount + 1
   Loop
End With

Sheets("Temp1").Cells.ClearContents
Sheets("Data").Cells.EntireColumn.AutoFit

End Sub- Hide quoted text -

- Show quoted text -