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 in 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.