The columns get duplicated, when useing ironpython script

[Leo Chan]

when I loads multiple files use ironpython in one go which have the same structure but when a column has the same name and different Data types, the columns get duplicated. how to fix this on import?

[Fredrik Rosell]

Hello,

To start with, could you please share your IronPython script here, and also example files that can be used to reproduce the issue?

[Leo Chan]

Here is script we used:

import clr

import os

clr.AddReference("Microsoft.Office.Interop.Excel")

import Microsoft.Office.Interop.Excel as Excel

from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

from Spotfire.Dxp.Data import AddRowsSettings, DataTableSaveSettings, DataType

from Spotfire.Dxp.Framework.ApplicationModel import ApplicationThread

from Spotfire.Dxp.Application import DocumentSaveSettings

from System.IO import File

clr.AddReference("System.Windows.Forms")

import System.Windows.Forms.DialogResult as DialogResult

from System.Windows.Forms import FolderBrowserDialog, DialogResult

from System.Windows.Forms import OpenFileDialog, SaveFileDialog

from System.Windows.Forms import OpenFileDialog

openFileDialog = OpenFileDialog()

openFileDialog.Title = "選擇檔案"

openFileDialog.Filter = "Excel 檔案 (*.xlsx)|*.xlsx"

openFileDialog.Multiselect = True

dialogResult = openFileDialog.ShowDialog()

if dialogResult == DialogResult.OK:

  file_paths = openFileDialog.FileNames

  print("選擇的檔案路徑：")

  for file_path in file_paths:

    print(file_path)

saveFileDialog = SaveFileDialog()

saveFileDialog.Title = "儲存檔案"

saveFileDialog.Filter = "Spotfire檔案 (*dxp*)|*dxp*"

dialogResult2 = saveFileDialog.ShowDialog()

if dialogResult2 == DialogResult.OK:

  file_path = saveFileDialog.FileName

  print("儲存的檔案路徑：", file_path)

# Define the file paths

folder_path = openFileDialog.FileName

spotname = saveFileDialog.FileName

##print(folder_path)

##print(spotname)

xlsx_files = file_paths

# Loop through all the .xlsx files in the folder path

for xlsxname in xlsx_files:

  # Open the Excel file

  excelApp = Excel.ApplicationClass()

  workbook = excelApp.Workbooks.Open(xlsxname)

  ii = 1

  # Loop through the worksheets in the Excel file

  for worksheet in workbook.Worksheets:

    # Rotate the spreadsheets to the beginning of the file

    worksheet.Move(workbook.Worksheets(1))

    workbook.Save()

    # Check if the data table already exists in the Spotfire document

    status, table = Document.Data.Tables.TryGetValue(worksheet.Name)

    # Create a file data source for the worksheet

    ds = Document.Data.CreateFileDataSource(xlsxname)

    ds.IsPromptingAllowed = True

    # If the data table exists, replace it with new data for the first time, else add rows for later iterations

    if status:

      if ii == 0:

        table.ReplaceData(ds)

      else:

        table.AddRows(ds, AddRowsSettings(table, ds))

    # Add a new data table if it doesn't exist

    else:

      myNewTable = Document.Data.Tables.Add(worksheet.Name, ds)

      settings = DataTableSaveSettings(myNewTable, False, False)

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

        

  # Close the Excel application

  excelApp.Workbooks.Close()

  excelApp.Quit()

def g(app,fileName,saveSettings):

  def f():    

 app.SaveAs(fileName, saveSettings)

  return f

# Set the file name

fileName = (spotname)

# Executing the function on the application thread, and Save the document back to the Library

Application.GetService[ApplicationThread]().InvokeAsynchronously(g(Application, fileName,DocumentSaveSettings()))

print("All Done")