|
Posted to microsoft.public.excel.programming
|
|
|
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 -
|