How to use IronPython to import data?

[Santiago Villa Cardenas]

I am currently working on a tool to visualize data but it will be used to analyze many different files, so I need to create an import function that will allow for selecting files and loads it into Spotfire.

[Gaia Paolini]

Assuming that you have already a data table in Spotfire containing data that has the same structure as what you want to load, you could use something like this, where "table" is an input parameter of type Data Table (your target table, pre-existing).

If your files are all of different structures instead, you will need to also create the data table on the fly. https://community.spotfire.com/s/article/IronPython-script-to-load-an-In-memory-csv-file-in-TIBCO-Spotfire

# Copyright © 2022. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

# Replace tables from files

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()

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

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

d1.ShowDialog()

files=d1.FileNames

#lazy workaround: replace data with first file then add the other selected files

ds=myDataManager.CreateFileDataSource(files[0])

table.ReplaceData(ds)

for ff in files[1:]:

ds=myDataManager.CreateFileDataSource(ff)

settings = AddRowsSettings(table,ds)

table.AddRows(ds,settings)

[Gaia Paolini]

looks like indentation is lost

[Santiago Villa Cardenas]

Thank you! Any ideas on how to create a different Table for each selected file? (With this code each file is added together in one single table)

[Gaia Paolini]

can you elaborate on what the desired outcome is? In order to create a new table, you will need to know or infer the data structure of each file. Are these files all different or is there a common data structure?

[Santiago Villa Cardenas]

So currently what it is doing is adding the files together row-wise. Basically all the files that will be used have the same structure. This is what it does now if I select these two files for loading:

but what I would want is to have them separated and each one being its own table.

[Gaia Paolini]

Actually it is easier than I thought, you only need the data types if they are wrongly inferred. So this should work.

Beware of the indentation, I cannot find a way to paste without losing it.

# Copyright © 2022. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

# Add or replace tables from files

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()

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

d1.InitialDirectory='C:\MyData' 

d1.ShowDialog()

files=d1.FileNames

for ff in files:

tableName = ff.split('\')[-1] #the file name without path

ds=myDataManager.CreateFileDataSource(ff)

try:

newTable = Document.Data.Tables.Add(tableName, ds) 

except:

newTable = Document.Data.Tables[tableName]

newTable.ReplaceData(ds)

[Santiago Villa Cardenas]

Works perfectly, Thank you Gaia!!

[Sundaram Sridharan]

I love the original answer that could apply to my situation - i.e. concatenate all input files of same structure into one table - I have a couple of twists that I would like see how to address though:

I have a fixed number of header lines I need to remove from each file

I also need to add the file name as a new column (for multi-select file option)

is there a way to tweak this "createfiledatasource" method to do the above two things?

[Gaia Paolini]

Hello Sundaram. To keep the issues separated, I recommend that you create a new question for this. I will be monitoring the community for your new question. You could link to the original question for additional background.

[Ming Qian]

hello all

who can share a document to ​explain ironpython code, such as "myVis = myVis.As[Visualization]()"?

we do not know why the author write it like this, so need to know the reason!

Thanks all!

[Gaia Paolini]

please can you write this as a separate new question, as it is not related to the previous question.

[Heleen Snelting]

Hi @Ming Qian​ can I kindly ask you to post new questions as a new question with its own title rather than adding to an existing thread? That way our IronPython experts can more easily recognise the question and will respond to you.

Thanks,

Heleen

[Ming Qian]

Hi, @Heleen Snelting​ 

you can put it a new question to post, we need more help to better use ironpython in Spotfire like python.

Thank you so much!

[Santiago Villa Cardenas]

Hi Gaia,

Getting back to this question since I have run into some issues overtime. Mainly regarding data type handling on load. Most of my files have identical structure but some columns (named equally) have different data types which creates column duplication, for example Column, Column (1), where the first is one specific data type and the second one is a different data type. My objective is to include something in the script above that can help handling these data type issues. Any ideas?

Thanks a lot

@Gaia Paolini​ 

[Gaia Paolini]

which script are you using? By the error it looks like you are trying to merge the data tables into one, but my latest script keeps them separate. Please describe your current use case in detail.

[Emma Wilson]

Hello,

To import data into Spotfire using IronPython, follow these simple steps:

1. Get IronPython:Make sure you have IronPython installed on your system.
2. Open Spotfire: Launch Spotfire, and open the project where you want to import the data.

You can also check this: https://community.spotfire.com/s/article/IronPython-script-to-load-an-In-memory-csv-file-in-TIBCO-Spotfire

1. Write a Script: In Spotfire, go to the #Tools menu, choose "IronPython Script Editor," and write a script to import the data. Here's a basic example:

from Spotfire.Dxp.Application import Documentfrom Spotfire.Dxp.Data import DataTable # Create a new data tablenew_table = DataTable() # Let users select files (you can use a simple file dialog) # Read and load data into the data table# For CSV files, using pandas for simplicityimport pandas as pdfile_path = "path/to/selected/file.csv"data = pd.read_csv(file_path)new_table.ImportRows(data) # Add the data table to the Spotfire documentDocument.Data.Tables.Add(new_table)

1. Run the Script: Execute the script in the IronPython Script Editor.
2. Check Spotfire: Go back to Spotfire, and you should see the data imported into a new table.

Remember to adjust the script based on the types of files you're working with and customize it according to your needs. This basic script creates a new data table, lets users select files, loads the data, and adds it to your #Spotfire project.

I hope this will help you.

Regards

@Emma Wilson​salesforce developer

[Mach Brown]

Hello,

According to me , To use IronPython for importing data into Spotfire:

1. Import required modules.
2. Prompt users to select files using
3. FileDialog
4. .
5. Create a new data table in Spotfire.
6. Define columns in the data table.
7. Read data from selected files and populate the table.
8. Update the Spotfire visualization to display the imported data.