Replace Data Tables from a file (client only)

# Replace tables from file
from System.Windows.Forms import OpenFileDialog
from Spotfire.Dxp.Data import *
import clr
clr.AddReference("System.Windows.Forms")


#get filename from file chooser
d1 = OpenFileDialog()
d1.InitialDirectory='C:\myfiles\salesdata'
d1.ShowDialog()


#setup data source from selected file
myDataManager = Document.Data
ds=myDataManager.CreateFileDataSource(d1.FileName)


#replace myTable 
myTable = Document.Data.Tables["T1"] #or use a DataTable script parameter
myTable.ReplaceData(ds)

7 comments:

adhish manu said...

it Not working in web player?
could anyone of you help me on this?

Jose Leviaguirre said...

Adhish, this approach does not work on web player. Only the client. In order to have this work on the browser you need a different approach, which is not as straight forward as in this example

glenn said...

Hi,

I just have few quesions:
1) d1.FileName is the excel file where you will get the data?
2) T1, is it the table in spotfire where you will change the data with the excel data?
3) T2, what is T2? do i need this if i'm just going to update one table?

Thanks in advance

Jose Leviaguirre said...

Glenn, d1.FileName holds the file you selected from the file chooser dialog box. T1 and T2 are data tables names from the analysis. If you need only to update one data table, the is no need to use T2

Jose Leviaguirre said...
This comment has been removed by the author.
Hongzhou Zhang said...

Hi Jose, how and where do you hook up this script? I need to update source file location and tried with data function described at https://www.cambridgesoft.com/support/EnterpriseSupport/KnowledgeBase/FAQ/details/Default.aspx?TechNote=3110
however the function is executed after spotfire display "missing file" dialog, which is too late.
Thanks a lot,
Hongzhou

Jose Leviaguirre said...

Hello Hongzhou,

This is an iron python script that lives in Edit > Document Properties > Scripts. Scripts can be triggered when a document property changes its value or by using Dynamic Items, Property Controls or Action Items found on Text Areas.

The article you refer to runs a Data Function when the report is loaded. This Data Function changes a Document Property value which then triggers an Iron Python script.

The Iron Python script does not read the c:\myfiles\salesdata from the example. It is only setting the default directory for you to choose a file from there.

You need to comment the entire file chooser section and edit the third one to specify where your data file is located:

myDataManager = Document.Data
ds=myDataManager.CreateFileDataSource("c:\\your\\data\\file.csv")