Customer Banners (Ads) - SpiceUp. AX and SpotfireX Disclaimer



If you find this site useful and you want to support, buy me a coffee   to keep this site alive and without ads.

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)

11 comments:

Unknown 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.
Unknown 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")

Matteo said...

Thanks Jose for the example.
Any tip for how to adapt it to import multiple files? To be more specific, the files have the same columns and I need to add import as "Add rows" so that they are stacked on top of each other in a single table.
I managed to get the dialog box to allow multiple selection (d1.Multiselect = True).

Thank you.

Jose Leviaguirre said...

you are better of with a python or R data function. They are good at handling multiple files if you don't need the prompt.

import os
import pandas as pd

# Directory path
directory_path = r'C:\Users\matteo\data\csvFilesToCombine'

# List files in the directory
file_list = os.listdir(directory_path)

# Filter only CSV files
csv_files = [file for file in file_list if file.endswith('.csv')]

# Initialize an empty DataFrame to store combined data
combined_df = pd.DataFrame()

# Read and concatenate data from each CSV file
for csv_file in csv_files:
file_path = os.path.join(directory_path, csv_file)
df = pd.read_csv(file_path)
combined_df = pd.concat([combined_df, df], ignore_index=True)

df = combined_df

Matteo said...

Thanks a lot Jose!
If it turns out that I need the dialog, I can probably combine the 2 solutions: IronPython script with dialog to store the path to the files to be imported + python script to import and combine in a single dataframe.

Jose Leviaguirre said...

That sounds like an awesome solution!