I'm trying to convert a csv file to excel using powershell. I got some help from another post and the script runs, but the output is a blank xlsx file.
Can someone help please?
Here is the powershell script
param (
[Parameter(Mandatory=$true)][string]$inputfile,
[Parameter(Mandatory=$true)][string]$outputfile
)
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)
$ws.Cells.NumberFormat = "@"
write-output "Opening $inputfile"
$i = 1
Import-Csv $inputfile | Foreach-Object {
$j = 1
foreach ($prop in $_.PSObject.Properties)
{
if ($i -eq 1) {
$ws.Cells.Item($i, $j) = $prop.Name
} else {
$ws.Cells.Item($i, $j) = $prop.Value
}
$j++
}
$i++
}
$wb.SaveAs($outputfile,51)
$wb.Close()
$excel.Quit()
write-output "Success"
Then I'm running the following command
.\csv_to_excel.ps1 -inputfile "C:\Scripts\testcsv.csv" -outputfile "C:\Scripts\data.xlsx"
Also, if anyone has experience with the Import Excel powershell module and has some kind of guide for that, I would appreciate that as well.
Thanks
$ws.Cells.Item($i, $j)->$ws.Cells.Item($i, $j).Value