How can I import a column ExternalName rather than the column Description when replacing a SAS dataset using an IronPython script

[Rodrigo Juarez]

A little preablem first: I am a statistical programmer, but I don't know Python. I am working onupdates to a Spotfire.dxp thatuses an IronPython script to replace data tables (specficically SAS datasets) with a click of a button. The Spotfire version I am working with is TIBCO7.11.2.4

The current scriptimports the SAS table with the Descriptionsof the Columns (i.e. the SAS variable labels). I want to modify the script so that the API imports the ExternalName of the Columns (i.e. the SAS variable original names).

The original script is below:

# Replace Data from file

import clr

clr.AddReference("System.Windows.Forms")

from System.Windows.Forms import MessageBox

MessageBox.Show("Click 'OK' to start. This may take a while depends on the size of the data and your connection speed. ")

#from System.Windows.Forms import OpenFileDialog

from Spotfire.Dxp.Data import *

#for property control

from Spotfire.Dxp.Data import DataPropertyClass

 

 

myDataManager = Document.Data

def replace_data(path, newdatafile, olddata):

newpath='/'.join(path.split('\'))

file=newpath+"/"+newdatafile

ds=myDataManager.CreateFileDataSource(file)

table1 = Document.Data.Tables[olddata]

table1.ReplaceData(ds)

 

selection = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "selectDataSet").Value

 

#parse columns from selection

__path__ = pathparam

for property in selection:

for dtname in property.split(","):

newdataname=dtname+"."+datatype

 

 

#datatype and pathparam are Script parameters taking the values 'sas7bdat' and the directory path where the SAS dataset resides

 

 

replace_data( path = __path__, newdatafile =newdataname , olddata = dtname )

#add a datatime stamp name as "_DateOfLoading" on the table

destable=Document.Data.Tables[dtname]

cols = Document.Data.Tables[dtname].Columns

found,column=cols.TryGetValue("_DateOfLoading")

if found:

cols.Remove("_DateOfLoading")

cols.AddCalculatedColumn("_DateOfLoading", "DateTimeNow()");

MessageBox.Show("Done!")

My suspicion is that I need to add a couple of statments in the script to 'uncheck' the option "Use Description as column name (if available)" from the API and import the ExternalNames.

Something like:

# Replace Data from file

import clr

clr.AddReference("System.Windows.Forms")

from System.Windows.Forms import MessageBox

MessageBox.Show("Click 'OK' to start. This may take a while depends on the size of the data and your connection speed. ")

#from System.Windows.Forms import OpenFileDialog

from Spotfire.Dxp.Data import *

#for property control

from Spotfire.Dxp.Data import DataPropertyClass

#for column properties

from Spotfire.Dxp.Data import DataColumnProperties

 

 

 

myDataManager = Document.Data

def replace_data(path, newdatafile, olddata):

newpath='/'.join(path.split('\'))

file=newpath+"/"+newdatafile

ds=myDataManager.CreateFileDataSource(file)

table1 = Document.Data.Tables[olddata]

table1.ReplaceData(ds)

 

selection = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "selectDataSet").Value

selection = Document.Data.Properties.GetProperty(DataColumnPorperties).ExternalName

 

#parse columns from selection

__path__ = pathparam

for property in selection:

for dtname in property.split(","):

newdataname=dtname+"."+datatype

replace_data( path = __path__, newdatafile =newdataname , olddata = dtname )

#add a datatime stamp name as "_DateOfLoading" on the table

destable=Document.Data.Tables[dtname]

cols = Document.Data.Tables[dtname].Columns

found,column=cols.TryGetValue("_DateOfLoading")

if found:

cols.Remove("_DateOfLoading")

cols.AddCalculatedColumn("_DateOfLoading", "DateTimeNow()");

MessageBox.Show("Done!")

In the previous script, I have inserted the followingstatements at line 11 and line 24

#for column properties

from Spotfire.Dxp.Data import DataColumnProperties

and

selection = Document.Data.Properties.GetProperty(DataColumnPorperties).ExternalName

NOTE: When I run the script with these changes, I getthe following error:

Traceback (most recent call last):

File "", line 24, in

NameError: name 'DataColumnPorperties' is not defined

IronPython.Runtime.UnboundNameException: name 'DataColumnPorperties' is not defined

at IronPython.Runtime.Operations.PythonOps.GetVariable(CodeContext context, String name, Boolean isGlobal, Boolean lightThrow)

at IronPython.Compiler.LookupGlobalInstruction.Run(InterpretedFrame frame)

at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)

at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)

at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)

at Microsoft.Scripting.Hosting.ScriptSource.Execute(ScriptScope scope)

at Spotfire.Dxp.Application.IronPython27.IronPythonScriptEngine.ExecuteForDebugging(String scriptCode, Dictionary`2 scope, Stream outputStream)

 

First question: Is it possible to import the SAS dataset with the ExternalNames using this IronPython script

Second question: If yes, what do I need to change inthe script to make this work

Thanks in advance

Rodrigo Juarez

Consultant Statistician - Computation

Eli Lilly Canada Inc.

[Rodrigo Juarez]

Hello Again.

 

Any takers on this Python Script question

 

Rodrigo

[Alex Weir 2]

Hi Rodrigo,

Did you get a solution for this?

[Rodrigo Juarez]

Hello Alex,

No. I still have not received a proposed solution.

My objective is to be able to automatically update/refresh SAS datasets in a Spotfire file by running the Python script. But my Spotfire file is using the SAS original variable names while the Python scrips is expecting the SAS variable labels.

We have since changed to Spotfire v12.0.2.41.

Look forward to any help you can provide to further automate my Spotfire dashboard.

Rodrigo

[Alex Weir 2]

Hi Rodrigo,

Unfortunately it sounds like I am facing the same issue here

Sorry I am not bringing you a solution.

Alex