PowerShell can easily create CSV files using Export-Csv, and if Microsoft Excel is installed on your system, PowerShell can then have Excel convert the CSV file to a native XLSX Excel file.
Here is some sample code. It uses Get-Process to get some data, then writes the data to a CSV file. Export-Csv uses -UseCulture to make sure the CSV file uses the delimiter your local Excel installation expects.
$FileName = "$env:tempReport" # create some CSV data Get-Process | Export-Csv -UseCulture -Path "$FileName.csv" -NoTypeInformation -Encoding UTF8 # load into Excel $excel = New-Object -ComObject Excel.Application $excel.Visible = $true $excel.Workbooks.Open("$FileName.csv").SaveAs("$FileName.xlsx",51) $excel.Quit() explorer.exe "/Select,$FileName.xlsx"
Next, Excel opens the CSV file, then saves the data as XLSX file.
This works beautifully, except you may be running into an exception like this:
PS> $excel.Workbooks.Open("$FileName.csv") Exception calling "Open" with "1" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" At line:1 char:1 + $excel.Workbooks.Open("$FileName.csv") + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation
This is a long known issue. It can occur if the language versions of Excel and your Windows operating system differ. You may not even notice because your Windows operating system may use a localized MUI package.
To work around this issue, you can temporarily change the thread culture to the culture needed by your Excel installation:
$FileName = "$env:tempReport" # create some CSV data Get-Process | Export-Csv -Path "$FileName.csv" -NoTypeInformation -Encoding UTF8 # load into Excel $excel = New-Object -ComObject Excel.Application $excel.Visible = $true # change thread culture [System.Threading.Thread]::CurrentThread.CurrentCulture = 'en-US' $excel.Workbooks.Open("$FileName.csv").SaveAs("$FileName.xlsx",51) $excel.Quit() explorer.exe "/Select,$FileName.xlsx"
This has other consequences as well: when you run Excel’s Open() method in the en-US culture, it no longer requires the CSV file to use your local delimiter. Instead, it now expects a native comma-separated file, which is why -UseCulture was taken out of the second script.
