Pro & Rux file searcher
-
- Community Contributor
- Posts: 109
- Joined: Thu Feb 26, 2009 8:44 am
- OLAP Product: TM1
- Version: 9 + 10 + Plan An
- Excel Version: All
- Location: Isle of Wight, UK
Pro & Rux file searcher
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. #####
- 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. #####
- Attachments
-
- TM1 Utils ID.xls
- (257.5 KiB) Downloaded 291 times
Last edited by iansdigby on Fri Jul 14, 2017 1:32 pm, edited 13 times in total.
"the earth is but one country, and mankind its citizens" - Baha'u'llah
-
- Community Contributor
- Posts: 217
- Joined: Thu Aug 15, 2013 9:05 am
- OLAP Product: TM1
- Version: 10.2.1.1
- Excel Version: 14.0.6129.5000
Re: Pro & Rux file searcher
Just for a bit of fun, I thought I would demonstrate some Python code that can do the same thing
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.
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.
## 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
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.
- sachin
- Posts: 92
- Joined: Fri Jan 15, 2010 9:54 pm
- OLAP Product: Transformer,SSAS, EP, TM1
- Version: 7.3 2005 10.1 10.1.1
- Excel Version: 2013
- Contact:
Re: Pro & Rux file searcher
May I make suggestion to include CellIncrementN a part of the search routine as well?
Check out my blog for some good information on TM1, SPSS
-
- Community Contributor
- Posts: 217
- Joined: Thu Aug 15, 2013 9:05 am
- OLAP Product: TM1
- Version: 10.2.1.1
- Excel Version: 14.0.6129.5000
Re: Pro & Rux file searcher
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.May I make suggestion to include CellIncrementN a part of the search routine as well?
-
- Community Contributor
- Posts: 349
- Joined: Tue Aug 17, 2010 6:31 am
- OLAP Product: Planning Analytics
- Version: 2.0.5
- Excel Version: 2016
Re: Pro & Rux file searcher
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.
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.
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')
-
- Community Contributor
- Posts: 109
- Joined: Thu Feb 26, 2009 8:44 am
- OLAP Product: TM1
- Version: 9 + 10 + Plan An
- Excel Version: All
- Location: Isle of Wight, UK
Re: Pro & Rux file searcher
.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!
"the earth is but one country, and mankind its citizens" - Baha'u'llah
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Pro & Rux file searcher
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.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!
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.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 95
- Joined: Mon Jun 25, 2012 6:58 am
- OLAP Product: TM1, SSAS, Power BI
- Version: 10.2.2
- Excel Version: 2016
Re: Pro & Rux file searcher
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
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
Thanks,
Kaz
Kaz
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Pro & Rux file searcher
What do you expect if you have:kaazimraza wrote:It is not very clean as it returns the file names as well that duplicates search results
Code: Select all
$PathArray += $_.FullName
$PathArray += $_.FullName
But thanks for sharing in any case.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 95
- Joined: Mon Jun 25, 2012 6:58 am
- OLAP Product: TM1, SSAS, Power BI
- Version: 10.2.2
- Excel Version: 2016
Re: Pro & Rux file searcher
Thanks Wim, for pointing it out.Wim Gielis wrote:What do you expect if you have:kaazimraza wrote:It is not very clean as it returns the file names as well that duplicates search results
in your code ? Twice the same line of code ?Code: Select all
$PathArray += $_.FullName $PathArray += $_.FullName
But thanks for sharing in any case.
Thanks,
Kaz
Kaz
-
- Community Contributor
- Posts: 109
- Joined: Thu Feb 26, 2009 8:44 am
- OLAP Product: TM1
- Version: 9 + 10 + Plan An
- Excel Version: All
- Location: Isle of Wight, UK
Re: Pro & Rux file searcher
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?
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?
"the earth is but one country, and mankind its citizens" - Baha'u'llah