ExcelBanter - COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES (https://www.excelbanter.com/excel-programming/425166-compile-log-info-within-multiple-text-files.html)

COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

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.

COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

What have you go so far?

Tim

"Sinner" wrote in message
...
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.

COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

On Mar 7, 9:15*am, "Tim Williams" wrote:
What have you go so far?

Tim

"Sinner" wrote in message

...

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

Help required... COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

On Mar 7, 3:35*pm, WoW wrote:
On Mar 7, 9:15*am, "Tim Williams" wrote:

What have you go so far?

Tim

"Sinner" wrote in message

...

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 -