Brad Carey (Customer) asked a question.

Edited May 25, 2023 at 6:54 PM
Problem with MOVEit Automation Script to Convert ODS to CSV

I am trying to make a script to convert ODS files to CSV.

 

Here is the basic code I am using for testing and development at this time:

 

D:\MOVEitTemp\ods2csv.ps1:

  1. $excelObj = New-Object -com excel.application
  2.  
  3. $excelWb = $excelObj.Workbooks.Open("D:\MOVEitTemp\ods_input.ods")
  4.  
  5. $excelWb.SaveAs("D:\MOVEitTemp\ods_converted.csv",6)
  6.  
  7. $excelWb.Close($false)
  8.  
  9. $excelObj.Quit()
  10.  
  11. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelWb)
  12.  
  13. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj)
  14.  
  15. [GC]::Collect()

If I run the above code via Powershell on the MOVEit Automation server, it produces the converted file ods_converted.csv

 

If I attempt to run the same code via a MOVEit Automation PS1 script:

  1. $inputFile = $miaclient.MICacheFilename()
  2.  
  3. $tempFile = "D:\MOVEitTemp\ods_input.ods"
  4.  
  5. Copy-Item -Path $inputFile -Destination $tempFile
  6.  
  7. D:\MOVEitTemp\ods2csv.ps1

I get the following error (and no file is produced):

 

Task "Script Testing": Script Testing returned error 10000: Error: Microsoft Excel cannot access the file 'D:\MOVEitTemp\ods_input.ods'. There are several possible reasons:

 

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

 

I am confident none of the several possible reasons is true.

 

Does anyone know why Excel doesn't have access to the file when the PS1 code is run via MOVEit Automation, but it does when run natively with Powershell?

 

The MOVEit Automation service account has full access to file D:\MOVEitTemp\ods_input.ods.

 

Alternatively, I am open to another idea on how to convert ODS to CSV.

 

XLSX to CSV was much easier to accomplish:

  1. #get the cache filename
  2. $inputFile = $miaclient.MICacheFilename()
  4. #get a new cache filename
  5. $outputFile = $miaclient.MINewCacheFilename()
  6.  
  7. #import module
  8. import-module -name "c:\program files\windowspowershell\modules\ImportExcel"
  10. #Perform conversion
  11. Import-Excel -Path $inputFile | Export-Csv $outputFile -NoTypeInformation
  13. #Create final file
  14. $miaclient.MIReplaceCacheFile($outputFile)

 

 

  • 2 answers
  • 260 views

  • Brad Carey (Customer)

    Frustratingly enough, step 1 was the cure to my problem:

    Capture 

    Yes, that is correct, I simply had to create the folder "Desktop" under C:\Windows\System32\config\systemprofile\.

     

    Here is the code I have thus far landed on:

    1. try
    2. {
    3. #get the cache filename
    4. $inputFile = $miaclient.MICacheFilename()
    5.  
    6. #get a new cache filename
    7. $outputFile = $miaclient.MINewCacheFilename()
    9. #make a log entry
    10. $miaclient.MILogMsg("Started converting from ODS to CSV.")
    11.  
    12. $excelObj = New-Object -comobject excel.application
    13.  
    14. $excelWb = $excelObj.Workbooks.Open($inputFile)
    16. $excelWb.SaveAs($outputFile,6)
    18. $excelWb.Close($false)
    19.  
    20. $excelObj.Quit()
    21.  
    22. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelWb)
    23. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj)
    24. [GC]::Collect()
    25.  
    26. #Create final file
    27. $miaclient.MIReplaceCacheFile($outputFile)
    28.  
    29. $miaclient.MILogMsg("Ended converting from ODS to CSV.")
    30. }
    31. catch
    32. {
    33. #make a log entry
    34. $miaclient.MISetErrorCode(10000)
    35. $miaclient.MISetErrorDescription("Error: " + $_)
    36. }

     

    Selected as Best

  • Brad Carey (Customer)

    Frustratingly enough, step 1 was the cure to my problem:

    Capture 

    Yes, that is correct, I simply had to create the folder "Desktop" under C:\Windows\System32\config\systemprofile\.

     

    Here is the code I have thus far landed on:

    1. try
    2. {
    3. #get the cache filename
    4. $inputFile = $miaclient.MICacheFilename()
    5.  
    6. #get a new cache filename
    7. $outputFile = $miaclient.MINewCacheFilename()
    9. #make a log entry
    10. $miaclient.MILogMsg("Started converting from ODS to CSV.")
    11.  
    12. $excelObj = New-Object -comobject excel.application
    13.  
    14. $excelWb = $excelObj.Workbooks.Open($inputFile)
    16. $excelWb.SaveAs($outputFile,6)
    18. $excelWb.Close($false)
    19.  
    20. $excelObj.Quit()
    21.  
    22. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelWb)
    23. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj)
    24. [GC]::Collect()
    25.  
    26. #Create final file
    27. $miaclient.MIReplaceCacheFile($outputFile)
    28.  
    29. $miaclient.MILogMsg("Ended converting from ODS to CSV.")
    30. }
    31. catch
    32. {
    33. #make a log entry
    34. $miaclient.MISetErrorCode(10000)
    35. $miaclient.MISetErrorDescription("Error: " + $_)
    36. }

     

    Selected as Best