Forum Discussion
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()
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-module-2/
$csv = import-csv c:\temp\test.csv
$csv | export-excel c:\temp\test.xlsx- Rahul UdayabhanuCopper 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.xlsxo/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"