Convert CSV to XLSX via PowerShell not working with Task scheduler

[Rahul Udayabhanu]

Hi, I tried to using this csv to Excel convert in one of storage Capacity reporting script. In script the storage cmd will run and generate a CSV data and I tried to put it as source for this script. It is working successfully when I manually running it. But the xlsx file is not generating when I putting this in a Windows task scheduler and running it , but storage cmd and csv file generation is happening. Could you someone help me on where is wrong. I am confused.

Thanks in advance.

Below is the script:-

$csv = "D:\Temp\UnityScript\Logs\FS_UTIL" +".csv" #Location of the source file

$xlsx = "D:\Temp\UnityScript\Logs\FS_UTIL1" +".xlsx" #Desired location of output

$delimiter = "," #Specify the delimiter used in the file

Remove-Item $csv

Remove-Item $xlsx

#EMC Storage cmd which give CSV output

uemcli -d X.X.X.X -u XXX -p XXX  /stor/prov/fs show -output csv -filter "Name,Health details" >> $csv

# Create a new Excel workbook with one empty sheet                                                                                                   

$excel = New-Object -ComObject excel.application                                    

$workbook = $excel.Workbooks.Add(1)                                                 

$worksheet = $workbook.worksheets.Item(1)                                            

# Build the QueryTables.Add command and reformat the data                           

$TxtConnector = ("TEXT;" + $csv)                                                  

$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))       

$query = $worksheet.QueryTables.item($Connector.name)                               

$query.TextFileOtherDelimiter = $delimiter                                          

$query.TextFileParseType  = 1                                                       

$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count                

$query.AdjustColumnWidth = 1                                                        

# Execute & delete the import query                                                  

$query.Refresh()                                                                    

$query.Delete()                                                                     

# Save & close the Workbook as XLSX.                                                

$Workbook.SaveAs($xlsx,51)                                                       

$excel.Quit()                                                                       

[#2]

What if you use the excell module and do something like this?
https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-modu...

$csv = import-csv c:\temp\test.csv
$csv | export-excel c:\temp\test.xlsx

[#3]

Hi,

I tried this method for manual script run. its not generating the output in a formatted way.

$slgcsv = "D:\Temp\UnityScript\Logs\FS_UTIL" +".csv" #Location of the source file

#EMC Storage cmd which give CSV output#EMC Storage cmd which give CSV output with Delimiter ","

uemcli -d XXX -u XXX -p XXX /stor/prov/fs show -output csv -filter "Name,Health details,Server,Protocol,Size,Size used,Replication destination" >> $slgcsv
 
$csv = import-csv D:\Temp\UnityScript\Logs\FS_UTIL.csv 
$csv | export-excel D:\Temp\UnityScript\Logs\test.xlsx

o/p of storage command is like below:-

Storage system address: X.X.X.X
Storage system port: 443
HTTPS connection
"Name","Health details","Server","Protocol","Size","Size used","Replication destination"
"XXXX","""The component is operating normally. No action is required.""","nas_2","cifs","107374182400 (100.0G)","38179020800 (35.5G)","no"
"XXXX","""The component is operating normally. No action is required.""","nas_4","cifs","107374182400 (100.0G)","57654591488 (53.6G)","no"