Get-Printer

PowerShell Help
1

I have an excel file with a list of over 250 print servers. The excel file lists the agency within my company for each print server.

I am trying to export a list of printers found on each print server to a CSV file. I would like to include the agency with each printer.

See code below. Is there a way for me to append $agency to $getprinter so that the Export-Csv will include the agency for each printer? Thanks!

$excel = New-Object -ComObject Excel.Application

Set to True for development; change to $false for production

$excel.Visible = $false

Open Excel file. NOTE: $filepath and $List_Of_Printers need to be updated to absolute path when file location is changed.

$filepath = ‘D:\Users\zapfd\Scripts\PrintServers_5_29_20_test.xlsx’
$List_Of_Printers = “D:\Users\zapfd\Scripts\Printers.csv”
$workbook = $excel.Workbooks.Open($filepath)

Open First Worksheet

$worksheet = $workbook.Worksheets.Item(‘Sheet1’)

if (Test-Path $List_Of_Printers)
{
Remove-Item $List_Of_Printers
}

Read Server Names, output to file

$i = 2
DO {
Write-Host ($worksheet.Cells.Item($i, 1).Text.trim(),$worksheet.Cells.Item($i, 5).Text.trim()) -Separator " "
$printserver = $worksheet.Cells.Item($i, 1).Text.trim()
$agency = $worksheet.Cells.Item($i, 5).Text.trim()

$getprinter = Get-Printer -ComputerName $printserver |
Select-Object ComputerName,Name |
Sort-Object -Property Name

$getprinter | Export-Csv -Path $List_Of_Printers -Append -Delimiter ‘,’ -NoTypeInformation
$i++
} UNTIL ($worksheet.Cells.Item($i, 1).Value() -eq $Null)

Close Excel File

$workbook.Close()
$excel.Quit()

 

2

David, welcome to Powershell.org. Please take a moment and read the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!.

When you post code, error messages, sample data or console output format it as code, please.
In the “Text” view you can use the code tags “PRE”, in the “Visual” view you can use the format template “Preformatted”. You can go back edit your post and fix the formatting - you don’t have to create a new one.
Thanks in advance.

I don’t like to deal with Excel sheets. So I won’t comment on your code. Sorry. But I’d like to show you an example way how you could match your printers and agencies.

$LookUpTable = @'
"Printer","Agency"
"Printer1","Agency1"
"Printer2", "Agency2"
"Printer3", "Agency3"
"Printer4", "Agency4"
"Printer5", "Agency5"
"Printer6", "Agency6"
'@ |
    ConvertFrom-Csv

$printerList = @(
    'Printer5'
    'Printer3'
    'Printer1'
    'Printer6'
    'Printer28'
    'Printer4'
)

foreach($printer in $PrinterList){

    [PSCustomObject]@{
        Printer = $printer
        Agency = ($LookUpTable | Where-Object {$_.Printer -eq $printer}).Agency
    }
}

It might have been helpful if you shared some sample data from both of the lists you’re dealing with. :wink: