Results 1 to 11 of 11
-
2020-08-28, 11:20 PM (ISO 8601)
- Join Date
- Jan 2009
program to log into an SFTP site and load files
I'd like to do this in SAS, but R or Python are also options.
EDIT: VBA is also an option. Probably second after SAS, as I have some code in SAS to write and execute VBA scripts, so I could essentially run it in my language-of-choice.
Goal: to have a program that logs into an online SFTP site (I have the URL, and my username and password. Once 'logged in', it goes straight to the folder I want to load the files to), then uploads a file on my computer to it.
Reason for goal: I need to automate creating and uploading files to an SFTP site. I got the creating the files automated, but I still have to manually log into the SFTP site and upload them. Since ideally I'd be regenerating the files daily, rather get around the manual part.
Issues: I'd like to do this without any third-party software or extra-programming-language stuff. I found a way to probably do it with SAS, but SAS doesn't support password-login with SFTP (why!?) and instead requires me to make a primary-public key setup with the SFTP server; I'd like to avoid that. I found the RCurl package in R which seems likely to do that, but am having some issues with installing the package AND realized it requires another software to be installed to work. I haven't looked into Python yet, but I've got Anaconda installed so I could use it.
Any advice?Last edited by JeenLeen; 2020-08-29 at 08:33 PM.
-
2020-08-29, 02:51 AM (ISO 8601)
- Join Date
- Sep 2016
Re: program to log into an SFTP site and load files
You could use Telnet (Putty, etc...) to go through the motions even more manually (At TCP level most of these interactions are like a conversation),
script here in part 7
And then recreate that in python using low level network libraries.
Or more boringly there's the FTPlib, which will help you do it in a much more structured way
https://docs.python.org/3/library/ftplib.html
Again example near the top.
-
2020-08-30, 09:40 PM (ISO 8601)
- Join Date
- Jan 2009
Re: program to log into an SFTP site and load files
At first I thought this is working, in that I was able to do the code below (URL, login, and password altered) and get no errors, so I guess I logged in successfully:
Code:from ftplib import FTP ftp=FTP(host='firstpart.sharefille.com', user='[email protected]', passwd='myPassword', timeout=None) ftp.nlist() ftp.dir() ftp.getwelcome
However, after a few minutes, this error message popped up
Code:create_connection raise err File "C:\REDACTED\Local\Continuum\anaconda3\lib\socket.py", line 716, in create_connection sock.connect(sa) TimeoutError: [WinError 10060] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
I tried again, dropping the nlist, dir, and getwelcome lines (in case those were causing the issue), but it seems to be doing the same (e.g., same error message).
Same result as well when trying this (which fits the example better than the above):
Code:from ftplib import FTP ftp=FTP('website.sharefile.com') ftp.login(user='[email protected]', passwd='myPassword?') ftp.retrlines('LIST')
EDIT/ ON SFTP vs FTP
On Googling Python SFTP, I found reference to pysftp, but I can't see
Some links
https://www.tutorialspoint.com/pytho...ython_sftp.htm
https://ourcodeworld.com/articles/re...sftp-in-python
But it seems I need to install it via something called PIP before I can use the import pysftp line*, and I don't know what that means. From some short googling, I'd guess that Pip is in Anacondra, but not sure where or how.
Any advice, or should the FTP function work?
Also found the paramiko package referred to here
https://stackoverflow.com/questions/...rm-independent
but when I try import paramiko, I get that the package isn't available.Last edited by JeenLeen; 2020-08-30 at 09:55 PM.
-
2020-08-31, 01:21 AM (ISO 8601)
- Join Date
- Jul 2004
- Location
- Freiburg, germany
- Gender
Re: program to log into an SFTP site and load files
First, I'd highly recommend you use the SAS-Library and just generate a Keypair for this use. It's dead simple if you ahve a cess to any linux box and not that much more complicated if all you have is a Windows machine, IIRC. Check if the SAS Library supports password-secured private keys beforehand. The god thing about this is:
- You don't hard code your credentials in a script that you have to update every time you change your password
- You don't have to change your password if for some reason you need to invalidate your script credentials
- An attacker needs access to a file instead of "just" running a keylogger for your password
- If the key can be password-secured you basically have 2-Factor-Authentication
Now, if you have access to a linux box (for example the server you want to SFTP onto - can you log in there with your credentials? If so, substitute the_target_machine_ip_or_name in the example below with localhost), you can generate the keypair like this:
Spoiler1. Generate a keypair:
Code:someuser@somebox ~ > ssh-keygen
Storage Location (defaults to ~/.ssh/id_rsa): ~/.ssh/my_sas_key
Leave everything else empty, except password IF your SAS-Library supports password protection for a private key. In that case, set a password.
2. Copy the pulic part to its destination:
Code:someuser@somebox ~ > ssh-copy-id -i ~/.ssh/my_sas_key.pub someuser@the_target_machine_ip_or_name
You'll be prompted for your password (twice if the key is password protected, once for the key password, once for your user password).
3. Move the private key to your script:
Code:cat ~/.ssh/my_sas_key
Code:rm ~/.ssh/my_sas_key
That's it, you're done. Follow the instructions for your SAS Library to use the key.
Now, that being said a few things from your 2nd post:
- pip is a package manager for python. It's what you use to install python packages. Assuming python3, you can substitute all calls to pip withCode:python3 -m pip
- paramiko as a library is a bit involved - I maintain a legacy codebase where paramiko is one of the main workhorses. If you have a different option, don't use it, IMHO.
- Don't know how good pysftp is, sorry.
And lastly: FTP and SFTP are completely different beasts. FTP, as in the File Tbransfer Protocol is a dead simple protocol that does not feature any Encryption. To bring it to modern standards, people bolted SSL/TLS onto it - That's called FTPS.
SFTP is plain FTP wrapped in the SSH protocol - kinda like scp, only supporting the FTP commands. It's what you want to use if you've already got OpenSSH on your box (most server linuxes have that) since OpenSSH is what provides SFTP, and that way you don't need another daemon running on the server.
Hope that clears some things up for you!
-
2020-09-01, 11:23 AM (ISO 8601)
- Join Date
- Jan 2009
Re: program to log into an SFTP site and load files
I got the Python packages installed and was starting to play around with them, but then I got a clarification that the secure site is not actually SFTP but is instead a FTP with Implicit SSL/TLS.
Which stinks, as SAS supports explicit with FILENAME FTP, but not implicit.
However, I was able to log in via WinSCP. And I've heard VBA can control WinSCP actions. And I have code that lets SAS write and then execute VBA code (or, rather, it writes a text file then uses system commands to tell Windows to run the text file as a VBA program.)
It's an annoying workaround, but it should work and get things automated.
But if anyone knows a more standard way for Python to connect with implicit SSL/TLS FTPs, let me know. I can run X/shell commands in SAS, so I think I can write a Python scrip then use an X command to tell Windows to run it.
-
2020-09-01, 01:21 PM (ISO 8601)
- Join Date
- Jul 2004
- Location
- Freiburg, germany
- Gender
Re: program to log into an SFTP site and load files
python3 with standard library, no extra package needed.
Quick and dirty, tested against my own FTP server which does FTPS but I can't remember if explicit or implicit:
Code:import ftplib hostname = "TARGET_HOST" username = "YOUR_USERNAME" password = "YOUR_PASSWORD" ftp = ftplib.FTP_TLS(hostname, username, password) ftp.prot_p() # list directory contents ftp.dir() # Do your stuff here. see https://docs.python.org/3/library/ftplib.html for methods
-
2020-09-01, 01:51 PM (ISO 8601)
- Join Date
- Jan 2009
Re: program to log into an SFTP site and load files
Thanks.
I think that doesn't support implicit, but I found some code that makes it account for implicit.
Below is what I've got going on now, and I'm sure I'm getting in since I can get some cwd to work and getwelcome is working. But I can't figure out the right code or command to upload a data file. I'm uploading CSVs and g-zip files.
Code with redacted personal info
Code:import ftplib import ssl class ImplicitFTP_TLS(ftplib.FTP_TLS): """FTP_TLS subclass that automatically wraps sockets in SSL to support implicit FTPS.""" def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) self._sock = None @property def sock(self): """Return the socket.""" return self._sock @sock.setter def sock(self, value): """When modifying the socket, ensure that it is ssl wrapped.""" if value is not None and not isinstance(value, ssl.SSLSocket): value = self.context.wrap_socket(value) self._sock = value ftp_client = ImplicitFTP_TLS() ftp_client.connect(host='theirname.sharefileftp.com', port=990) ftp_client.login(user='myUserID', passwd='myPassword') ftp_client.prot_p() ftp_client.cwd('Project Platform') print(ftp_client.pwd()) filepath="\\\server\\office\\ProjectName\\deliverable\\" filename=filepath+'2021File_.csv'
But how do I tell it actually upload the file with the location & name of filename to the SFTP site?
I tried a few things, but none work. I'm having trouble if I'm using a bad code, or if my permissions are somehow different via Python than in WinSCP login.
This doesn't work:
Code:file=open(filename,'rb') ftp_client.storbinary('testing.csv',file) file.close()
But I'm sure I got the filename right, since this does work
Code:import csv with open(filename, newline='') as csvfile: mydata=csv.reader(csvfile, delimiter=',') for row in mydata: print(row)
Permissions question
I can see the subfolders I'm trying to cwd to in WinSCP, but when I try in Python, I'm getting this error
Code:error_perm: 550 Could not change to 'Project Platform\MyState'
EDIT: upon more analysis, it seems the Python issue might be I can't upload files to the base folder, but I can't figure out how to cwd to the lower-level folder I need to be at.
How do you change directory to something three folders deep? E.g., something like 'Project Platform\Mystate\Contacts'Last edited by JeenLeen; 2020-09-01 at 01:59 PM.
-
2020-09-01, 03:05 PM (ISO 8601)
- Join Date
- Jul 2004
- Location
- Freiburg, germany
- Gender
Re: program to log into an SFTP site and load files
On phone, so rather short: I assume the various typos in your linked/posted code are not there on your actual machine. That said: you have spaces in the folder names. Guessing here, but try to escape them. A SPACE becomes \SPACE.
Edit: Nevermind the typos, form together with my phone acted up. Still, try escaping the SPACEs.Last edited by Whoracle; 2020-09-01 at 03:55 PM.
-
2020-09-02, 08:19 AM (ISO 8601)
- Join Date
- Jan 2009
Re: program to log into an SFTP site and load files
I was able now to navigate down to the folder I need to transmit the data to.
Instead of using one CWD line with subfolders, I went folder by folder. E.g., one line of code to go 1 folder deep, then another to go another deep, and so on. Tedious, but nothing hard and it's just 3 folders whose names won't change. So once I code it, no big.
However, I can't find the right ftplib code to upload a file.
Any help on what command for ftplib function does that?
---
I didn't test the spacing thing, but am starting to now. Will edit in results, but wanted to go ahead and ask what the command/function to upload a file is. (I do see examples in documentation and Googling, but trying to follow them isn't helping.)
And, also, thanks for your help.Last edited by JeenLeen; 2020-09-02 at 08:28 AM.
-
2020-09-02, 10:20 AM (ISO 8601)
- Join Date
- Jul 2004
- Location
- Freiburg, germany
- Gender
Re: program to log into an SFTP site and load files
Most commands that your FTPlib object implements are straight copies of the relevant FTP protocol commands. So for upload, it should be
ftpclient.put(filename)
Untested, you might need to pass the whole path, but that should be it.
-
2020-09-02, 12:38 PM (ISO 8601)
- Join Date
- Jan 2009
Re: program to log into an SFTP site and load files
It finally worked!!!
Code:with open(filename, 'rb') as fp: ftp_client.storlines('STOR myfile.txt', fp)
Now just to try to figure out how to call it from SAS, if possible.
I heard it should work via
X python <file path here>/myprogram.py
but that isn't seeming to do anything.
EDIT
Anyone know if there's a good reason .storlines works with text or CSV, but not a gzip file? Should another command be used to upload a ziipped file?
I was able to load a gzip file, but when the company tried to open it they got a message that it was messed up as “invalid or incomplete compressed data". When I upload it manually instead of via Python, it worked fine.
EDIT ANSWER
Seems like zipped stuff needs storbinary, not storlines.Last edited by JeenLeen; 2020-09-05 at 01:13 PM.