How to programmatically merge columns based on the columns' name using IronPython?

[Santiago Villa Cardenas]

I currently have a dashboard that loads multiple files in one go which have the same structure but when a column has the same name and different Data types, the columns get duplicated. I have not found a way to fix this on import, so is there a way to merge columns using IronPython? Image below for context

[Jose Leviaguirre]

Can you provide more details please? How are you importing data? You could use a transformation to change the data type. This can be done only once so next time you load data, it will remember what data type it is supposed to be if coming form the same source. Another way to merge columns is using a Data Function if the transformations at loading does not work. I think a data function is a better approach than IronPython, but it is certainly possible to merge with IP as a last resource.

[Santiago Villa Cardenas]

Hi Jose, thank you for your answer. I import the data using an IP script that I got help from in another Community post: How to use IronPython to import data? (tibco.com) . The script imports multiple files into one spotfire data table but on import, when columns are named the same but have different data types, the columns duplicate as in the screenshot I posted. Is there a way to specify data types in this script or to force data types so that there is consistency and also that no columns get duplicated? Here is the script.

 

[Santiago Villa Cardenas]

and the code (indentation will probably get lost):

import clr

import Spotfire.Dxp.Data.DataTable

from Spotfire.Dxp.Data import *

clr.AddReference("System.Windows.Forms")

from System.Windows.Forms import OpenFileDialog

from Spotfire.Dxp.Data import *

myDataManager = Document.Data

d1 = OpenFileDialog() #Open windows window

d1.Multiselect=True #lets you select more than one file

d1.InitialDirectory='C:\' #the folder containing your source data files

d1.ShowDialog()

files=d1.FileNames #Store the names of the files

ds=myDataManager.CreateFileDataSource(files[0]) #The file source of the first selected file

table.ReplaceData(ds) #replace placeholder RESET DATA table with first file stored which is stored in ds

for ff in files[1:]: #for every file in files, 

ds=myDataManager.CreateFileDataSource(ff)

settings = AddRowsSettings(table,ds)

table.AddRows(ds,settings)

Maybe there is a spotfire API function that can allow for datatype consistency? AddRowsSettings does not seem to contribute to that.

Thank you in advance