Converting CSV to Excel File

by May 5, 2015

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.

Twitter This Tip! ReTweet this Tip!