Using the Excel.Application Com-object converting a csv file to an xlsx file is quite simple, it can be written up in a single one-liner:
((New-Object -ComObject "Excel.Application").workbooks.open('c:\list.csv')).SaveAs('c:\list.xlsx',51) |
Now this does leave an Excel process open so if you would like the process to be closed afterwards we could amend the code as follows:
$CurrentExcel = Get-Process Excel | Select-Object -ExpandProperty ID ((New-Object -ComObject "Excel.Application").workbooks.open('c:\list.csv')).SaveAs('c:\list.xlsx',51) Start-Sleep 1 Get-Process Excel | Where-Object {$CurrentExcel -notcontains $_.ID} | Stop-Process -Force |
By first gathering the list of all open Excel processes we can make sure that any previously opened Excel processes are not accidentally closed. That leaves us with a quick and easy piece of code to convert comma separated files to xlsx files on the fly.
For more information about the Excel.Application object have a look at its MSDN entry:
Application Object Excel
What does the ’51’ parameter signify?
So the SaveAs method of the Application.Excel object has a number of parameters. The second parameter determines the file format. It is a XlFileFormat enumeration that is defined here:
https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
If for example you would like to convert the csv to xls instead of xlsx, you would use the number 18.
Hope that helps.
Regards,
Jaap Brasser