Script to export to excel

Question

I have below script: - search for help to convert output to excel format

$servers = get-content "c:\list.txt" foreach ($server in $servers) { $server $command = "quser /server:" + $server invoke-expression $command } 

when receiving in a format below the output file.

 server1 USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME Vdw231 ica-tcp#8 7 Active . 11/5/2012 10:40 AM Vdw232 ica-tcp#60 16 Active 16:18 11/5/2012 2:22 PM Vdw233 ica-tcp#71 3 Active . 11/6/2012 6:10 AM Vdw234 ica-tcp#72 1 Active 3 11/6/2012 6:59 AM Vdw235 ica-tcp#73 5 Active . 11/6/2012 6:59 AM Vdw236 rdp-tcp#74 2 Active . 11/6/2012 7:07 AM server2 USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME Vdw210 ica-tcp#44 14 Active 13:50 11/5/2012 9:03 AM Vdw211 ica-tcp#67 6 Active . 11/6/2012 1:56 AM Vdw212 ica-tcp#70 1 Active 45 11/6/2012 6:34 AM Vdw213 ica-tcp#72 9 Active 25 11/6/2012 6:53 AM Vdw214 server3 USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME Vdw215 rdp-tcp#131 1 Active 19 11/5/2012 1:42 AM Vdw216 rdp-tcp#132 4 Active 17 11/5/2012 2:06 AM Vdw217 rdp-tcp#143 6 Active . 11/6/2012 3:31 AM 

My requirement is that I would like to convert this output to excel format for sending to management. Below is the excel format I think ... to have a script on top ...

Answer 1

I rewrote this, but I have not tested the full script, and it is not optimized. If you encounter any problems, feel free to contact me.

  $statuses = @() $servers = get-content "c:\list.txt" $splitter = [regex]"\s+" foreach ($server in $servers) { $command = "quser /server:$server" $lines = @((invoke-expression $command | Out-String) -split "`n") #remove header $lines = $lines[1..$lines.count] foreach ($line in $lines) { $attrs = @($splitter.Split($line.Trim(),6)) if ( $attrs -eq 6 ) { $status = New-Object PSCustomObject -Property @{ "SERVER"=$server; "USERNAME"=$attrs[0]; "SESSIONNAME"=$attrs[1]; "ID"=$attrs[2]; "STATE"=$attrs[3]; "IDLE_TIME"=$attrs[4]; "LOGON_TIME"=[datetime]$attrs[5]} $statuses += $status } } } #your filter here #$statuses = $statuses | where{ XXXXX } $statuses | Export-Csv G:/test.csv -NoTypeInformation 

Answer 2

You need to convert the PSObject into an excel compatible array, and after you can write this array to an excel sheet

include this code in your * .PS1 script and use it like this: get-process | Export-Excel

 #============================================================================= # Convert powershell Object to Array for Excel #============================================================================= function ConvertTo-MultiArray { <# .Notes NAME: ConvertTo-MultiArray AUTHOR: Tome Tanasovski Website: http://powertoe.wordpress.com Twitter: http://twitter.com/toenuff Version: 1.2 .Synopsis Converts a collection of PowerShell objects into a multi-dimensional array .Description Converts a collection of PowerShell objects into a multi-dimensional array. The first row of the array contains the property names. Each additional row contains the values for each object. This cmdlet was created to act as an intermediary to importing PowerShell objects into a range of cells in Exchange. By using a multi-dimensional array you can greatly speed up the process of adding data to Excel through the Excel COM objects. .Parameter InputObject Specifies the objects to export into the multi dimensional array. Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to ConvertTo-MultiArray. .Inputs System.Management.Automation.PSObject You can pipe any .NET Framework object to ConvertTo-MultiArray .Outputs [ref] The cmdlet will return a reference to the multi-dimensional array. To access the array itself you will need to use the Value property of the reference .Example $arrayref = get-process |Convertto-MultiArray .Example $dir = Get-ChildItem c:\ $arrayref = Convertto-MultiArray -InputObject $dir .Example $range.value2 = (ConvertTo-MultiArray (get-process)).value .LINK http://powertoe.wordpress.com #> param( [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)] [PSObject[]]$InputObject ) BEGIN { $objects = @() [ref]$array = [ref]$null } Process { $objects += $InputObject } END { $properties = $objects[0].psobject.properties |%{$_.name} $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count # i = row and j = column $j = 0 $properties |%{ $array.Value[0,$j] = $_.tostring() $j++ } $i = 1 $objects |% { $item = $_ $j = 0 $properties | % { if ($item.($_) -eq $null) { $array.value[$i,$j] = "" } else { $array.value[$i,$j] = $item.($_).tostring() } $j++ } $i++ } $array } } #============================================================================= # Export pipe in Excel file #============================================================================= function Export-Excel { [cmdletBinding()] Param( [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)] [PSObject[]]$InputObject ) begin{ $header=$null $row=1 $xl=New-Object -ComObject Excel.Application $wb=$xl.WorkBooks.add(1) $ws=$wb.WorkSheets.item(1) $xl.Visible=$false $xl.DisplayAlerts = $false $xl.ScreenUpdating = $False $objects = @() } process{ $objects += $InputObject } end{ $array4XL = ($objects | ConvertTo-MultiArray).value $starta = [int][char]'a' - 1 if ($array4XL.GetLength(1) -gt 26) { $col = [char]([int][math]::Floor($array4XL.GetLength(1)/26) + $starta) + [char](($array4XL.GetLength(1)%26) + $Starta) } else { $col = [char]($array4XL.GetLength(1) + $starta) } $ws.Range("a1","$col$($array4XL.GetLength(0))").value2=$array4XL $wb.SaveAs("$([Environment]::GetFolderPath('desktop'))\Export-Excel ($(Get-Date -Format u)).xlsx") $xl.Quit() Remove-Variable xl } } 

You are getting