Import a CSV, run port connection, then export to new csv

Question

I have a Sources.csv which has columns "Source," "Host" and "Port" (Source is just the name of the device I'm trying to connect to). My goal is to go line-by-line through this CSV, run a connection test to the IP and Port, then export to a new csv file to include these same columns as well as a new column "Reachable." This is what I have so far, but my csv file is filling up with all sorts of information that doesn't seem to make sense... So I'm curious if anyone can point me in the right direction:

$path = '.\Sources.csv'
$csv = Import-Csv -Path $path
Foreach($line in $csv){
    $TCPTest = Test-NetConnection $line.host -Port $line.port
    $Test = @{DataSource=$line.source; IP=$line.host; Port=$line.port; 
        Reachable=$TCPTest.TcpTestSucceeded}
    $Test | Export-Csv -Path .\SourceChecks.csv -append
    }

Were you able to solve your problem? – jimhark Aug 24 '22 at 16:55 — jimhark, Aug 24 '22 at 16:55

score 1 · Answer 1 · edited Aug 23 '22 at 17:05

You can handle this using calculated properties with Select-Object:

$path = '.\Sources.csv'
Import-Csv -Path $path | Select-Object *, @{
    Name       = 'Reachable'
    Expression = {Test-NetConnection $_.Host -Port $_.Port -InformationLevel Quiet}
} | Export-Csv newpathtocsv.csv -NoTypeInformation

Using -InformationLevel Quiet on Test-NetConnection so the cmdlet returns a boolean:

If you set this parameter to Quiet, the cmdlet returns basic information. For example, for a ping test, this cmdlet returns a Boolean value that indicates whether the attempt to ping a host or port is successful.

You can also add -WarningAction Ignore if you wish to not see the warning messages displayed to the console.

It's worth noting that Test-NetConnection is quite slow if you have many target hosts and ports to test connectivity. If you're looking to speed up this process you can give this function a try. It leverages the async capabilities from the .NET TcpClient Class.

An example, using the following CSV as input:

Host,Port
google.com,80
google.com,8080
cisco.com,80
cisco.com,443
amazon.com,80
amazon.com,389
amazon.com,636

Output would become:

PS /> Import-Csv $path | Test-TCPConnectionAsync -TimeOut 10

Source             Destionation Port Success
------             ------------ ---- -------
sourceComputerName google.com     80    True
sourceComputerName cisco.com     443    True
sourceComputerName cisco.com      80    True
sourceComputerName amazon.com     80    True
sourceComputerName google.com   8080   False
sourceComputerName amazon.com    389   False
sourceComputerName amazon.com    636   False

Nice use of InformationLevel parameter. I admit I didn't know about that and would have selected TcpTestSucceeded from the full results. — jimhark, Aug 22 '22 at 17:37
@jimhark thanks for the edit. Clearly I rarely use `Select-Object` calculated properties — Santiago Squarzon, Aug 23 '22 at 17:08
No problem. I used to use calculated properties all the time when my code had to run on PowerShell version 2. (Ugh!) Now I'm much happier using PSCustomObject. — jimhark, Aug 23 '22 at 17:19

score 1 · Accepted Answer · answered Aug 22 '22 at 17:36

Santango's answer works all the way back to PowerShell version 1 (I think).

If you can accept a dependency on PowerShell Version 3 (released in 2012), there is another option:

$sourcesPath = '.\Sources.csv'

$sources = Import-Csv $sourcesPath

$sources | ForEach {
    $reachable = Test-NetConnection $_.host -Port $_.port -InformationLevel Quiet

    [PSCustomObject]@{
        Source = $_.Source
        Host = $_.Host
        Port = $_.Port
        Reachable = $reachable
    }
} | 
Export-Csv newpathtocsv.csv

Thanks! This worked perfectly! Sorry it took so long to respond. — Christopher Cass, Aug 25 '22 at 18:10

Import a CSV, run port connection, then export to new csv

2 Answers2