How to read a python script's output into IronPython

[Hung Huynh]

Hi, I have a python script that creates an excel CSV file on the desktop. I would like to import the CSV file into Spotfire as a datatable.

from System.Diagnostics import Process

from Spotfire.Dxp.Data import *

from System import String,Object

from System.IO import Directory, Path, File, StreamWriter

import System

 

p = Process()

p.StartInfo.UseShellExecute = False

p.StartInfo.RedirectStandardOutput = True

 

#Include python executable program path location

p.StartInfo.FileName = "C:Anaconda3python.exe"

 

#Include python script file name

p.StartInfo.Arguments = "BubbleSort.py"

p.Start()

 

 

#Create a data table on Spotfire using python script

from Spotfire.Dxp.Data import DataTableSaveSettings

import clr

 

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

 

from Microsoft.Office.Interop import Excel

 

# Assuming data tables in this analysis have the same name as the workbook spreadsheets. A datatable on Spotfire should be created named "sheet1" that is under the data filter on the left side of the screen.

dataTablesToUpdateFromExcel = ["sheet1"]

 

# Microsoft Excel application

excelApp = Excel.ApplicationClass()

excelApp.Visible = False

workbook = excelApp.Workbooks.Open(filename) # I need the filename of the CSV file created from the python script.

 

# Perform the data import

for entry in dataTablesToUpdateFromExcel:

 

# Rotate the spreadsheets to the beginning of the file

workbook.worksheets(entry).move(workbook.worksheets(1))

workbook.save()

 

# Find out if a data table already exist in the DXP file

status, table = Document.Data.Tables.TryGetValue(entry)

 

# Create data source

ds = Document.Data.CreateFileDataSource(filename)

ds.IsPromptingAllowed=False

 

# Replace data table with new data if found

if status:

table.ReplaceData(ds)

 

# Add a new data table, if corresponding table is not found

elif not status:

# Add table

myNewTable = Document.Data.Tables.Add(entry,ds)

 

# Keep the data embedded or linked

# Embedded

# settings = DataTableSaveSettings (myNewTable,False, False);

# Linked

settings = DataTableSaveSettings (myNewTable,True, False);

Document.Data.SaveSettings.DataTableSettings.Add(settings);

[Shandilya Peddi]

To add a csv file as a data table, you can use the below script as reference

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Data.Import import *

 

filePath=r"C:sample.csv"

#specify any settings for the file

settings= TextDataReaderSettings()

dataSource=TextFileDataSource(filePath,settings)

 

dataManager=Document.Data

#datasource name and datasource to add table

dataManager.Tables.Add("myNewDataTable",dataSource)https://docs.tibco.com/pub/doc_remote/sfire_dev/area/doc/api/TIB_sfire-a...

https://docs.tibco.com/pub/doc_remote/sfire_dev/area/doc/api/TIB_sfire-a...

https://docs.tibco.com/pub/doc_remote/sfire_dev/area/doc/api/TIB_sfire-a...

[Hung Huynh]

Thank you for your help!

 

 

[Hung Huynh]

The excel csv file that was uploaded to the spotfire datatable was not comma separated therefore my data columns are all bunch together. Any solution to separate the columns by commas

[Shandilya Peddi]

You can specify the separator in the TextDataReaderSettings. For example,

 

settings= TextDataReaderSettings()settings.Separator =","

 

[Hung Huynh]

Hello, I wantmy the first row of my data to be the column names for my datatable on spotfire. So I usedthis line of code toadd the provided row (first row)to the list of column name rows.

settings.AddColumnNameRows = 1However I get this error. I am not familiar with this class format.

attribute 'AddColumnNameRows' of 'TextDataReaderSettings' object is read-onlyEdit: I figured it out!

settings.AddColumnNameRow(0)