Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COMPILE LOG FROM INFO WITHIN MULTIPLE TEXT FILES | Excel Discussion (Misc queries) | |||
Extract Info from Multiple files | Excel Worksheet Functions | |||
I'm trying to compile text from multiple worksheets... | Excel Discussion (Misc queries) | |||
Macro copying info/data in multiple excel files into one summary file. | Excel Programming | |||
Opening Multiple files and Copying the info all to one other sheet | Excel Discussion (Misc queries) |