Hello, We had a legacy VB.exe that was being called by an sqr from the winnt proc scheduler. The app was custom built to convert a report in csv format generated in PeopleSoft to an xls file. After a recent upgrade to 2019 OS and peopletools 8.59 we found that the old exe was unreliable.
I found a powershell module called ImportExcel that can do this without Excel being installed on the box. I then spent a lot of time digging online to try and figure out how to call the powershell and actually get it to create the files and dump them to our repository. I've never coded sqr, poeplesoft, or peoplecode so maybe this is obvious to others. I couldn't find anything clearcut that solved my problem. Anyway, thank you to “Deepak's PeopleSoft blog” for finally giving me a good example of how to call the windows command line and wait for it to finish.
This is just an fyi post to help anyone else out there like me. The core of the code to convert to excel is below. We have an initial sqr that the process scheduler calls, that creates the csv with no file extension and then passes the full path to this sqc as a variable.
The powershell in this code will convert csv to xlsx, make the top row filtered, add table formatting, autosize columns, and make the whole document text to keep leading zeroes.
let $comspec = getenv('COMSPEC')
let $Command = $comspec || ' /c Powershell.exe -Command "import-module ImportExcel; import-csv -path ' || $DescrFile || ' |Export-Excel -AutoSize -NoNumberConversion * -TableStyle Medium13 -path ' || $DescrFile || '.xlsx'
show 'Calling SQR to Excel Conversion Program: ' $Command
call system using $Command #callresult wait