Here's just a bit of code to get Sql statements from TI processes in the data directory if
anyone's interested.
Code: Select all
Sub readViaFso()
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim iFolder As Folder, iFile As File, filePath As String, n As Integer, x As Integer
Dim ts As TextStream, tsLine As String, xR As Boolean, c As Range, y As Integer
Set iFolder = fso.GetFolder("E:\Cognos\TM1\Custom\TM1Data") 'directory containing *.pro
Set c = ActiveCell
For Each iFile In iFolder.Files
If iFile.Type = "PRO File" Then
n = n + 1: y = y + 1
c.Offset(y, 0) = iFile.Name: Debug.Print n; iFile.Name
Set ts = iFile.OpenAsTextStream(ForReading)
Do Until ts.AtEndOfStream
tsLine = ts.ReadLine
If InStr(tsLine, "566,") > 0 Then
While InStr(tsLine, "567,") = 0 And Not ts.AtEndOfLine
tsLine = ts.ReadLine
If InStr(tsLine, "567,") = 0 Then
If Not xR Then xR = True: x = x + 1: Debug.Print , x 'counter for odbc sql strings
y = y + 1: c.Offset(y, 1) = tsLine: Debug.Print , tsLine
End If
Wend
xR = False 'default for x counter
Exit Do 'exit text stream
End If
Loop
End If
Next
End Sub
)
Back in ye olden days I had written code to import log files into an Access database, back when I still used Access. Even though I rarely do these days, the code was still useful. (And before anyone starts, I might remind them of the reality of doing log file queries in the GUI; a maximum of one element from each dimension, 100 sodding rows after 100 sodding rows after 100 sodding rows. Not wildly useful when users ask "Who entered the budget values for these 5 departments for these 7 profit centres" (as they do not infrequently around here) and you find that it was done 3 months earlier.)