Pro & Rux file searcher

Post Reply
iansdigby
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

Post 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. #####
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
TrevorGoss
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

Post 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.
User avatar
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

Post by sachin »

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
TrevorGoss
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

Post 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.
PlanningDev
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

Post 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.
iansdigby
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

Post 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!
"the earth is but one country, and mankind its citizens" - Baha'u'llah
User avatar
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

Post 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.)
"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.
kaazimraza
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

Post 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

Thanks,

Kaz
Wim Gielis
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

Post 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.
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
kaazimraza
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

Post 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.
Thanks,

Kaz
iansdigby
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

Post 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?
"the earth is but one country, and mankind its citizens" - Baha'u'llah
Post Reply