How to read Excel files in powershell?

Question

I would like to parse the following Excel file which perfectly open in the application.

I browsed the Internet and lots of guys said that those two command lines do open Excel files (example Read Excel sheet in Powershell).

$excel = New-Object -com excel.application
$wb = $excel.workbooks.open("c:\users\administrator\my_test.xls")

But I have got the following exception:

Exception calling "Open" with "1" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"

How to outputs the value of each cell read-only, if possible keeping the rows ?

Quite a common error when automating MS Office with non-english language/regional settings.Take a look at these for solutions: http://wouter.shush.com/2007/08/02/excel-automation-with-powershell/ https://www.vistax64.com/threads/com-cant-open-an-excel-workbook.16344/ — henrycarteruk, Jan 25 '18 at 13:10
[Related](https://support.microsoft.com/en-us/help/320369/bug-old-format-or-invalid-type-library-error-when-automating-excel). — Ansgar Wiechers, Jan 25 '18 at 13:12
You can try https://github.com/RamblingCookieMonster/PSExcel — Hackerman, Jan 25 '18 at 14:04

score 2 · Answer 1 · answered Jan 22 '20 at 12:55

As mentioned by commenters, the error you're seeing is due to your system having different regional settings to those of the document you want to edit. To get around the problem, you can change your system locale to that of the excel document in question. If, for instance, your document is of locale 'nl-BE' (assumption based on your username), you can do the following:

& {
    [threading.thread]::CurrentThread.CurrentCulture = 'nl-BE'
    $excel = New-Object -com excel.application
    $wb = $excel.workbooks.open("c:\users\administrator\my_test.xls")
  }

Hope this helps.

How to read Excel files in powershell?

1 Answers1

Linked