Page 1 of 1
Read *.pro in excel vba
Posted: Fri Jan 10, 2014 1:20 am
by yyi
Hi Everyone,
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
* alt-f11 > tools > references > ms scripting runtime > tick!
Re: Read *.pro in excel vba
Posted: Fri Jan 10, 2014 4:42 am
by macsir
A good idea but can't we just open *.pro file in notepad and copy/paste?
Re: Read *.pro in excel vba
Posted: Fri Jan 10, 2014 6:52 am
by Duncan P
Re: Read *.pro in excel vba
Posted: Fri Jan 10, 2014 11:37 am
by Wim Gielis
Good point Duncan.
From my perspective, I dó see a value in these kind of routines:
- IF they are generic
- IF they can do (far) more than getting the SQL for the data source.
Personally I would do this from TM1 itself, rather than from VBA (though I'm not against Excel VBA
)
I would fill a cube holding that information (by process, by "measure") and also by process parameter (name of the parameter, default value, type, question).
Lastly, have Excel or Word VBA generate some start of documentation (for example, Word document listing all processes, with type of data source and data source definition).
The Word doc would be preformatted with styles.
That would be a real timesaver. Not sure if there are other (free) tools that can go "that far"?
Re: Read *.pro in excel vba
Posted: Fri Jan 10, 2014 11:40 am
by Wim Gielis
@yyi
Thank you for sharing!
If you only want to get the SQL statements, your selection of TI processes is too broad.
Property 562 should be ODBC:
562,"ODBC"
Next, please note that you can also set the Data source dynamically in the Prolog tab of a TI process...
Lastly, I would probably use the Dir function instead of Filescriptingobject.
Re: Read *.pro in excel vba
Posted: Tue Jan 21, 2014 1:09 pm
by rmackenzie
Re: Read *.pro in excel vba
Posted: Tue Jan 21, 2014 1:20 pm
by tomok
Preach on, brother! I'd say this illustration applies to about half the stuff asked on this board. I'm constantly amazed at the lack of common sense coming from some of the posters.
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 1:39 am
by yyi
by rmackenzie » Tue Jan 21, 2014 11:09 pm
Duncan P wrote:Is it worth the time?
Probably not!
don't know what you guys were thinking, but i needed to get sap table names from around 300 processes that were written by consultants long gone. there were no documentation. if you have better strategy, let us know.
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 2:44 am
by macsir
yyi wrote:
don't know what you guys were thinking, but i needed to get sap table names from around 300 processes that were written by consultants long gone. there were no documentation. if you have better strategy, let us know.
copy all *.pro files into your local drive and search your key words in notepad++
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 4:57 am
by yyi
can't argue with that. sounds like you're a notepad+ preferred person
I hope somebody reading the post got some value from it
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 5:55 am
by Duncan P
The killer is the bit where you have to extract the table name from the SQL statement. That is in fact a lot more difficult in the general case than getting the SQL out of the .pro. I'm a programmer by trade and for me that part would probably flip the balance toward manual. If it were just 300 full statements the VB (or APL in my case) would easily be worth it.
I wasn't saying don't bother, just that the payback is often over-estimated (by me at least - but then I like programming). And like Wim, thanks for sharing.
NB. as with any undocumented quirk that you have learned by inspecttion be careful to check with each new version that the format is the same and that your code still works as expected. Look e.g. at the log file format change in 9.5!
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 1:37 pm
by yyi
great! & i've deleted the temp file now but after it lists the process names and any sql on the next column in the activesheet (also does it in debug window), in my case it was copying some substring formulas down like =MID(C1,FIND("from",C1,1)+5,FIND(" ",C1,FIND("from",C1,1)+1-FIND("from",C1,1))) then sorting that column
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 6:58 pm
by Duncan P
So you don't have nested selects then. I had one project where I was importing using SQL generated by (and manually copied out of) Cognos Framework Manager which was all nested two or three levels deep. Ditto queries from the Cognos Controller database.
The other things that muck it up are joins and spaces in table names (which I am assuming you don't have as they are SAP tables), but it does make the general case a bit of a nightmare to code.
Re: Read *.pro in excel vba
Posted: Fri Jan 31, 2014 9:27 pm
by Alan Kirk
Duncan P wrote:NB. as with any undocumented quirk that you have learned by inspecttion be careful to check with each new version that the format is the same and that your code still works as expected. Look e.g. at the log file format change in 9.5!
Don't get me started.
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.)
The first time I tried to use it with some 9.5 logs it was a case of "what the...?" Iboglix had taken simple, uniformly structured flat files and "improved" them in the same way that Creative Assembly "improved" the user interface in Rome II. Which is to say, as one reviewer famously did, "like a sick dairy cow in a milking stall, there's s**t all over the place". And well documented s**t too; I recall this being in the relevant release notes for 9.5.x. No, wait... sorry, that actually happened in a dream sequence. Because even in the 9.5.2 Operations Guide (page 93) the original version of the log file structure is shown.
I'll be charitable and assume that crud like:
#LOG_FORMAT=1
#LOGID=2292
"2294",{actual useful log content}
#"2294","20140108215224","Change set 2294 complete : 6"
actually has some purpose and intent in the system beside cluttering up the files, requiring bespoke code to read them, and clogging up the arteries of your hard disk space with a few dozen extra megabytes per file. I'll grant you that coding around it was not wildly complex, but it's still time that I could have done without wasting.
I suppose it's possible that IBM could have alleviated the problem by
themselves writing a utility tool to do an offline query of the log files stretching back weeks or months and for multiple elements per cube without locking up the server with its woefully slow log search function. Though even if they did it would be hidden behind some ridiculously bloated web-based Java-infected front end that runs as slow as an arthritic Clydesdale. Lemmie check my Weatherzone app and see what the chances of that are:
Hell
Min: 150
Max: 320
Hot with a chance of Java-powered firestorms
Chance of snow: 0%
Hmm, no, don't see it happening. Guess it's back to coding our own, then.
