New TIBCO Community Launches Soon!

The community will temporarily be 'read-only' beginning July 8th until the launch.

28views

watch

Is there a way to make Spotfire automatically "guess" the DataTypes of columns when importing data using IronPython?

TIBCO Spotfire®

#IronPython

#Datatype Mapping Error

#data type

#data import

#import

#import data

Hello,

When importing data from a text file using IronPython using code such as this:

readerSettings = TextDataReaderSettings()
readerSettings.Separator = "\t" # Our text file is tab-delimited 
readerSettings.AddColumnNameRow(0) 
readerSettings.AddIgnoreRow(1) # Ignore the bad data type row completely 

# create a data source to read in the file
textDataSource = TextFileDataSource(dataPath, readerSettings)

newTable = Document.Data.Tables.Add("My Table", textDataSource) 

tableSettings = DataTableSaveSettings(newTable, True, False) # UseLinkedData = True; ??? = False
tableSettings.UseLinkedData = True # To be safe  
Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

is there a way to make Spotfire automatically "guess" the data types of columns (like when you open the "Import settings" window for a data source, then click "Refresh," and all the types get set automatically)? Currently, every single column gets imported as a String, regardless of whether it is solely numeric or not.

I know that specifying a TypeRow is the preferred method for setting DataTypes automatically, but the data I am working with unfortunately has unhelpful type rows (which, for example, list some - but not all - numeric columns as being of type "STRING"), and they are out of my control.

Thank you very much,

Charles

Jun 22, 2022 - 8:22am

a0491758

Flag for Review

+ Add a Comment

(1) Answer

I cannot find a way to do it on import, however it is possible to change the data types on a table after it's been created.

See script below. This snippet checks for floating point. It is pretty basic, I hope you can adapt it to your needs and maybe not check all rows but a subset.

---------------------------------------

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelection

from Spotfire.Dxp.Data import *

table = Document.Data.Tables['MyNewTableWithWrongDataTypes']

rowsToInclude = IndexSet(table.RowCount,True)

t = ExpressionTransformation()

#get the list of columns

columns = table.Columns

#iterate on columns, collect values, try to turn them into floating point. If they are not actually floating point, do nothing, otherwise change the column type.

for cc in columns:

   ccname=cc.Name

   cursor = DataValueCursor.CreateFormatted(table.Columns[ccname])

   values=[]

   for row in table.GetRows(rowsToInclude,cursor):

       values.append(cursor.CurrentValue)

   try:

       values = [float(x) for x in values ]

       t.ColumnReplacements.Add(

           ccname,'real(['+ccname+'])',ColumnSelection(ccname)

)

   except:

       print ('was not real')

       pass