Reading values from CSV files and storing in local variable in PowerShell

Question

I am stuck at one point, the requirement is I have to store the Username, Password, Hostname & hostkey in a CSV file and I have to read & store the values in local variable which can be used for establishing SFTP connection.

My CSV looks like this:

HostName,Username,Password,SshHostKeyFingerprint
abc.com,Lear,qwert,ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx...

The code which I am using to read and store the different columns values is:

Add-Type -Path "WinSCPnet.dll"

$csv = Import-Csv c:\test\output.csv
$csv | ForEach-Object
{
    $Hostname = $_.hostname
    $username = $_.username
    $Password = $_.Password
    "$Hostname - $username -$Password"
}

# Set up session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = $Hostname
    UserName = $username
    Password = $Password
    SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
}

Nothing is getting displayed, when trying to display the values.

Answer 1

The script block after the ForEach-Object cmdlet has to start on the same line. Otherwise PowerShell won't connect them together.

$csv | ForEach-Object {
    $HostName = $_.HostName
    $Username = $_.Username 
    $Password = $_.Password
    "$HostName - $Username - $Password"
}

---

Another problem, that your will face eventually, is that your code does process only the last line in the CSV file.

You most probably actually want to process the parsed out values within the ForEach-Object script block. Like this:

$csv | ForEach-Object {
    $HostName = $_.HostName
    $Username = $_.Username 
    $Password = $_.Password

    "$HostName - $Username - $Password"

    # Set up session options
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = $HostName
        UserName = $Username
        Password = $Password
        SshHostKeyFingerprint = ...
    }

    # ...
}