Jump to content

Data type change

Francisco Aspillaga

Recommended Posts


I have a connection to a sharepoint excel file, and every time a new column is added to this file, some of the data that was already connected changes the reading type and is unconfigured. Example: the column "TRUCKS TOTAL" was typed "Real" and now it is typed "Date".

Is it possible to change the data type without having to connect the file to the base data again and having to check each column? Also, when I do this I lose all connection types that add more columns from other files.


Link to comment
Share on other sites

Hello Francisco,

Adding to what Raju suggested, first I'm curious about how you are connecting to that Excel file in SharePoint - are you e.g. using the custom SharePoint connector available in TIBCO Exchange?  

FYI: Here's an article that describes the various options for accessing SharePoint data:


Just for testing if the way you are connected to the Excel file is a factor, do you experience the same issue if you save that Excel file to your local disk and use it from there?

For changing data types after import, there are various available options, directly selecting the type from a drop down in the UI, or using a transformation. See the following pages in the manual:

The Expanded Data in Analysis Flyout (see #4 in the page for how to select the type from a drop down)


Details on Change Data Types (using a transformation to change it)


Transforming Data (for general awareness of how to best use transformations)


It may be easier to investigate this issue as a support case, so our Support team can get more details about your specific Excel file, and exactly how it is updated with new data. If you want to do that, please open a support case in the TIBCO Support Portal - https://support.tibco.com

Link to comment
Share on other sites

you could use a script that effectively does the same as when you manually apply an Expression Transformation on the canvas, so you don't need an extra calculated column.

In this example I am turning all the String that actually contain Real values to Real, but you can modify it to change specific columns according to your use case:

from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelectionfrom Spotfire.Dxp.Data import * table = Document.Data.Tables['My Table']rowsToInclude = IndexSet(table.RowCount,True)t = ExpressionTransformation() #get the list of columnscolumns = table.Columnsfor 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 ('original column did not contain real data') pass  table.AddTransformation(t)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...