Read Excel File with Powershell

Question

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"

    }

}

Raf · Accepted Answer · 2014-06-02 11:06:41Z

3

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

edited Jun 2, 2014 at 11:06

answered Jun 2, 2014 at 9:19

Raf

10.2k1 gold badge34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read Excel File with Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related