Back to the main data prep VBA page
This is an old script I created that imports HTML based reports into Excel, searches for the target headers in the file, then pulls key data I specified for the OLTP style application I was tuning. This script would loop through all *.html files located in the same directory. The output is a single formatted Excel sheet in a time series view with the metrics I was tracking for this app spanning multiple html files.
Sub importAWR()
'August 2009
'routine to import 24 hours of AWR snaps
'logic allows for multi-node imports on RAC environments (up to 4 nodes)
'point to a directory and import all html based reports
'text based reports are not supported at this time
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Call unHideWS
Application.StatusBar = "Import AWR data..."
'delete any existing AWR data
'''''''''''''''''''''''''''''
For Each ws In Sheets
If LCase(ws.Name) Like "awr*" Then Sheets(ws.Name).Delete
Next
Sheets.Add.Name = "AWRdata"
' declare variables
'''''''''''''''''''
Dim AWRpath As String
Dim nodeID As String
Dim nodeCount As Byte
Dim activePath As String
Dim fname As String, srcFileString As String
Dim tgt As String
Dim addr As String
'set variable values
''''''''''''''''''''
Dim MyFile As String
'Const ForReading = 1
srcFileString = Sheets("input").Range("C38")
Set objSrcAWR = CreateObject("Scripting.FileSystemObject")
MyPath = objSrcAWR.GetFile(srcFileString).ParentFolder.path & "\"
MyFile = Dir(MyPath)
AWRrow = 2
Do While MyFile <> ""
If MyFile Like "*.html" Then
Sheets.Add.Name = "AWRtemp"
addr = "A1"
'import data
''''''''''''
Sheets("AWRtemp").Select
Range(addr).Select
Application.StatusBar = "Importing AWR report number " & AWRrow - 1 & "..."
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///" & MyPath & MyFile & "" _
, Destination:=Range(addr))
.Name = fname & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'copy AWR data
'''''''''''''''
AWRcolumn = 1
'import header and timestamps
'''''''''''''''''''''''''''''
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Instance", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(1, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Snap Id", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(1, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(2, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Snap Time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(1, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(2, 0)
AWRcolumn = AWRcolumn + 1
'import basic statistics
''''''''''''''''''''''''
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Logical reads:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = "=ROUNDDOWN((D" & AWRrow & " - ROUNDDOWN(D" & AWRrow & ",0))*24,0)"
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(0, 1)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(2, 1)
AWRcolumn = AWRcolumn + 1
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="ordered by wait time desc, waits desc (idle events last)", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="db file sequential read", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(0, 4)
AWRcolumn = AWRcolumn + 1
Sheets("AWRtemp").Cells(1, 1).Activate
'import BCHR
''''''''''''
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Buffer Hit %:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(0, 1)
AWRcolumn = AWRcolumn + 1
'import logical read activity
'''''''''''''''''''''''''''''
Cells.Find(What:="Total Logical Reads:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(8, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(8, 5)
AWRcolumn = AWRcolumn + 2
Sheets("AWRtemp").Cells(1, 1).Activate
'import physical read activity
''''''''''''''''''''''''''''''
Cells.Find(What:="Total Physical Reads:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
On Error Resume Next
If Err.Number <> 0 Then
AWRcolumn = AWRcolumn + 11
Resume top5exec:
End If
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 5)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(8, 2)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(8, 5)
AWRcolumn = AWRcolumn + 2
'import top 5 timed events
''''''''''''''''''''''''''
top5exec:
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Top 5 Timed Events", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Err.Number <> 0 Then
Cells.Find(What:="Top 5 Timed Foreground Events", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End If
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(3, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(3, 4)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(4, 4)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(5, 4)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(6, 4)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 0)
AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(AWRrow, AWRcolumn) = ActiveCell.Offset(7, 4)
AWRcolumn = AWRcolumn + 1
'query search
'''''''''''''
If Sheets("input").Range("C38") = "" Then GoTo skipQuery
Dim queryID As String
For q = 1 To 5
queryID = Sheets("input").Range("C" & q + 39 & "")
queryCOL = AWRcolumn + (q - 1) * 3
tgtADDR = ""
Sheets("AWRtemp").Cells(1, 1).Activate
Cells.Find(What:="Total Buffer Gets: ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
tgtADDR = Cells.Find(What:=queryID, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
If tgtADDR <> "" Then
Sheets("AWRdata").Cells(1, queryCOL) = queryID
Sheets("AWRdata").Cells(AWRrow, queryCOL) = Sheets("AWRtemp").Range(tgtADDR).Offset(0, -6)
Sheets("AWRdata").Cells(1, queryCOL).Offset(0, 1) = "execs"
Sheets("AWRdata").Cells(AWRrow, queryCOL).Offset(0, 1) = Sheets("AWRtemp").Range(tgtADDR).Offset(0, -5)
Sheets("AWRdata").Cells(1, queryCOL).Offset(0, 2) = "reads/exec"
Sheets("AWRdata").Cells(AWRrow, queryCOL).Offset(0, 2) = Sheets("AWRtemp").Range(tgtADDR).Offset(0, -4)
End If
Next q
skipQuery:
AWRrow = AWRrow + 1
Sheets("AWRtemp").Delete
End If
MyFile = Dir
Loop
'sort data based on begin snap
''''''''''''''''''''''''''''''
lastRow = Sheets("AWRdata").Cells(Rows.Count, 1).End(xlUp).row
Columns("A:BZ").Activate
ActiveWorkbook.Worksheets("AWRdata").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AWRdata").Sort.SortFields.Add Key:=Range("B2:B" & lastRow & "") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AWRdata").Sort
.SetRange Range("A1:BZ" & lastRow & "")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'insert titles
''''''''''''''
AWRcolumn = 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "instance": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "begin snap": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "end snap": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "begin time": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "end time": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "hour": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "lrps": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "prps": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "read ms": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "BCHR": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by lr #1": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by lr #2": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by lr #3": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by lr #4": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by lr #5": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 2
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by pr #1": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by pr #2": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by pr #3": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by pr #4": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "seg by pr #5": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "reads": AWRcolumn = AWRcolumn + 2
Sheets("AWRdata").Cells(1, AWRcolumn) = "top 5, event 1": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "%": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "top 5, event 2": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "%": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "top 5, event 3": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "%": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "top 5, event 4": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "%": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "top 5, event 5": AWRcolumn = AWRcolumn + 1
Sheets("AWRdata").Cells(1, AWRcolumn) = "%": AWRcolumn = AWRcolumn + 1
'apply formatting
'''''''''''''''''
Sheets("AWRdata").Range("A1:BZ1").Font.Bold = True
Sheets("AWRdata").Range("A1:BZ" & lastRow & "").Font.Size = 8
Sheets("AWRdata").Range("A1:BZ1").Font.Underline = xlUnderlineStyleSingle
Sheets("AWRdata").Range("F2:AD" & lastRow & "").NumberFormat = "#,##0"
Sheets("AWRdata").Range("A1:AN1").HorizontalAlignment = xlRight
Sheets("AWRdata").Columns("D:E").NumberFormat = "m/d/yy h:mm;@"
Sheets("AWRdata").Range("J1:BZ" & lastRow & "").Font.ColorIndex = 48
Sheets("AWRdata").Range("AE2:BZ" & lastRow & "").NumberFormat = "#,##0.0"
Sheets("AWRdata").Range("J2:J" & lastRow & "").NumberFormat = "#,##0.00"
Sheets("AWRdata").Cells.EntireColumn.AutoFit
ActiveWindow.DisplayGridlines = False
Sheets("AWRdata").Columns("J:J").Activate
ActiveWindow.FreezePanes = True
Sheets("AWRdata").Tab.Color = 65535
Sheets("AWRdata").PageSetup.Orientation = xlLandscape
Sheets("AWRdata").PageSetup.LeftMargin = Application.InchesToPoints(0.5)
Sheets("AWRdata").PageSetup.RightMargin = Application.InchesToPoints(0.5)
Sheets("AWRdata").PageSetup.TopMargin = Application.InchesToPoints(0.5)
Sheets("AWRdata").PageSetup.BottomMargin = Application.InchesToPoints(0.5)
Sheets("AWRdata").PageSetup.FitToPagesWide = 1
Sheets("AWRdata").PageSetup.FitToPagesTall = 10
Sheets("AWRdata").Move After:=Sheets("report.overlay")
'collect summary stats
''''''''''''''''''''''
For AWRsum = 2 To lastRow
Sheets("report").Cells(Sheets("AWRdata").Cells(AWRsum, 6) + 56, 14) = _
Sheets("report").Cells(Sheets("AWRdata").Cells(AWRsum, 6) + 56, 14) + _
Sheets("AWRdata").Cells(AWRsum, 7)
Sheets("report").Cells(Sheets("AWRdata").Cells(AWRsum, 6) + 56, 15) = _
Sheets("report").Cells(Sheets("AWRdata").Cells(AWRsum, 6) + 56, 15) + _
Sheets("AWRdata").Cells(AWRsum, 8)
Next
For cleanRow = 56 To 79
Sheets("report").Cells(cleanRow, 15) = Sheets("report").Cells(cleanRow, 14) _
/ (Sheets("report").Cells(cleanRow, 14) + Sheets("report").Cells(cleanRow, 15))
Next cleanRow
'reset environment variables
''''''''''''''''''''''''''''
Call HideWS
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = "AWR import complete."
End Sub