Data type change

[Francisco Aspillaga]

Hi,

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.

Thanks¡

[raju spotfire]

Hello Francisco,

you can create new calculated column and use expression as real([TRUCKS TOTAL])

thanks & Regards

raju

[Fredrik Rosell]

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:

https://community.spotfire.com/s/article/overview-how-access-microsoft-sharepoint-data-tibco-spotfire

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)

https://docs.tibco.com/pub/sfire-analyst/latest/doc/html/en-US/TIB_sfire-analyst_UsersGuide/data/data_the_expanded_data_in_analysis_flyout.htm?rhhlterm=change%20data%20types&rhsyns=%20

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

https://docs.tibco.com/pub/sfire-analyst/latest/doc/html/en-US/TIB_sfire-analyst_UsersGuide/data/data_details_on_change_data_type.htm?rhhlterm=change%20data%20types&rhsyns=%20

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

https://docs.tibco.com/pub/sfire-analyst/latest/doc/html/en-US/TIB_sfire-analyst_UsersGuide/data/data_transforming_data.htm

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

[Gaia Paolini]

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)