Francisco Aspillaga Posted November 21, 2022 Share Posted November 21, 2022 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¡ Link to comment Share on other sites More sharing options...
raju spotfire Posted November 21, 2022 Share Posted November 21, 2022 Hello Francisco,you can create new calculated column and use expression as real([TRUCKS TOTAL])thanks & Regardsraju Link to comment Share on other sites More sharing options...
Fredrik Rosell Posted November 22, 2022 Share Posted November 22, 2022 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-spotfireJust 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=%20Details 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=%20Transforming 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.htmIt 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 More sharing options...
Gaia Paolini Posted November 22, 2022 Share Posted November 22, 2022 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now