PowerShell - Download from SFTP

[Disturbedguy]

Hi all,

Hoping that there is a PowerShell / Scripting guru on here to give a guy a hand.

I have some PowerShell that I've cobbled together from lots of googling. The PowerShell connects to an SFTP mailbox, downloads the file (there is only ever one file in the location), renames the file to yesterdays date in the dd-mm-yyyy format and saves it as .txt, then replaces the end of line identifier with CRLF ('r 'n) and then saves again so it can be uploaded into another piece of software.

Now the odd thing is, it works, kind of.

The first few times I ran it, it seemed to run fine, except when it was editing the end of line identifier which I eventually fixed. It then worked fine a few times then started failing again and I can't figure out why.

So far its -

Worked fine (Downloaded file, renamed and then amended the end of line identifier)
Downloads the file, renames it and saves it but doesn't amend the end of line identifier
I get an error that the file with yesterdays date doesn't exist, but if i check the download location, the file is present, but its 0bytes
Downloads the file, renames it and amends the end of line identifier but adds umpteen extra lines
And then others it runs but simply doesn't download the file

I initially thought it might be because it was trying to run the various bits of the script at once, so between each function I added a Start-Sleep 5 seconds to pause between each, but it still failed.
I still think it's trying to run everything at once, instead of running them in order

Now, I'm not a programmer or someone that generally does any scripting so I imagine there's loads wrong with the below. Therefore any assistance would be fantastic.

Code below -

#===========================================================================

# ** banking file download and fix script

# This script was created due to continuing issues with getting the Expedite

# software to install and run on Windows 10

#

# The powershell script needs to sit in \\***\

# The batch file - Expedite.bat can be placed anywhere

#

# The script downloads the *** banking file from and sftp mailbox -

# ***. 

#

# The download file is saved as .txt and is given the previous days date

#

# The file is then opened in the background (The user does not see this)

# and the hex of the file is edited to correctly determine the end of line.

# If this step does not complete, the file cannot be imported into Alloc8.

# This is because the end of each line is marked with LF instead of CRLF,

# the final part of this script adds the CR and then resaves the document.

#

# Author: 

#

#===========================================================================

 

# Load WinSCP .NET assembly

Add-Type -Path ****\WinSCPnet.dll

 

# Set up session options

$sessionOptions = New-Object WinSCP.SessionOptions -Property @{

Protocol = [WinSCP.Protocol]::Sftp

HostName = "***"

 UserName = "***"

Password = "***"

SshHostKeyFingerprint = "****"

}

 

$session = New-Object WinSCP.Session

 

try

{

    # Connect

    Write-Host "Connecting..."

    $session.Open($sessionOptions)

 

    # This changes to the relevant directory and then saves the file to the required directory

    # Left side, before the , is where it browses too, after the , is the location it saves too

    # The * in the download source, means any file.  There should only ever be one file to download

   $session.GetFiles("****", "****\").Check()

}

finally

{

   # Self explanatory - this disconnects from the SFTP mailbox

   $session.Dispose()

 

 

}

 

 

# Sets yesterdays date and the format it should be in

$YDate = ((Get-Date).AddDays(-1).ToString("dd-MM-yyyy"))

# Gets all files in directory.  There should only ever be one file in this direcory

# which is the *** file that is downloaded

$files = Get-ChildItem -Path \\location\ | where { $_.Name -like "*" }

# loop through each file and rename it

foreach ($file in $files)

{

    # Takes the file and renames it with yesterdays date and changes its extention to .txt

   $newfilename = "$($YDate).txt"

   Rename-Item -Path $file.FullName -NewName "$($file.Directory.FullName)\$newfilename"

 

  

 }

 


 

# Appenend end of lines with appropriate CRLF to allow prooper processing of document by Alloc8  

$original_file ="****\$newfilename" 

$text = [IO.File]::ReadAllText($original_file) -replace  "`n", "`r`n"

[IO.File]::WriteAllText($original_file, $text)

[b0redom]

Sorry, I'm not a powershell guy, you might try StackOverflow or similar. I would, however, strongly recommend you remove references to your employer and real domains!

[Disturbedguy]

Sorry, I'm not a powershell guy, you might try StackOverflow or similar. I would, however, strongly recommend you remove references to your employer and real domains!

Oops! I copied the one I hadn't amended in!

[GuruNot]

Here is my two penneth after some googling too. It resets all End of Line instances in the original file and then writes out to a new file having replaced them with CRLF. It then removes the original file.

As you mentioned the assumption is that there is only ever one file in the directory you scan, otherwise you will need to come up with a new format for $newfilename.

# Sets yesterdays date and the format it should be in

$YDate = ((Get-Date).AddDays(-1).ToString("dd-MM-yyyy"))

# Gets all files in directory.  There should only ever be one file in this direcory

# which is the *** file that is downloaded

$files = Get-ChildItem -Path c:\test | where { $_.Name -like "*" }

# loop through each file

foreach ($file in $files) {

	# Generate Filename with yesterdays date
	$newfilename = "$($YDate).txt"
   
	#Create Variable holding absolute Path of new file
	$newfilepath = "c:\test\$newfilename"

	# Replace Existing CR+LF with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r`n", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	# Replace Existing CR with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	#  At this point all line-endings should be LF.

	# Replace LF with CRLF and write out to new file with date filename
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`n", "`r`n"
	[IO.File]::WriteAllText("$newfilepath", $text)
 
	#Remove Old File
	Remove-Item -Path $file.fullname -Force
  

 }

[matts-uk]

At first glance, the symptoms sound like you might be attempting to process the file before the download is finished, or when the download failed in some way.

I've written a number of WSH scripts that did similar things in the past. You may want to have a look at curl, which is a general purpose download utility, suited to scripting and available cross platform.

[Disturbedguy]

Here is my two penneth after some googling too. It resets all End of Line instances in the original file and then writes out to a new file having replaced them with CRLF. It then removes the original file.

As you mentioned the assumption is that there is only ever one file in the directory you scan, otherwise you will need to come up with a new format for $newfilename.

# Sets yesterdays date and the format it should be in

$YDate = ((Get-Date).AddDays(-1).ToString("dd-MM-yyyy"))

# Gets all files in directory.  There should only ever be one file in this direcory

# which is the *** file that is downloaded

$files = Get-ChildItem -Path c:\test | where { $_.Name -like "*" }

# loop through each file

foreach ($file in $files) {

	# Generate Filename with yesterdays date
	$newfilename = "$($YDate).txt"
   
	#Create Variable holding absolute Path of new file
	$newfilepath = "c:\test\$newfilename"

	# Replace Existing CR+LF with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r`n", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	# Replace Existing CR with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	#  At this point all line-endings should be LF.

	# Replace LF with CRLF and write out to new file with date filename
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`n", "`r`n"
	[IO.File]::WriteAllText("$newfilepath", $text)
 
	#Remove Old File
	Remove-Item -Path $file.fullname -Force
  

 }

Thanks, from reading, it looks like that may well be what i need. Am I correct in thinking I can add this to the end of the code I have for connecting to the SFTP Mailbox and grabbing the file?

[GuruNot]

Thanks, from reading, it looks like that may well be what i need. Am I correct in thinking I can add this to the end of the code I have for connecting to the SFTP Mailbox and grabbing the file?

I stood up a quick local ftp server and played with that assembly , it is slightly different to yours but this code worked ok for me, here is the full script , change the paths details as needed, you will need to change session back to sftp and add in ssh key:

try
{
    # Load WinSCP .NET assembly
    Add-Type -Path "WinSCPnet.dll"
 
    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::ftp
        HostName = "127.0.0.1"
        UserName = "bob"
        Password = "mypassword"
        
    }
 
    $session = New-Object WinSCP.Session
 
    try
    {
        # Connect
        $session.Open($sessionOptions)
 
        # Download files
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
 
        $transferResult =
            $session.GetFiles("/testdown/*", "c:\test\", $False, $transferOptions)
 
        # Throw on any error
        $transferResult.Check()
 
        # Print results
        foreach ($transfer in $transferResult.Transfers)
        {
            Write-Host "Download of $($transfer.FileName) succeeded"
        }
    }
    finally
    {
        # Disconnect
        $session.Dispose()
		
		# Sets yesterdays date and the format it should be in

	$YDate = ((Get-Date).AddDays(-1).ToString("dd-MM-yyyy"))

	# Gets all files in directory.  There should only ever be one file in this direcory

	# which is the *** file that is downloaded

	$files = Get-ChildItem -Path c:\test | where { $_.Name -like "*" }

	# loop through each file and rename it

	foreach ($file in $files) {

	# Generate Filename with yesterdays date
	$newfilename = "$($YDate).txt"
   
	#Create Variable holding absolute Path of new file
	$newfilepath = "c:\test\$newfilename"

	# Replace Existing CR+LF with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r`n", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	# Replace Existing CR with LF
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`r", "`n"
	[IO.File]::WriteAllText($file.fullname, $text)

	#  At this point all line-endings should be LF.

	# Replace LF with CRLF and write out to new file with date filename
	$text = [IO.File]::ReadAllText($file.fullname) -replace "`n", "`r`n"
	[IO.File]::WriteAllText("$newfilepath", $text)
 
	#Remove Old File
	Remove-Item -Path $file.fullname -Force
  

 }
		
    }
 
    exit 0
}
catch
{
    Write-Host "Error: $($_.Exception.Message)"
    exit 1
}

[kalniel]

Enable Windows Subsystem for Linux and do it more easily from a linux shell?

[Disturbedguy]

Enable Windows Subsystem for Linux and do it more easily from a linux shell?

haha, unfortunately I have to make something a user could easily run if needed and it had to be done as soon as possible

GuruNot, thanks. After some testing and tweaking it works perfectly.