PowerShell Scripts, Tips, Expert Advices, Forums, and Resources

Geek Sync Live

Welcome to PowerShell.com, the educational and community site for Windows PowerShell People. Get a quick overview.


As a Powershell.com member you will have access to:

  • Daily PowerShell tips written by Microsoft MVPs and other leading Windows PowerShell experts
  • Free Windows PowerShell advice and training provided by Microsoft MVPs and other leading Windows PowerShell experts
  • Access to leading Windows PowerShell blogs
  • A free ebook, Mastering PowerShell, written by Microsoft MVP Dr. Tobias Weltner
PowerTip of the Day

Converting CSV to Excel File

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:temp\Report"

# 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:temp\Report"

# 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!

Copyright 2012 PowerShell.com. All rights reserved.