When I imported the string “2023/01/01 0:00:00” using the ReplaceData method, it became a Date type. Is it possible to import it as a DateTime type?

[Morio Saeki 3]

I imported "2023/01/01 0:00:00" using ReplaceData() in my ironPython script. DataType= Date

I imported "2023/01/01 0:00:00" using ReplaceData() in my ironPython script. Datatype = DateTime

Spotfire Analyst version: 10.10.3 LTS

​

My ironPythn script imports multiple csv files into one data table.

1st csv file : import using ReplaceData() to DataTable1.

2nd csv file: import using AddRows() to DataTable1.

Later files: same as 2nd csv files.

​

My expectation is "2023/01/01 0:00:00" import with DateTime.

Currently, since "2023/1/1 0:00:00" is imported as a Date type, the DateTime type data that was subsequently imported using AddRows() is not displayed on the screen.

​

Is there any workaround?

[Gaia Paolini]

could you show your script code and maybe some sample dataset/use case?

[Morio Saeki 3]

Please show attached files.

UseCase​

1. Start fruitsLoad.dxp

2. ​Push button "DynamicLoad3”

2.1. select below files​ in file select dialog

fruits1.csv

fruits2.csv

fruits3.csv

2.2. file select end

3.Start ironPython script "DynamicLoad3"

​

My Expectation : "2000/1/1 0:00:00" in fruits1.csv import with data type=DateTime.

And DateTime data in fruits1/2/3.csv display on Table View.

Result : ​"2000/1/1 0:00:00" import with type=Date.

And DateTime Data in Fruits2/3.csv not display Table View, because Column type is not DateType.

​

I changed DateTime Value "2000/1/1 0:00:01" in fruits1.csv. It works as I expected.

​

I referred to the following article for import data using ironPython script.

How to use IronPython to import data? (tibco.com)

​

[Gaia Paolini]

The simplest way, without changing your script too much, would be to add a datatype transformation at the end.

Your first file is imported using the most likely data type settings, since your date has no time, it is set to a Date (interestingly, when I run it, it sets it as a String). So, since you are copying all the settings from your first file, everything else is set as a Date.

Try this: I added an import at the top and a transformation at the end:

1. # 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 *
   from Spotfire.Dxp.Data.Transformations import ExpressionTransformation,ColumnSelection
    
   clr.AddReference("System.Windows.Forms")
   from System.Windows.Forms import OpenFileDialog
    
   print "Start"
    
   myDataManager = Document.Data
   #myDataManager = Document.Data['BOM_Detail']
   #myDataManager = table
   d1 = OpenFileDialog()
   d1.Multiselect=True #lets you select more than one file
   d1.InitialDirectory='C\\Data' #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)
    
   print "Complete"
    
    
   transformation = ExpressionTransformation()
   column_name='Update DateTime'
   transformation.ColumnReplacements.Add(
   column_name, 'DateTime(['+column_name+'])', ColumnSelection(column_name)
   )
   table.AddTransformation(transformation)

    

[Morio Saeki 3]

I tried your script. However, the situation did not improve.

Is there any other way?

[Result with your script]

[My expectations]

I expect that even if the "Update Datetime" value of the first imported file is "2000/01/01 0:00:00"(time part=0:00:00), it will be imported as a DateTime.

[Gaia Paolini]

It worked for me importing all the files in one go. How do you intend to use it? By importing a few files at a time?

[Morio Saeki 3]

I ​imported 3 files in one go.

Update DateTime changed from Date to DateTime​.

But result is not good.

Is there a possibility that the difference in versions has an effect?

My environment use Spotfire@Analist 10.10.3 LTS. ​

​I will try below.

ReplaceData()　//fruits1.csv

​ExpressionTransformation()　// Date to DateTime

AddRows()​ //fruits2.csv,fruits3.csv

[Morio Saeki 3]

[Results of trying]​

I imported DateTime data as my expectation.Script summay is below.

ReplaceData()　//fruits1.csv

AddTransformation()　// Date to DateTime

AddRows()​ //fruits2.csv,fruits3.csv

​​

Thank you for your information.