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: wouter.shush.com/2007/08/02/excel-automation-with-powershell vistax64.com/threads/com-cant-open-an-excel-workbook.16344 — henrycarteruk
– henrycarteruk, Commented Jan 25, 2018 at 13:10

borizzzzz · Accepted Answer · 2020-01-22 12:55:42Z

2

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.

answered Jan 22, 2020 at 12:55

borizzzzz

6301 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read Excel files in powershell?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related