COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES

[Angela]

Hi,

File is at link: http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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.

 

[Joel]

Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format. the imported file got split into two columns which make
the macro harder to write. I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.

 

[Sinner]

Angela: Can you post you log file on savefile.com?  In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format.  the imported file got split into two columns which make
the macro harder to write.  I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the filegot
imported into excel.

Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt

Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.

Example:
V0963001 000429183300 000429283299 100000 01-Sep-08
V0963001 000429182200 000429282199 100000 01-Sep-08

Thx.

 

[Sinner]

Angela: Can you post you log file on savefile.com?  In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format.  the imported file got split into two columns which make
the macro harder to write.  I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the filegot
imported into excel.

Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html


Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.


Example:
V0963001 000429183300 000429283299 100000 01-Sep-08
V0963001 000429182200 000429282199 100000 01-Sep-08


Thx.

 

[Angela]

Angela: Can you post you log file on savefile.com?  In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format.  the imported file got split into two columns which make
the macro harder to write.  I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the filegot
imported into excel.

Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html


Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.


Example:
V0963001 000429183300 000429283299 100000 01-Sep-08
V0963001 000429182200 000429282199 100000 01-Sep-08


Thx.

 

[Joel]

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet. Here is what I havew so far. I didn't remove the
duplicates. thought that should be a sepertate macro.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String


Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

'format column E
Columns("E").NumberFormat = "DD-MMM-YY"

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = ID
Range("B" & RowCount) = Num1
Range("C" & RowCount) = Num2
Range("D" & RowCount) = Vou
Range("E" & RowCount) = FileDate
RowCount = RowCount + 1

End If


fin.Close
FName = Dir()
Loop


End Sub

Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format. the imported file got split into two columns which make
the macro harder to write. I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.

Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file at http://www.ziddu.com/download/3784205/FILE2.txt.html


Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.


Example:
V0963001 000429183300 000429283299 100000 01-Sep-08
V0963001 000429182200 000429282199 100000 01-Sep-08


Thx.

 

[Angela]

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet.  Here is what I havew so far.  I didn't remove the
duplicates.  thought that should be a sepertate macro.

Sub GetLogs()
   Dim objShell As Object, objFolder As Object
   Dim ID As String
   Dim Num1 As String
   Dim Num2 As String
   Dim Vou As String

   Const ForReading = 1, ForWriting = -2, ForAppending = 3
   Const Start = "Start:"

   TABCh = Chr(9)

   StartLen = Len(Start)

   Set objShell = CreateObject("Shell.Application")
   Set fs = CreateObject("Scripting.FileSystemObject")

   On Error Resume Next
   Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ",&H1&)
   On Error GoTo 0

   If objFolder Is Nothing Then
      MsgBox ("Cannot open directory -xit Macro")
      Exit Sub
   End If

   Set oFolderItem = objFolder.Items.Item
   Folder = oFolderItem.Path

   'format column E
   Columns("E").NumberFormat = "DD-MMM-YY"

   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   RowCount = LastRow + 1

   FName = Dir(Folder & "\" & "*.txt")
   Do While FName <> ""
      Set fin = fs.OpenTextFile(Folder & "\" & FName, _
         ForReading, TristateFalse)

      FileErr = False
      LineNumber = 0
      Do While fin.AtEndOfStream <> True
         ReadData = fin.readline
         LineNumber = LineNumber + 1

         Select Case LineNumber
            Case 2
               If InStr(ReadData, "Start:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
                  FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
                  FileDate = Trim(FileDate)
                  FileDate = Replace(FileDate, ".", "/")
               End If

            Case 3
               If InStr(ReadData, "Order:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  ID = Left(ReadData, 15)
                  ID = Mid(ID, 7)
                  ID = "V" & Left(ID, 4) & Mid(ID, 7)

                  'remove everything up to and including the 2nd Pack
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Trim(Replace(ReadData, TABCh, ""))
                  Num1 = Left(ReadData, 10) & "00"

                  'read past the word "TO"
                  ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
                  Num2 = Left(ReadData, 10) & "99"

                  'get the number after the colon
                  Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

               End If

            Case 4
               Exit Do
         End Select

      Loop

      If FileErr = False Then
         Range("A" & RowCount) = ID
         Range("B" & RowCount) = Num1
         Range("C" & RowCount) = Num2
         Range("D" & RowCount) = Vou
         Range("E" & RowCount) = FileDate
         RowCount = RowCount + 1

      End If

      fin.Close
      FName = Dir()
   Loop

End Sub

Angela: Can you post you log file on savefile.com?  In this case itwill be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format.  the imported file got split into two columns which make
the macro harder to write.  I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.
:
Hi,
File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file athttp://www.ziddu.com/download/3784205/FILE2.txt.html

Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.

Example:
V0963001    000429183300        000429283299    100000  01-Sep-08
V0963001    000429182200        000429282199    100000  01-Sep-08

Thx.- Hide quoted text -

- Show quoted text -

Dear Joel,

I have uploaded the excel file at
http://www.ziddu.com/download/3786047/COMPILELOGFROMINFOWITHINMULTIPLETEXTFILES.xls.html
Hope you can check the format there.
Let me check the above code, will get back to you soon.
Thx alot.

Waiting for reply.

 

[Joel]

Don't use ziddu. there is too much adware at this site. I made the
formatting changes. Try this new code.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String


Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

If Range("A1") = "" Then

'format column E
Columns("A").NumberFormat = "#."
Columns("G").NumberFormat = "DD-MMM-YYYY"
Range("A1") = "S#"
Range("B1") = "File#"
Range("C1") = "Base#"
Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "VG#"
Range("G1") = "Date"
End If

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = (RowCount - 1) & "."
Range("B" & RowCount) = ID
Range("D" & RowCount) = Num1
Range("E" & RowCount) = Num2
Range("F" & RowCount) = VG
Range("G" & RowCount) = FileDate
RowCount = RowCount + 1

End If


fin.Close
FName = Dir()
Loop


End Sub

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet. Here is what I havew so far. I didn't remove the
duplicates. thought that should be a sepertate macro.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String

Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

'format column E
Columns("E").NumberFormat = "DD-MMM-YY"

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = ID
Range("B" & RowCount) = Num1
Range("C" & RowCount) = Num2
Range("D" & RowCount) = Vou
Range("E" & RowCount) = FileDate
RowCount = RowCount + 1

End If

fin.Close
FName = Dir()
Loop

End Sub

Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format. the imported file got split into two columns which make
the macro harder to write. I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.

:
Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid points Joel.
Sorry but savefile is down.
I have uploaded the *.txt log file athttp://www.ziddu.com/download/3784205/FILE2.txt.html

Also Joel I was thinking that if two text files are same inside(not
the file names out side being same, I'm referring to the content &
that too the 15 digits file#V0963001 being same & ranges being
different, it should be populated in another sheet named "Duplicate
Entry" with all details.

Example:
V0963001 000429183300 000429283299 100000 01-Sep-08
V0963001 000429182200 000429282199 100000 01-Sep-08

Thx.- Hide quoted text -

- Show quoted text -

Dear Joel,

I have uploaded the excel file at
http://www.ziddu.com/download/3786047/COMPILELOGFROMINFOWITHINMULTIPLETEXTFILES.xls.html
Hope you can check the format there.
Let me check the above code, will get back to you soon.
Thx alot.

Waiting for reply.

 

[Angela]

Don't use ziddu.  there is too much adware at this site.  I made the
formatting changes.  Try this new code.

Sub GetLogs()
   Dim objShell As Object, objFolder As Object
   Dim ID As String
   Dim Num1 As String
   Dim Num2 As String
   Dim Vou As String

   Const ForReading = 1, ForWriting = -2, ForAppending = 3
   Const Start = "Start:"

   TABCh = Chr(9)

   StartLen = Len(Start)

   Set objShell = CreateObject("Shell.Application")
   Set fs = CreateObject("Scripting.FileSystemObject")

   On Error Resume Next
   Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ",&H1&)
   On Error GoTo 0

   If objFolder Is Nothing Then
      MsgBox ("Cannot open directory -xit Macro")
      Exit Sub
   End If

   Set oFolderItem = objFolder.Items.Item
   Folder = oFolderItem.Path

   If Range("A1") = "" Then

      'format column E
      Columns("A").NumberFormat = "#."
      Columns("G").NumberFormat = "DD-MMM-YYYY"
      Range("A1") = "S#"
      Range("B1") = "File#"
      Range("C1") = "Base#"
      Range("D1") = "Start"
      Range("E1") = "End"
      Range("F1") = "VG#"
      Range("G1") = "Date"
   End If

   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   RowCount = LastRow + 1

   FName = Dir(Folder & "\" & "*.txt")
   Do While FName <> ""
      Set fin = fs.OpenTextFile(Folder & "\" & FName, _
         ForReading, TristateFalse)

      FileErr = False
      LineNumber = 0
      Do While fin.AtEndOfStream <> True
         ReadData = fin.readline
         LineNumber = LineNumber + 1

         Select Case LineNumber
            Case 2
               If InStr(ReadData, "Start:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
                  FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
                  FileDate = Trim(FileDate)
                  FileDate = Replace(FileDate, ".", "/")
               End If

            Case 3
               If InStr(ReadData, "Order:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  ID = Left(ReadData, 15)
                  ID = Mid(ID, 7)
                  ID = "V" & Left(ID, 4) & Mid(ID, 7)

                  'remove everything up to and including the 2nd Pack
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Trim(Replace(ReadData, TABCh, ""))
                  Num1 = Left(ReadData, 10) & "00"

                  'read past the word "TO"
                  ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
                  Num2 = Left(ReadData, 10) & "99"

                  'get the number after the colon
                  VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

               End If

            Case 4
               Exit Do
         End Select

      Loop

      If FileErr = False Then
         Range("A" & RowCount) = (RowCount - 1) & "."
         Range("B" & RowCount) = ID
         Range("D" & RowCount) = Num1
         Range("E" & RowCount) = Num2
         Range("F" & RowCount) = VG
         Range("G" & RowCount) = FileDate
         RowCount = RowCount + 1

      End If

      fin.Close
      FName = Dir()
   Loop

End Sub

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet.  Here is what I havew so far.  I didn't remove the
duplicates.  thought that should be a sepertate macro.
Sub GetLogs()
   Dim objShell As Object, objFolder As Object
   Dim ID As String
   Dim Num1 As String
   Dim Num2 As String
   Dim Vou As String
   Const ForReading = 1, ForWriting = -2, ForAppending = 3
   Const Start = "Start:"
   TABCh = Chr(9)
   StartLen = Len(Start)
   Set objShell = CreateObject("Shell.Application")
   Set fs = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
   On Error GoTo 0
   If objFolder Is Nothing Then
      MsgBox ("Cannot open directory -xit Macro")
      Exit Sub
   End If
   Set oFolderItem = objFolder.Items.Item
   Folder = oFolderItem.Path
   'format column E
   Columns("E").NumberFormat = "DD-MMM-YY"
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   RowCount = LastRow + 1
   FName = Dir(Folder & "\" & "*.txt")
   Do While FName <> ""
      Set fin = fs.OpenTextFile(Folder & "\" & FName, _
         ForReading, TristateFalse)
      FileErr = False
      LineNumber = 0
      Do While fin.AtEndOfStream <> True
         ReadData = fin.readline
         LineNumber = LineNumber + 1
         Select Case LineNumber
            Case 2
               If InStr(ReadData, "Start:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
                  FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
                  FileDate = Trim(FileDate)
                  FileDate = Replace(FileDate, ".", "/")
               End If
            Case 3
               If InStr(ReadData, "Order:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  ID = Left(ReadData, 15)
                  ID = Mid(ID, 7)
                  ID = "V" & Left(ID, 4) & Mid(ID, 7)
                  'remove everything up to and including the 2nd Pack
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Trim(Replace(ReadData, TABCh, ""))
                  Num1 = Left(ReadData, 10) & "00"
                  'read past the word "TO"
                  ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
                  Num2 = Left(ReadData, 10) & "99"
                  'get the number after the colon
                  Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))
               End If
            Case 4
               Exit Do
         End Select
      Loop
      If FileErr = False Then
         Range("A" & RowCount) = ID
         Range("B" & RowCount) = Num1
         Range("C" & RowCount) = Num2
         Range("D" & RowCount) = Vou
         Range("E" & RowCount) = FileDate
         RowCount = RowCount + 1
      End If
      fin.Close
      FName = Dir()
   Loop
End Sub
:
Angela: Can you post you log file on savefile.com?  In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format.  the imported file got split into two columns which make
the macro harder to write.  I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.
:
Hi,
File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -
Dear Joel,
Valid

...

read more »- Hide quoted text -

- Show quoted text -

Thx Joel.
Pls suggest some file sharing site other than savefile & ziddu.
The code worked like a charm.
Two concerns if you can check.

- Can I get a better way to browse to file like when you click open in
excel and you get a box asking for file location & you can see files
also in view. I hope you get this.
- The date is Sep 01, 2008 and not Jan 08, 2008.
- The value which is 100000 is the difference of start & end. Need to
calculate that like (a-b+1) to get the value instead of read it from
log to make it authentic.
- Additional column in the end to get the file name so that in case
there is some problem, one can jump to that file and deal with it
instantly like duplicate text files, blank files.

Thx once again for your time and effort. Means lot to me.
I'm grateful.

 

[Joel]

Easy

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim VG As String


Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

If Range("A1") = "" Then

'format column E
Columns("A").NumberFormat = "#."
Columns("G").NumberFormat = "DD-MMM-YYYY"
Range("A1") = "S#"
Range("B1") = "File#"
Range("C1") = "Base#"
Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "VG#"
Range("G1") = "Date"
End If

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ReadData = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
StartDate = Left(ReadData, InStr(ReadData, "End:") - 1)
StartDate = Trim(StartDate)
StartDay = Left(StartDate, 2)
StartMonth = Mid(StartDate, 4, 2)
StartYear = Mid(StartDate, 7, 4)
StartDate = DateSerial(StartYear, StartMonth, StartDay)


End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'VG is the difference between Num1 and Num2
VG = Val(Num2) - Val(Num1) + 1
End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = (RowCount - 1) & "."
Range("B" & RowCount) = ID
Range("D" & RowCount) = Num1
Range("E" & RowCount) = Num2
Range("F" & RowCount) = VG
Range("G" & RowCount) = StartDate
RowCount = RowCount + 1

End If


fin.Close
FName = Dir()
Loop


End Sub

Don't use ziddu. there is too much adware at this site. I made the
formatting changes. Try this new code.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String

Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

If Range("A1") = "" Then

'format column E
Columns("A").NumberFormat = "#."
Columns("G").NumberFormat = "DD-MMM-YYYY"
Range("A1") = "S#"
Range("B1") = "File#"
Range("C1") = "Base#"
Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "VG#"
Range("G1") = "Date"
End If

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = (RowCount - 1) & "."
Range("B" & RowCount) = ID
Range("D" & RowCount) = Num1
Range("E" & RowCount) = Num2
Range("F" & RowCount) = VG
Range("G" & RowCount) = FileDate
RowCount = RowCount + 1

End If

fin.Close
FName = Dir()
Loop

End Sub

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet. Here is what I havew so far. I didn't remove the
duplicates. thought that should be a sepertate macro.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String

Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

'format column E
Columns("E").NumberFormat = "DD-MMM-YY"

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

If FileErr = False Then
Range("A" & RowCount) = ID
Range("B" & RowCount) = Num1
Range("C" & RowCount) = Num2
Range("D" & RowCount) = Vou
Range("E" & RowCount) = FileDate
RowCount = RowCount + 1

End If

fin.Close
FName = Dir()
Loop

End Sub

:
Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format. the imported file got split into two columns which make
the macro harder to write. I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.

:
Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid

...

 

[Joel]

I forgot the filename

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim VG As String


Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

If Range("A1") = "" Then

'format column E
Columns("A").NumberFormat = "#."
Columns("G").NumberFormat = "DD-MMM-YYYY"
Range("A1") = "S#"
Range("B1") = "File#"
Range("C1") = "Base#"
Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "VG#"
Range("G1") = "Date"
Range("H1") = "Filename"
End If

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ReadData = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
StartDate = Left(ReadData, InStr(ReadData, "End:") - 1)
StartDate = Trim(StartDate)
StartDay = Left(StartDate, 2)
StartMonth = Mid(StartDate, 4, 2)
StartYear = Mid(StartDate, 7, 4)
StartDate = DateSerial(StartYear, StartMonth, StartDay)


End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'VG is the difference between Num1 and Num2
VG = Val(Num2) - Val(Num1) + 1
End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = (RowCount - 1) & "."
Range("B" & RowCount) = ID
Range("D" & RowCount) = Num1
Range("E" & RowCount) = Num2
Range("F" & RowCount) = VG
Range("G" & RowCount) = StartDate
Range("H" & RowCount) = FName
RowCount = RowCount + 1

End If


fin.Close
FName = Dir()
Loop


End Sub

Don't use ziddu. there is too much adware at this site. I made the
formatting changes. Try this new code.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String

Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

If Range("A1") = "" Then

'format column E
Columns("A").NumberFormat = "#."
Columns("G").NumberFormat = "DD-MMM-YYYY"
Range("A1") = "S#"
Range("B1") = "File#"
Range("C1") = "Base#"
Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "VG#"
Range("G1") = "Date"
End If

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
VG = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

Loop

If FileErr = False Then
Range("A" & RowCount) = (RowCount - 1) & "."
Range("B" & RowCount) = ID
Range("D" & RowCount) = Num1
Range("E" & RowCount) = Num2
Range("F" & RowCount) = VG
Range("G" & RowCount) = FileDate
RowCount = RowCount + 1

End If

fin.Close
FName = Dir()
Loop

End Sub

I waiting for savefile to come back up so I can get the format correct for
the spreadsheet. Here is what I havew so far. I didn't remove the
duplicates. thought that should be a sepertate macro.

Sub GetLogs()
Dim objShell As Object, objFolder As Object
Dim ID As String
Dim Num1 As String
Dim Num2 As String
Dim Vou As String

Const ForReading = 1, ForWriting = -2, ForAppending = 3
Const Start = "Start:"

TABCh = Chr(9)

StartLen = Len(Start)

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory -xit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

'format column E
Columns("E").NumberFormat = "DD-MMM-YY"

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1

FName = Dir(Folder & "\" & "*.txt")
Do While FName <> ""
Set fin = fs.OpenTextFile(Folder & "\" & FName, _
ForReading, TristateFalse)

FileErr = False
LineNumber = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
LineNumber = LineNumber + 1

Select Case LineNumber
Case 2
If InStr(ReadData, "Start:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
FileDate = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
FileDate = Left(FileDate, InStr(FileDate, "End:") - 1)
FileDate = Trim(FileDate)
FileDate = Replace(FileDate, ".", "/")
End If

Case 3
If InStr(ReadData, "Order:") = 0 Then
MsgBox ("Bad Log File : " & FName)
FileErr = True
Exit Do
Else
ID = Left(ReadData, 15)
ID = Mid(ID, 7)
ID = "V" & Left(ID, 4) & Mid(ID, 7)

'remove everything up to and including the 2nd Pack
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
ReadData = Trim(Replace(ReadData, TABCh, ""))
Num1 = Left(ReadData, 10) & "00"

'read past the word "TO"
ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
Num2 = Left(ReadData, 10) & "99"

'get the number after the colon
Vou = Trim(Mid(ReadData, InStr(ReadData, ":") + 1))

End If

Case 4
Exit Do
End Select

If FileErr = False Then
Range("A" & RowCount) = ID
Range("B" & RowCount) = Num1
Range("C" & RowCount) = Num2
Range("D" & RowCount) = Vou
Range("E" & RowCount) = FileDate
RowCount = RowCount + 1

End If

fin.Close
FName = Dir()
Loop

End Sub

:
Angela: Can you post you log file on savefile.com? In this case it will be
easier to write a macro that opens the log file and place the data into the
worksheet rather than take the imported data from the worksheet and convert
it to your format. the imported file got split into two columns which make
the macro harder to write. I also need to see the column spacing and any
tabs (or other special characters) that may of been changed when the file got
imported into excel.

:
Hi,

File is at link:http://www.savefile.com/files/2029405
File contains sample of below required and also sample of text file
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 -

Dear Joel,

Valid

...

 

[Angela]

I forgot the filename

Sub GetLogs()
   Dim objShell As Object, objFolder As Object
   Dim ID As String
   Dim Num1 As String
   Dim Num2 As String
   Dim VG As String

   Const ForReading = 1, ForWriting = -2, ForAppending = 3
   Const Start = "Start:"

   TABCh = Chr(9)

   StartLen = Len(Start)

   Set objShell = CreateObject("Shell.Application")
   Set fs = CreateObject("Scripting.FileSystemObject")

   On Error Resume Next
   Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ",&H4001&)
   On Error GoTo 0

   If objFolder Is Nothing Then
      MsgBox ("Cannot open directory -xit Macro")
      Exit Sub
   End If

   Set oFolderItem = objFolder.Items.Item
   Folder = oFolderItem.Path

   If Range("A1") = "" Then

      'format column E
      Columns("A").NumberFormat = "#."
      Columns("G").NumberFormat = "DD-MMM-YYYY"
      Range("A1") = "S#"
      Range("B1") = "File#"
      Range("C1") = "Base#"
      Range("D1") = "Start"
      Range("E1") = "End"
      Range("F1") = "VG#"
      Range("G1") = "Date"
      Range("H1") = "Filename"
   End If

   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   RowCount = LastRow + 1

   FName = Dir(Folder & "\" & "*.txt")
   Do While FName <> ""
      Set fin = fs.OpenTextFile(Folder & "\" & FName, _
         ForReading, TristateFalse)

      FileErr = False
      LineNumber = 0
      Do While fin.AtEndOfStream <> True
         ReadData = fin.readline
         LineNumber = LineNumber + 1

         Select Case LineNumber
            Case 2
               If InStr(ReadData, "Start:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  ReadData = Mid(ReadData, InStr(ReadData, "Start") +
StartLen)
                  StartDate = Left(ReadData, InStr(ReadData, "End:") - 1)
                  StartDate = Trim(StartDate)
                  StartDay = Left(StartDate, 2)
                  StartMonth = Mid(StartDate, 4, 2)
                  StartYear = Mid(StartDate, 7, 4)
                  StartDate = DateSerial(StartYear, StartMonth, StartDay)

               End If

            Case 3
               If InStr(ReadData, "Order:") = 0 Then
                  MsgBox ("Bad Log File : " & FName)
                  FileErr = True
                  Exit Do
               Else
                  ID = Left(ReadData, 15)
                  ID = Mid(ID, 7)
                  ID = "V" & Left(ID, 4) & Mid(ID, 7)

                  'remove everything up to and including the 2nd Pack
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Mid(ReadData, InStr(ReadData, "Pack") + 4)
                  ReadData = Trim(Replace(ReadData, TABCh, ""))
                  Num1 = Left(ReadData, 10) & "00"

                  'read past the word "TO"
                  ReadData = Trim(Mid(ReadData, InStr(ReadData, "to") + 2))
                  Num2 = Left(ReadData, 10) & "99"

                  'VG is the difference between Num1 and Num2
                  VG = Val(Num2) - Val(Num1) + 1
               End If

            Case 4
               Exit Do
         End Select

      Loop

      If FileErr = False Then
         Range("A" & RowCount) = (RowCount - 1) & "."
         Range("B" & RowCount) = ID
         Range("D" & RowCount) = Num1
         Range("E" & RowCount) = Num2
         Range("F" & RowCount) = VG
         Range("G" & RowCount) = StartDate
         Range("H" & RowCount) = FName
         RowCount = RowCount + 1

      End If

      fin.Close
      FName = Dir()
   Loop

End Sub





...

read more »- Hide quoted text -

- Show quoted text -

Hey Joel,

The code worked fine.
However I would appreciate if you can get the browse to file just like
when one opens file in excel, with My recent documents, Desktop, My
Documents, My Computer, My Network Places on left, files/folder view
on right, bottom we have file name: & file type: where I can give file
name & location(address to file). In this case would just want this
window so that I can easily copy/paste address of folder and get to it
instead of browsing through existing window, clicking + to get to the
folder each time I have to.

Thanks once again for all the help.

 

[Joel]

I change the line below from 4001 to 4071. The new options will remeber the
last folder selected and also contain a box so you can paste in an address
rather than always have to select a path. See if you like this better. If
not I will do more research tomorrow.

Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&,
&H8&)

 

[Angela]

I change the line below from 4001 to 4071.  The new options will remeber the
last folder selected and also contain a box so you can paste in an address
rather than always have to select a path.  See if you like this better. If
not I will do more research tomorrow.

Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4071&,
&H8&)





...

read more »- Hide quoted text -

- Show quoted text -

Thx Jeol for your patience and help.
I have uploaded the file at http://www.savefile.com/files/2032417
This is what I'm looking for.

Thx once again. I'm grateful.

 

[Joel]

I have had your concerns previously with the options for Browse for Folders.
I didn't like the solutions I found so I went looking again today for a
better solution.

The Browse for Folders (common for all windows functions - WIN32) doesn't
have an option to give you sizes and dates. Browse for files doesn't give
you an option to pick folders. My solution in the past is to Use
GETOPENFILENAME and select a file. Then in VBA code is to extract the folder
Name from the file. I can easily do this if you want.

 

[Angela]

I have had your concerns previously with the options for Browse for Folders.  
I didn't like the solutions I found so I went looking again today for a
better solution.

The Browse for Folders (common for all windows functions - WIN32) doesn't
have an option to give you sizes and dates.  Browse for files doesn't give
you an option to pick folders.  My solution in the past is to Use
GETOPENFILENAME and select a file.  Then in VBA code is to extract the folder
Name from the file.  I can easily do this if you want.





...

read more »- Hide quoted text -

- Show quoted text -

Thx for the reply Joel.
Joel I think this will do since I just have to browse to the folder. I
will stick to your existing code. No addition required. Since then you
would also need to add multiple file select I think which I dnt want
right now.
Sure I will post incase I need that. For now this has saved me almost
a 3/4 days work since these are over 2GB logs which first I download &
then compile the details afterward. Thanks a million. God bless. -
been great help.