Powershell Hacks

Search This Blog

Home
About

Showing posts with the label Excel

View all

Posts

Powershell Read An Excel File

July 11, 2018

+

0

Get link
Facebook
X
Pinterest
Email
Other Apps

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

Powershell Read A Text File

I get a lot of data in different formats for a lot of different users. Nothing is ever perfect. This is the simple script I use to begin the process of reading files: 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 #File parameter Param ( [String] $file ) #Get the current directory $scriptDir = Split-Path -Path $MyInvocation .MyCommand.Definition -Parent #Build Full Path $filePath = $scriptDir + "\" + $file #Get File Contents $content = Get-Content -Path $filePath #Get Max number of rows $intRowMax = $content .count #loop through rows for ( $intRow = 0 ; $intRow -lt $intRowMax ; $intRow ++) { #display Write-Host $content [ $intRow ] } #Alternative Write-Host "`n**********Alternative*******" foreach ( $line in Get-Content $filePath ) { #display Write-Host $line } Pretty straightforward example. I added an alternative that uses a foreac...

Labels

Excel
Powershell
Read
Text Document

About

Powered by Blogger