Forum Discussion

Rahul Udayabhanu's avatar
Rahul Udayabhanu
Copper Contributor
May 14, 2018

Convert CSV to XLSX via PowerShell not working with Task scheduler

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()                                                                       

    • Rahul Udayabhanu's avatar
      Rahul Udayabhanu
      Copper Contributor

      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"
       

Resources