CODEWITHSUNDEEP
excelpowershellforeach
aston_zh
aston_zh

Reputation: 6843

Read Excel File with Powershell

I've got a excel serverlist where column D is hostnames and column F area (like dev, int, prd). Now I want to read this excelfile with powershell and printout each hostname that has the area dev. What do I have to change? 

$FilePath = "C:\serverlist.xlsx"
$SheetName = "serverlist"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$WorkBook = $objExcel.Workbooks.Open($FilePath)
$WorkSheet = $WorkBook.sheets.item($SheetName)


$Range = $WorkSheet.Range("F2:F150").Text

Foreach ($cell in $Range) {

    If ($cell -like "dev") {

        Write-Host "hostname from  Column D"

    }

}

Upvotes: 0

Views: 7172

Answers (1)

Raf
Raf

Reputation: 10117

Save your spreadsheet as a CSV and you will be able to process the list much faster. Then you can use following code, change values after -Header to column names relevant to you:

$FilePath = "C:\serverlist.csv"
$serverList = Import-Csv $FilePath 
    -Header "ColumnA","ColumnB","ColumnC","HostName","Area"
$serverList | where {$_.Area -like"dev" } | Select-Object HostName

Upvotes: 3

Related Questions