Page 1 of 1
Pro & Rux file searcher
Posted: Wed Oct 14, 2015 3:17 pm
by iansdigby
Yet another Excel VBA TM1 text file searcher that finds:
- CellPut/Gets in .Pro files (shows Tab and line no.), with option to find a cube name as well
- DB( in .Rux files
- Specific text
*** New *** Action button property searcher
PLEASE NOTE The new action button searcher requires TM1 client to be installed, and
you must create a reference to the 'APPLIX TM1 CUBE VIEWING CONTROLS FOR EXCEL' within the VBE
Can search all files in a folder or just one.
NOT WARRANTED IN ANY WAY. I am only sharing what I use at work.
!!USE AT YOUR PERIL!!
##### Update 2015-12-18, works better. #####
##### Update 2016-01-21, includes Action Button properties search. #####
##### Update 2017-07-14, general functional improvements. #####
Re: Pro & Rux file searcher
Posted: Wed Oct 14, 2015 3:38 pm
by TrevorGoss
Just for a bit of fun, I thought I would demonstrate some Python code that can do the same thing
## module
import os
## path to data folder
vPath = '\\\\abcde12345\\z$\\TM1 Server \\Data\\'
## for each file that ends in 'pro
for f in os.listdir(vPath):
if f.endswith('.pro'):
proFile = open(vPath+f)
for each line in the file
for line in proFile:
if 'CellPutN' in line:
print "file: " + f + "line: " + line
This script loops around the directory to find each process file and if it finds it, it opens it up and looks for the 'CellPutN' In a line.
Although VBA has the API, python is simpler, cleaner, layman friendly and the interpreter is free and easy to install. That being said, it is based around indentation of code, so a simple copy and paste will not always work.
You can easily edit this TI to take in user inputs, arguments and what not.
Re: Pro & Rux file searcher
Posted: Mon Oct 19, 2015 6:28 am
by sachin
May I make suggestion to include CellIncrementN a part of the search routine as well?
Re: Pro & Rux file searcher
Posted: Mon Oct 19, 2015 7:47 am
by TrevorGoss
May I make suggestion to include CellIncrementN a part of the search routine as well?
You can make that suggestion, you can stick a bunch of search routines, or options in an array and check if the string in a line of a file contains one of those items.
Re: Pro & Rux file searcher
Posted: Mon Oct 19, 2015 9:43 pm
by PlanningDev
FYI this kind of search can be done using the RestApi. I would recommend downloading the chrome "Advanced Rest Client" if you want to play around with it.
Code: Select all
http://tm1server:api-port/api/v1/Processes?$filter=contains(PrologProcedure, 'ViewZeroOut') or contains(MetadataProcedure, 'ViewZeroOut') or contains(DataProcedure, 'ViewZeroOut') or contains(EpilogProcedure, 'ViewZeroOut')
You can replace ViewZeroOut with any text and it will check the normal 4 tabs of all Processes for the text. The RestAPI can be handy for a few things including impact analysis.
Re: Pro & Rux file searcher
Posted: Mon Oct 19, 2015 10:39 pm
by iansdigby
this kind of search can be done using the RestApi
.
Unless of course one is using TM1 v9.x. And a lot of people are still!
Re: Pro & Rux file searcher
Posted: Mon Oct 19, 2015 11:04 pm
by Alan Kirk
iansdigby wrote:this kind of search can be done using the RestApi
.
Unless of course one is using TM1 v9.x. And a lot of people are still!
I'm wondering how many will be for long though. I know that the monthly polls aren't scientific but I was surprised to see that out of 39 responders in the
September poll (including moi) not a single one expected to be on version 9 or earlier by the end of the year, with support dropping off for the V9 series.
Even though I still have some servers to be migrated I'm no longer developing anything (significant) outside of REST. (Unless of course it's Excel based, which brings us back to the original post.)
Re: Pro & Rux file searcher
Posted: Tue Oct 20, 2015 2:03 am
by kaazimraza
Hi guys,
I use a PowerShell script to search into rules & processes. It is not very clean as it returns the file names as well that duplicates search results but this script is quite quick and has made my development & maintenance quite easy, especially with TM1 10.1
Code: Select all
$Path = "Path of data folder"
$Text = "text to find"
$PathArray = @()
$Results = "Full Output Path\File Name.txt"
# This code snippet gets all the files in $Path that end in ".pro".
Get-ChildItem $Path -Filter "*.pro" |
Where-Object { $_.Attributes -ne "Directory"} |
ForEach-Object {
If (Get-Content $_.FullName | Select-String -Pattern $Text) {
$PathArray += $_.FullName
$PathArray += $_.FullName
}
}
$PathArray | % {$_} | Out-File $Results
Re: Pro & Rux file searcher
Posted: Tue Oct 20, 2015 7:55 am
by Wim Gielis
kaazimraza wrote:It is not very clean as it returns the file names as well that duplicates search results
What do you expect if you have:
Code: Select all
$PathArray += $_.FullName
$PathArray += $_.FullName
in your code ? Twice the same line of code ?
But thanks for sharing in any case.
Re: Pro & Rux file searcher
Posted: Tue Oct 20, 2015 9:23 am
by kaazimraza
Wim Gielis wrote:kaazimraza wrote:It is not very clean as it returns the file names as well that duplicates search results
What do you expect if you have:
Code: Select all
$PathArray += $_.FullName
$PathArray += $_.FullName
in your code ? Twice the same line of code ?
But thanks for sharing in any case.
Thanks Wim, for pointing it out.
Re: Pro & Rux file searcher
Posted: Wed Oct 21, 2015 12:27 pm
by iansdigby
Great to see these Python, REST API and other solutions. I feel all of my 62 years.
The great thing about Excel and VBA from an operational point of view is that it exists in situ, in most large coprorate environments (certainly the ones I've been in), so there is a ready-made programming envrionment there. No need to download/install other tools, which can be a difficult procedure to get past ever-more vigilant IT departments.
VBA to my mind, for all its clunkiness, has been a great credit to Microsoft and, despite its efforts to deprecate it over the years (remember VSTO and .NET?), still going strong everywhere in the financial world.
Alan, very interesting stats on upgrades. I wonder, of those that will go to v10x, how many will be using the REST API in anger. And if so, what tools will predominate in that space?