Unable to pull data from xlsx into powershell

Question

Please bear with me as I am a complete beginner. I am trying to create a phone list where in powershell it pulls data from xlsx. I have an example below, i have also tried Importxlsx but it keeps spitting an error.

The xlsx only has 2 columns Phone List and Department that i want to pull from. Basically Pull "x department" and spit "x phone number"

just need a simple effective command for this.

Function Get-PhoneNumber{
  PARAM($Department)
  $Array = ImportExcel 'C:OutputsInternal Phone List 01.01.213 test'
  $Array.Where({_.Department -eq $Department}) | Select-Object -Expandproperty 
  PhoneNumber
}

get-phonenumber -department "ICU"

question from:https://stackoverflow.com/questions/65874744/unable-to-pull-data-from-xlsx-into-powershell

Answer 1

You can use ImportExcel module.

Since i do not have your XLSX file I need to create one to visualise the example:

# using temp directory to create file
    Set-Location $env:TEMP
    $excelFiles = "ImportExcelHowTo.xlsx"
# remove any pre-existing file
    Remove-Item $excelFiles -ErrorAction SilentlyContinue
    
    # create a file with some data
    $data = ConvertFrom-Csv -InputObject @"
    Phone,Dept
    111-111-1111,James Mary
    222-222-2222,Patricia John
    333-333-3333,Jennifer Robert
    444-444-5555,Michael Linda
    555-555-5555,Elizabeth William
    "@

# export data to XLSX file
    $data | Export-Excel $excelFiles

# open the file if you need
# Invoke-Item $excelFiles

Now I can read the Excel file using Import-Excel function

$data2 =   Import-Excel -Path $excelFiles -WorksheetName Sheet1

It will look like that:

Phone        Dept             
-----        ----             
111-111-1111 James Mary       
222-222-2222 Patricia John    
333-333-3333 Jennifer Robert  
444-444-5555 Michael Linda    
555-555-5555 Elizabeth William

More examples on my blog or ImportExcel GitHub repo.