Loading...
 

Greg`s Tech blog

Manipulating Native Excel FIles in Powershell

Wednesday 03 of October, 2018

The first time I used Import-CSV to access data i a CSV file, I knew I was leaving VBScript behind for PowerShell.  THat was probably more than 10 years ago and I have had a lot of "fun" with PowerShell since then.  Today, I had reason to manipulate native Excel files (OOXML) and found the process as simple as CSV.  This all starts with a module from the PowerShell Gallery called ImportExcel and it can be installed runnning Install-Module ImportExcel in a pwsh window.


Once installed, accessing an excel file is as simple as accessing a csv file.  Execute


 


$Data = Import-Excel C:\temp\somefile.xlsx


 


As with Import-csv, you can now access the various columns of the worksheet using the header name and the rows by the array index of the $Data object.  


 


EX: $Data2.Name will display the value of the column Name in the second row.