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.

For general security reasons, you cannot just pipe a plain text password to WinSCP (or any other Microsoft application). You will need to create a credentials (New-Object Management.Automation.PSCredential) by using a secured password (ConvertTo-SecureString -String "something" -AsPlainText -Force), see e.g.: stackoverflow.com/a/41548976/1701026 — iRon
– iRon, Commented Oct 11, 2017 at 12:09

Martin Prikryl · Accepted Answer · 2017-10-12 11:04:20Z

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 = ...
    }

    # ...
}

edited Oct 12, 2017 at 11:04

answered Oct 11, 2017 at 12:07

Martin Prikryl

205k64 gold badges561 silver badges1.1k bronze badges

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

7 Comments

Ratnesh Sharma Over a year ago

At the time when we are importing the CSV from local machine following error is coming: Import-Csv : Cannot bind parameter 'Delimiter'. Cannot convert value "Integration\Scripts\Power" to type "System.Char". Error: "String must be exactly one character long."

Ratnesh Sharma Over a year ago

This is the actual path:

# Load WinSCP .NET assembly Add-Type -Path "WinSCPnet.dll"   $csv = Import-Csv C:\abc\Projects\Bank\Attendance Integration\Scripts\Power Shell Script\Configuration.csv $csv | foreach-object {     $Hostname = $_.hostname     $username =$_.username     $Password = $_.Password      "$Hostname - $username -$Password"        }      $session.Dispose()

Martin Prikryl Over a year ago

You have to enclose the path with spaces to quotes: $csv = Import-Csv "C:\abc\Projects\Bank\Attendance Integration\Scripts\Power Shell Script\Configuration.csv"

Ratnesh Sharma Over a year ago

Hi, One more error is coming in the code:

$csv = Import-Csv "C:\abc\Projects\Bank\Attendance Integration\Scripts\Power Shell Script\Configuration.csv" $csv | foreach-object {     $Hostname = $_.hostname     $username =$_.username     $Password = $_.Password     $hostkey = $_.hostkey       } $sessionOptions = New-Object WinSCP.SessionOptions -Property @{     Protocol = [WinSCP.Protocol]::Sftp     HostName = $Hostname     UserName = $username     Password = $Password     SshHostKeyFingerprint = $hostkey }

Ratnesh Sharma Over a year ago

The error which is coming is:

New-Object : The value supplied is not valid, or the property is read-only. Change the value, and then try again. At C:\abc\Projects\Bank\Attendance Integration\Scripts\Power Shell Script\Download&MoveToArchive.ps1:16 char:19

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related