Powershell Read An Excel File

Reading and manipulative files can be a repetitive boring task. I always have issues working in Excel. I feel like whatever I want it to do, it does something completely different. So I use powershell to morph excel files into something I need.

I first used this script to read line by line and then call an API with the data. This has been a base for a lot of different tasks. 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

#File parameter Param( [String] $file ) #Get the current directory $scriptDir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent #Create Full Path $filePath = $scriptDir + "\" + $file #Create New Excel Object $objExcel=New-Object -ComObject Excel.Application #Open File Path $WorkBook=$objExcel.Workbooks.Open($filePath) #Get The First Workbook. Update this for other workbooks $worksheet = $WorkBook.sheets.Item(1) # loop for each row of the excel file $intRowMax = ($worksheet.UsedRange.Rows).count for($intRow = 1; $intRow -le $intRowMax ; $intRow++) { #Get first and second column for example $ColOne = $worksheet.cells.item($intRow,1).Text $ColTwo = $worksheet.cells.item($intRow,2).Text #display Write-Host ('(' + $intRow + ') 1st Column (' + $ColOne + ') 2nd Column (' + $ColTwo + ')') } #close $WorkBook.close() $objexcel.quit()

Here is how to call the script and the output:

From this test excel document:

I have the files in the same directory, but this can be updated by either calling by calling the script by the full path or adding a function