Output results to Excel but in different columns

PowerShell Help
#1

I am brand new to PS so if I sound like I don’t know what I am talking about, its because I don’t.

I pilfered a script that pings hostnames and returns the IP address. The result is written to a csv. This all works well. However it writes the hostname and IP address in the same cell. How can I write them to different columns?

Here is what I have:

$PingPCs = Get-Content .\TestPing.txt 
$Date = Get-Date 
$Date >> PingResult.csv
$Date
"Server , 'IP Address'"  >> PingResult.csv
$Date >> PingFailed.csv
foreach ($PC in $PingPCs)
{
$up = Test-Connection $PC -Count 1 -Quiet 
if($up) {
#Test-Connection $PC -Count 1 | Select-Object Address,IPv4address  >> PingResult.csv
$Response = Test-Connection $PC -Count 1 | ft Address,IPv4address -HideTableHeaders
$Response >> PingResult.csv
}
else {
$Failed =  "$PC is not reacheble" 
$Failed >> PingFailed.csv
Write-host "$PC is not reacheble" -ForegroundColor Red
}
}
#Write-Host $PingFailed

Any help would be greatly appreciated! Thank you!

-Josh

#2

Based on your existing code, this is one of likely many ways …

$Response = Test-Connection $PC -Count 1
"$($Response.Address),$($Response.IPV4Address.IPAddressToString)".Trim() >> .\PingResult.CSV
#3

Thank you for your response, tonyd! I tried to paste this into my code, but it isn’t working. I get an RPC Server not available error. This is what I am using for those lines:

#Test-Connection $PC -Count 1 | Select-Object Address,IPv4address  >> PingResult.csv
$Response = Test-Connection $PC -Count 1 "$($Response.Address),$($Response.IPV4Address.IPAddressToString)".Trim() >> .\PingResult.CSV
$Response >> PingResult.csv

Is that the correct way to use this code? Im sorry if this is an obvious answer. I truly have no idea what I am doing in PowerShell

#4

No, you need separate lines as in my post.

From your original code, replace these lines:

$Response = Test-Connection $PC -Count 1 | ft Address,IPv4address -HideTableHeaders
$Response >> PingResult.csv

With these lines:

$Response = Test-Connection $PC -Count 1
"$($Response.Address),$($Response.IPV4Address.IPAddressToString)".Trim() >> .\PingResult.CSV
#5

Am I doing this wrong? The results are exactly the same as the code I posted.

$PingPCs = Get-Content .\TestPing.txt 
$Date = Get-Date 
$Date >> PingResult.csv
$Date
"Server , 'IP Address'"  >> PingResult.csv
$Date >> PingFailed.csv
foreach ($PC in $PingPCs)
{
$up = Test-Connection $PC -Count 1 -Quiet 
if($up) {
#Test-Connection $PC -Count 1 | Select-Object Address,IPv4address  >> PingResult.csv
$Response = Test-Connection $PC -Count 1
"$($Response.Address),$($Response.IPV4Address.IPAddressToString)".Trim() >> .\PingResult.CSV
}
else {
$Failed =  "$PC is not reacheble" 
$Failed >> PingFailed.csv
Write-host "$PC is not reacheble" -ForegroundColor Red
}
}
#Write-Host $PingFailed

Resulting in this:

Tuesday, July 26, 2022 10:13:08 AM
Server , ‘IP Address’

192.168.1.120 (I redacted the hostname for security reasons)

Both the hostname and IP address are in the same cell. Here is a screenshot that shows it clearly:

image

#6

Well, I have to admit, I did not test opening in Excel, my bad on that. I created a new CSV in excel, compared that to the output of the PowerShell, and no difference. Yet, opening each file individually obtained different results. The PS output opened in one cell as you state.

Sadly, I dont have the time to get this figured, but there is a workaround. You can open a blank Excel document, go to the Data tab and select “From Text/CSV” and open your file that way.

I think the master Olaf will have an answer for you at some point. I will play more when I have the time.

#7

I’d recommend to work with objects and properties instead of strings and to use Export-Csv instead of Out-File for the output to a CSV file…

And depending on your locale setting you could try to use another delimiter for your CSV files. For German systems for example we have to use semikolons to get Excel to recognize it as the default delimiter for CSV files. :wink:

I’d start with something like this:

$InputPcList = Get-Content -Path .\TestPing.txt
$InputPcList = 'BRZWRB1501','BRZNRB1501'
$Result =
foreach ($ComputerName in $InputPcList) {
    $ResultHashTable = @{
        InputComputerName = $ComputerName
        Source            = $ENV:COMPUTERNAME
        Destination       = 'n/a'
        IPV4Address       = 'n/a'
    }
    try {
        $PingResult = Test-Connection -ComputerName $ComputerName -Count 1 -ErrorAction Stop
        $ResultHashTable.IPV4Address = $PingResult.IPV4Address
        $ResultHashTable.Destination = $PingResult.Address
    }
    catch {
        $ResultHashTable.IPV4Address = 'n/a'
        $ResultHashTable.Destination = 'n/a'
    }

    [PSCustomObject]$ResultHashTable
}
$Result |
    Format-Table -AutoSize

Instead of Foramt-Table you could pipe the output to Export-Csv. :wink:

BTW: Since Test-Connection produces another output format in PowerShell version 7 you would need adapt the code a little bit. :wink: