Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Export-CSV Help Please RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Guys,

    Im have a script to extract the needed data from Printers and save to a file in one column (Version1). What I wanted to do is format the output so that it saves in csv format as a table with headers (Version2).

    I think it is because were I have $o | export-csv $Outputfile -noType placed but could be wrong.

    Can anyone help with my problem please?

    **************************************************************

    Version 1 below: working

    **************************************************************

    $Inputfile = Read-Host "Read in?"
    $Printservers =  get-content $Inputfile
    $Outputfile = Read-Host "Save to?"

    ForEach ($Printserver in $Printservers)
    {   $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver
        ForEach ($Printer in $Printers)
        {
            if ($Printer.Name -notlike "Microsoft XPS*")
            {   $Printer.DriverName  >> $Outputfile
            If ($Printer.PortName -notlike "*\*")
                {   $Ports = Get-WmiObject Win32_TcpIpPrinterPort -Filter "name = '$($Printer.Portname)'" -ComputerName $Printserver
                    ForEach ($Port in $Ports)
                    {   $Port.Name  >> $Outputfile
                        $Port.HostAddress  >> $Outputfile
                    }
                }
                $Printer.ShareName  >> $Outputfile   
                $Printer.Location  >> $Outputfile        
                $Printer.Name  >> $Outputfile
            }
        }
    }

    **************************************************************

    Version 2 below: not working

    **************************************************************

    $Inputfile = Read-Host "Read in?"
    $Printservers =  get-content $Inputfile
    $Outputfile = Read-Host "Save to?
    $o = @()

    ForEach ($Printserver in $Printservers)
    {   $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver
        ForEach ($Printer in $Printers)
        {
            if ($Printer.Name -notlike "Microsoft XPS*")
            {   $o = new-object PSObject
                $o | add-member NoteProperty DriverName $Printer.DriverName
            If ($Printer.PortName -notlike "*\*")
                {   $Ports = Get-WmiObject Win32_TcpIpPrinterPort -Filter "name = '$($Printer.Portname)'" -ComputerName $Printserver
                    ForEach ($Port in $Ports)
                    {   $o | add-member NoteProperty PortName $Port.Name
                        $o | add-member NoteProperty IP $Port.HostAddress
                    }
                }
                $o | add-member NoteProperty ShareName $Printer.ShareName   
                $o | add-member NoteProperty Location $Printer.Location
                $o | add-member NoteProperty Name $Printer.Name
            }
        }
    } $o | export-csv $Outputfile -noType
    • Moved by Bill_Stewart Thursday, January 2, 2014 8:51 PM Question outside reasonable forum scope
    Monday, November 18, 2013 12:21 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    How does it "not work?" Do you receive a PowerShell error? Do you receive a blank CSV, or CSV with fields that are incorrect? Or...?
    Monday, November 18, 2013 1:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think you misunderstand what a CSV is.

    A CSV is a file with a header.

    ¯\_(ツ)_/¯

    Monday, November 18, 2013 1:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Start like this and look at what is happening.

    Get-WmiObject Win32_TcpIpPrinterPort | Select name, ipaddress | Export-Csv file -NoType

    Now. Gased on that explain what you need in your file.  YOu seem to be heading in the right direction but you are missing some things about WMI.

    ¯\_(ツ)_/¯

    Monday, November 18, 2013 1:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is how we do this with  WMI.  Run this.  It is missing one piece only.

    $Inputfile = Read-Host 'Read in?'
$Printservers =  get-content $Inputfile
$Outputfile = Read-Host 'Save to?'

ForEach ($Printserver in $Printservers){
	$printers=Get-WmiObject Win32_Printer -ComputerName $Printserver -Filter 'Not Name like "%Microsoft XPS%"'
	foreach($printer in $printers){
		$port=$printer.GetRelated('Win32_TcpIpPrinterPort')
		$props=@{
		        Name=$printer.Name
			DriverName=$Printer.DriverName
		        PortName=$port.Name
			IP=$port.HostAddress
			ShareName=$printer.ShareName
			Location=$printer.Location
		}
		New-Object PsObject -Property $props
	}
}

    ¯\_(ツ)_/¯






    • Edited by jrv Monday, November 18, 2013 1:57 AM
    Monday, November 18, 2013 1:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you for the reply.

    I needed to extract data from Win32_Printer to get the printer details and then from Win32_TcpIpPrinterPort to get the port name and IP address. The First version of the script works well but only extracts all the Data in a single column.

    I need to format the data somehow, either from file or in an array before output. Sorry, still learning Powershell :-)

    Example of output from Version 1

    HP LaserJet 5200 Series PCL 5
    hp_1
    10.0.0.9
    HPL001_Share
    L1
    HPL001
    HP LaserJet 5200 Series PCL 5
    hp_2
    10.0.0.10
    HPL002_Share
    L2
    HPL002
    HP LaserJet 5200 Series PCL 5
    hp_3
    10.0.0.11
    HPL003_Share
    L3
    HPL003

    Ideal Output example

    DriverName,PortName,IP,ShareName,Location,Name
    HP LaserJet 5200 Series PCL 5,hp_1,10.0.0.9,HPL001_Share,L1,HPL001
    HP LaserJet 5200 Series PCL 5,hp_2,10.0.0.10,HPL002_Share,L2,HPL002
    HP LaserJet 5200 Series PCL 5,hp_3,10.0.0.11,HPL003_Share,L3,HPL003


    • Edited by RitaKnap Monday, November 18, 2013 3:26 AM
    Monday, November 18, 2013 3:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is how we do this with  WMI.  Run this.  It is missing one piece only.

    $Inputfile = Read-Host 'Read in?'
$Printservers =  get-content $Inputfile
$Outputfile = Read-Host 'Save to?'

ForEach ($Printserver in $Printservers){
	$printers=Get-WmiObject Win32_Printer -ComputerName $Printserver -Filter 'Not Name like "%Microsoft XPS%"'
	foreach($printer in $printers){
		$port=$printer.GetRelated('Win32_TcpIpPrinterPort')
		$props=@{
		        Name=$printer.Name
			DriverName=$Printer.DriverName
		        PortName=$port.Name
			IP=$port.HostAddress
			ShareName=$printer.ShareName
			Location=$printer.Location
		}
		New-Object PsObject -Property $props
	}
}

    ¯\_(ツ)_/¯






    Thank you. When I run, it seams to be having an issue reading data from Win32_TcpIpPrinterPort

    Output

    Name       : HPL001
    DriverName : HP LaserJet 5200 Series PCL 5
    PortName   :
    ShareName  : HPL001_Share
    IP         :
    Location   : L1

    Monday, November 18, 2013 3:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The printer may not have a tcpip port.  Directly connected printers do not use TCPIP.

    ¯\_(ツ)_/¯

    Monday, November 18, 2013 3:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Start by changing this line:

    PortName=$printer.PortName

    ¯\_(ツ)_/¯

    Monday, November 18, 2013 3:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I found another article which assisted with exporting to csv so now I have the formatting working but it only stores the last printer within the array to export.

    Can anyone help to see what I am going wrong? Using Powershell v2

    $Printservers =  get-content "C:\scripts\servers.txt"
    $results = @()

    ForEach ($Printserver in $Printservers)  
    {   $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver
        ForEach ($Printer in $Printers)
        {
            if ($Printer.Name -notlike "Microsoft XPS*")
            {   $results = new-object PSObject
                $results | add-member NoteProperty Drv $Printer.DriverName
            If ($Printer.PortName -notlike "*\*")
                {   $Ports = Get-WmiObject Win32_TcpIpPrinterPort -Filter "name = '$($Printer.Portname)'" -ComputerName $Printserver
                    ForEach ($Port in $Ports)
                    {   $results | add-member NoteProperty Po $Port.Name
                        $results | add-member NoteProperty IP $Port.HostAddress
                    }
                }
                $results | add-member NoteProperty ShareName $Printer.ShareName  
                $results | add-member NoteProperty Location $Printer.Location
                $results | add-member NoteProperty na $Printer.Name
            }
        }
    }  $results | Export-Csv -NoTypeInformation -Path "C:\scripts\Results.csv"

    Monday, November 18, 2013 6:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Change this  

    $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver 
    $Printers += Get-WmiObject Win32_Printer -ComputerName $Printserver


    Regards Chen V [MCTS SharePoint 2010]

    Monday, November 18, 2013 10:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That code is not going to produce the correct output. 

    Please go back to the code I posted and make the change I requested.  It will show you why the ports are coming up incorrectly.

    You are using the wrong copy that I posted.  I willpost it again so you can see how it works.

    $Inputfile = Read-Host 'Read in?'
$Printservers =  get-content $Inputfile
$Outputfile = Read-Host 'Save to?'

ForEach ($Printserver in $Printservers){
	$printers=Get-WmiObject Win32_Printer -ComputerName $Printserver -Filter 'Not Name like "%Microsoft XPS%"'
	foreach($printer in $printers){
		$port=$printer.GetRelated('Win32_TcpIpPrinterPort')
		$props=@{
		        Name=$printer.Name
			DriverName=$Printer.DriverName
		        PortName=$printer.PortName
			IP=$port.HostAddress
			ShareName=$printer.ShareName
			Location=$printer.Location
		}
		New-Object PsObject -Property $props
	}
}

    The first copy that you ran had a typo which I quickly fixed  Apparently you grabbed the unedited copy.  That is why I am repasting it.  THe original had $ports and it should have been $port

    ¯\_(ツ)_/¯

    Monday, November 18, 2013 11:14 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Change this  

    $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver 
    $Printers += Get-WmiObject Win32_Printer -ComputerName $Printserver


    Regards Chen V [MCTS SharePoint 2010]

    Same issue, only last printer exported but thank you for the reply :)
    Monday, November 18, 2013 11:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I have used the reworked version but it only seams to be able to access Win32_Printer class. It doesn't seam to want to read from the Win32_TCPIPPrinterPort class (Output1) where the IP details are stored which is one piece I need to recover (As per Output2)?

    Output1:
    Name       : HPL001
    DriverName : HP LaserJet 5200 Series PCL 5
    PortName   : hp
    ShareName  : HPL001_Share
    IP         :
    Location   : L1

    Output2:
    HPL001
    HP LaserJet 5200 Series PCL 5
    hp
    HPL001_Share
    10.0.0.9
    L1

    Monday, November 18, 2013 11:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Some progress, however, the output need working on.

    Is there a way I can clean up the repeated lines and also insert the , between the output and also only leave the first heading?

    Script:

    #######################################################

    $Printservers =  get-content "C:\scripts\servers.txt"
    $results = @()

    ForEach ($Printserver in $Printservers)  
    {   $Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver
        ForEach ($Printer in $Printers)
        {
            if ($Printer.Name -notlike "Microsoft XPS*")
            {   $results = new-object PSObject
                $results | add-member NoteProperty Drv $Printer.DriverName
            If ($Printer.PortName -notlike "*\*")
                {   $Ports = Get-WmiObject Win32_TcpIpPrinterPort -Filter "name = '$($Printer.Portname)'" -ComputerName $Printserver
                    ForEach ($Port in $Ports)
                    {   $results | add-member NoteProperty Port $Port.Name
                        $results | add-member NoteProperty IP $Port.HostAddress
                    }
                }
                $results | add-member NoteProperty ShareName $Printer.ShareName  
                $results | add-member NoteProperty Location $Printer.Location
                $results | add-member NoteProperty Name $Printer.Name
                Write-Output $results | format-table -wrap >> "C:\scripts\Results.txt"
                }
        }
    }

    #######################################################

    *************

    Current Output:

    *************

    Drv                     Port   IP           ShareName       Location   Name
    ---                       ----    --            ---------              --------       ----
    HP LaserJet 520 hp     10.0.0.9  HPL001_Share  L1             HPL001
    Series PCL 5 

    Drv                     Port   IP           ShareName       Location   Name
    ---                       ----    --            ---------              --------       ----
    HP LaserJet 520 hp2     10.0.0.10  HPL002_Share  L2             HPL002
    Series PCL 5

    *************

    Desired Output

    *************

    Drv,Port,IP,ShareName,Location,Name                       
    HP LaserJet 5200 Series PCL 5,hp,10.0.0.9,HPL001_Share,L1,HPL001                    
    HP LaserJet 5200 Series PCL 5,hp2,10.0.0.10,HPL002_Share,L2,HPL002


    • Edited by RitaKna Tuesday, November 19, 2013 12:02 AM
    Tuesday, November 19, 2013 12:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I have used the reworked version but it only seams to be able to access Win32_Printer class. It doesn't seam to want to read from the Win32_TCPIPPrinterPort class (Output1) where the IP details are stored which is one piece I need to recover (As per Output2)?

    Output1:
    Name       : HPL001
    DriverName : HP LaserJet 5200 Series PCL 5
    PortName   : hp
    ShareName  : HPL001_Share
    IP         :
    Location   : L1

    Output2:
    HPL001
    HP LaserJet 5200 Series PCL 5
    hp
    HPL001_Share
    10.0.0.9
    L1

    Can you please paste the exact copy of the code you are trying to run.  I am sure you have copied it incorrectly.  The scritp I pasted has been tested and runs as expected.

    ¯\_(ツ)_/¯

    Tuesday, November 19, 2013 12:37 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I see what I missing on your copy.  It is a result of trying to use your code as a template  I missed a bit.

    This will be able to read the port under all circumstances.

    $Inputfile = Read-Host 'Read in?'
$Printservers =  get-content $Inputfile
$Outputfile = Read-Host 'Save to?'

ForEach ($Printserver in $Printservers){
	$printers=Get-WmiObject Win32_Printer -ComputerName $Printserver -Filter 'Not Name like "%Microsoft XPS%"'
	foreach($printer in $printers){
		$port=Get-WmiObject Win32_TCPIPPrinterPort -EnableAllPrivileges -Filter "Name='$($printer.PortName)'"
		
		$props=@{
		    Name=$printer.Name
			DriverName=$Printer.DriverName
		    PortName=$printer.PortName
			IP=$port.HostAddress
			ShareName=$printer.ShareName
			Location=$printer.Location
		}
		New-Object PsObject -Property $props
	}
}

    Part of the issues is that we need to have security tokens to read the port IP   (-EnableAllPrivileges)

    ¯\_(ツ)_/¯

    Tuesday, November 19, 2013 1:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Of course you realize that WS 2008 PrintManager can export all of this right from the console.  It can also copy printer definitions between servers. It is full set up to support clustered print servers.

    ¯\_(ツ)_/¯

    Tuesday, November 19, 2013 1:20 AM