.gif)
from System.Windows.Forms import OpenFileDialog
from Spotfire.Dxp.Data import *
import clr
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()
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)
myTable.ReplaceData(ds)
11 comments:
it Not working in web player?
could anyone of you help me on this?
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
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
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
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
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")
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.
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
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.
That sounds like an awesome solution!
Post a Comment