How to insert a data read from ActiveMarkingSelection as a row into a new datatable?

[PBR]

Hello.

I have a line chart of Var vs. Time. I want to make a selection on the line chart, then get the min and max Time for that selection and then insert both as a row into a new Table.
I have this code in IronPython that returns the start_date and end_date correctly based on the marking on selection. However, I don't know how to insert them into the table.
I have created a New_Table data table with columns Start_Date and End_Date and no available data at the moment.

from System.Collections.Generic import List
from Spotfire.Dxp.Data import *
from System.Globalization import CultureInfo
import System

# VarData is the existing table with data
dataTable = Document.Data.Tables["VarData"]
cursor = DataValueCursor.CreateFormatted(dataTable.Columns["Time"])


markings = Document.ActiveMarkingSelectionReference.GetSelection(dataTable)
markedata = List [str]();

# Iterate through the data table rows to retrieve the marked rows
for row in dataTable.GetRows(markings.AsIndexSet(),cursor):
    value = cursor.CurrentValue
    if value <> str.Empty:
        markedata.Add(value)

# Convert string dates to DateTime objects
date_format = "yyyy-MM-dd h:mm:ss tt" 
culture_info = CultureInfo.InvariantCulture
date_objects = [System.DateTime.ParseExact(date, date_format, culture_info) for date in markedata if date]

start_date = min(date_objects)
end_date = max(date_objects)

 

[Olivier Keugue Tadaa]

 

Hi PBR,

Not tested but you should try something like this 👇
 

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource,AddRowsSettings

#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables["new_table"], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)
              

Let us know if it works

[PBR]

23 minutes ago, Olivier Keugue Tadaa said:

 

Hi PBR,

Not tested but you should try something like this 👇
 

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource,AddRowsSettings

#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables["new_table"], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)
              

Let us know if it works

Thank you for the reply. I get this error on import: "ImportError: Cannot import name AddRowsSettings"

I am using Spotfire Analyst 12.0.4

[Olivier Keugue Tadaa]

My bad.  here are the right imports

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data import AddRowsSettings


#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables["new_table"], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)

 

Edited August 6 by Olivier Keugue Tadaa

[PBR]

1 hour ago, Olivier Keugue Tadaa said:

My bad.  here are the right imports

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data import AddRowsSettings


#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables["new_table"], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)

 

I am a little confused about the last two lines. Where datablename should be defined? is that a new document property like below?

datablename = Document.Data.Tables["new_table"]
settings = AddRowsSettings(Document.Data.Tables["new_table"], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)

When I run above, I get this "TypeError: expected str, got DataTable"

[Olivier Keugue Tadaa]

You should assign your data tablename variable with a string like this

datablename = "New_table"

see below 👇

 

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data import AddRowsSettings


#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)
datablename = "new_table"

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables[datablename], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)

# dispose the stream
stream.Dispose()

 

 

Edited August 6 by Olivier Keugue Tadaa

[PBR]

3 hours ago, Olivier Keugue Tadaa said:

You should assign your data tablename variable with a string like this

datablename = "New_table"

see below 👇

 

#add these imports
from System.IO import MemoryStream
from System.Text import Encoding, StringBuilder
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data import AddRowsSettings


#........

#at the end of your script
builder = StringBuilder()
builder.Append("start_date\tend_date")
builder.Append(start_date)
builder.Append("\t")
builder.Append(end_date)

stream = MemoryStream(Encoding.UTF8.GetBytes(builder.ToString()))
dataSource = TextFileDataSource(stream)
datablename = "new_table"

#This Constructor performs an automatic match, no columns are ignored.
settings = AddRowsSettings(Document.Data.Tables[datablename], dataSource)
Document.Data.Tables[datablename].AddRows(dataSource,settings)

# dispose the stream
stream.Dispose()

 

 

Thank you Olivier. This one worked in making new rows but it is not properly aligned under my table columns. I tried to fix it with this code:
It is creating new columns as Start_Date(2), End_Date(2), and keeps copying the names for every run.
If I import the datatable and uncheck all column, running the code creates Start_Date and End_Date, but I need to manually add the columns to table visualization. Then, running the code works well and keep generating rows under the right column. Maybe the formatting between the existing columns and new columns is wrong.

builder = StringBuilder()
builder.AppendLine("Start_Date\tEnd_Date")
builder.AppendLine("{0}\t{1}".format(start_date.ToString("yyyy-MM-dd HH:mm:ss"), end_date.ToString("yyyy-MM-dd HH:mm:ss")))

 

[Olivier Keugue Tadaa]

As you have pointed out, it should be a matter of data format. Can you check the type of the added columns Start_Date(2) and End_Date(2)? they are probably strings. If so, then I'll check the datatypes and try again.

 

[PBR]

1 hour ago, Olivier Keugue Tadaa said:

As you have pointed out, it should be a matter of data format. Can you check the type of the added columns Start_Date(2) and End_Date(2)? they are probably strings. If so, then I'll check the datatypes and try again.

 

The issue was the format of columns in imported datable. Thank you again

[Olivier Keugue Tadaa]

Happy to hear that you were able to fix it.

Could you mark it as the solution?