3

I want to export a list of Test-Connection results either to csv or txt file - either one is fine, but I'm not sure how to do it.

An alternative would be to display the 'online' results in green and 'offline' results in red.

This code shows a neat table that I would like to export to file as well. I've been trying for hours various iterations of export-csv and output file and the file is either empty or not created at all.

## read file and create array
$devices = Get-Content -Path C:\temp\devices.txt

## loop through each device
foreach($device in $devices)
{
    $test = Test-Connection -ComputerName $device -Count 1 -Quiet
[pscustomobject]@{
    DeviceName = $device
    Status = if($test){'ONLINE'}
         else{'OFFLINE'}
    }
}

Result:

results

Another thing I tried is show the results in color like this:

## read file and create array
$devices = Get-Content -Path C:\temp\devices.txt

## loop through each device
foreach($device in $devices)
{
    $test = Test-Connection -ComputerName $device -Count 1 -Quiet
[pscustomobject]@{
    DeviceName = $device
    Status = if($test){Write-Host "ONLINE" -ForegroundColor Green}
         else{Write-Host "OFFLINE" -ForegroundColor Red}
    }
}

But the resulting table formatting gets all messed up:

colored_results

Improve this question
2
  • 1
    Does this answer your question: stackoverflow.com/questions/73558709/… ? It's unclear if you're asking for colors in the console or how to export the output to a file Commented Sep 24 at 22:32
  • @SantiagoSquarzon Yes, that was the answer to my coloring problem - I just didn't know how to properly look for an answer, was using wrong keywords. Thank you for the link! Commented Sep 25 at 11:14

1 Answer 1

Reset to default
6

Regarding coloring the values in the Status property, Write-Host writes output to a different stream than Success, when you write output to different streams in the same pipeline you will see issues as the one you're observing. See about_Output_Streams for more info.

What you can do is to embed VT sequences in your string, as demonstrated in Powershell Different color in strings with echo in array. Also about_ANSI_Terminals is worth a read.

In summary, this is how you can approach your code:

# In pwsh 7+ you can use the `$PSStyle` variable:
#
# $map = @{
#   $false = "$($PSStyle.Foreground.BrightRed)OFFLINE$($PSStyle.Reset)"
#   $true  = "$($PSStyle.Foreground.BrightGreen)ONLINE$($PSStyle.Reset)"
# }

$map = @{
    $false = "$([char] 27)[91mOFFLINE$([char] 27)[0m"
    $true  = "$([char] 27)[92mONLINE$([char] 27)[0m"
}

## read file and create array
$devices = Get-Content -Path C:\temp\devices.txt

## loop through each device
foreach ($device in $devices) {
    $test = Test-Connection -ComputerName $device -Count 1 -Quiet

    [pscustomobject]@{
        DeviceName = $device
        Status     = $map[$test]
    }
}

Regarding outputting to a file, first capture the output to a variable, then use the desired cmdlet (Export-Csv or Out-File)... This has been explained before and you can find many answers in this site.

## read file and create array
$devices = Get-Content -Path C:\temp\devices.txt

## loop through each device
$result = foreach ($device in $devices) {
    $test = Test-Connection -ComputerName $device -Count 1 -Quiet
    [pscustomobject]@{
        DeviceName = $device
        Status     = if ($test) { 'ONLINE' } else { 'OFFLINE' }
    }
}

# pipe to the desired output cmdlet...
$result | Export-Csv C:\path\to\the\File.csv -NoTypeInformation

If, however, what you want is to see the colors in the Excel file itself, note that CSV is a plain-text format. You will need an actual .xlsx file, and for that you can check out How to add colors to Excel output file in Powershell, which uses the ImportExcel Module with Conditional Text.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.