Replace file data source using IronPython script

[cscs]

Dear Community,

I came across this guide and i am successfully using it for the general case, i.e. to replace a local file CSV data source with another one:
How to replace the File DataSource of a Data Table in Spotfire® Using IronPython Scripting

However, i see some limitation which i was not able to overcome yet.

When originally loading the data, in some data table some column types were manually changed. This happened let's say because based on the sampled rows Spotfire defaulted some Reals to Integers, or some well-formatted DateTimes to Strings.

When the script changes the source, these settings are neglected, i.e. the selected columns return to their wrong defaults, instead of the manually set correct ones.

I would expect i could find this data type modification step somewhere, and i would be able to add it as a transformation step at DataFlowBuilder.AddTransformation(...). I was trying to find the data type modification step at sourceView.OperationsSupportingTransformations and at different types of sourceView.GetAllOperations[...](), but it just did not appear anywhere.

Am i trying to find this at the wrong place? Is this transformation object reachable anyhow? Is there even a way to achieve my goal programmatically?

Thank you for your suggestions!
 

[David Boot-Olazabal]

Hi cscs,

You may want to have a look at similar questions, it may solve your issue.

Kind regards,

David

[cscs]

Hi David,

thank you for your reply and for the suggestions. I was searching for replacing data tables, probably that is why these were not displayed (among the ~6000, mostly irrelevant topics :) )

I think i will be able to apply the above for my use-case

However, if i need to manually add a column types changing transformation each time i replace the source, it implies further questions:

• Why are the originally set column types disregarded when only the source is changed, but not any more of the transformations?
• And, more importantly, why is the column type changing transformation not exposed at all for my existing table, so i could at least reuse it when changing the source?
  • Would it somehow be possible to programmatically expose this? I'm asking because otherwise some column names need to be hardcoded into the script, which is not ideal.

Thank you again,
Csaba

 

[cscs]

Solution was to:

1. Read the current data types

   1. # Get the current column types
      colTypes = {}
      for col in table.Columns:
      	if col.Properties.ColumnType == DataColumnType.Imported:
      		colTypes[col.Name] == str(col.DataType)

2. Replace the data table's source file (this resets the data types to string)
   1. As discussed in the original article
3. Apply the formerly read data types,
   1. As described by Gaia in the other article linked above, just doing the loop on the dictionary created in step1 above

Thank you again