New OOTS products from CafePress
New OOTS t-shirts, ornaments, mugs, bags, and more
Results 1 to 11 of 11
  1. - Top - End - #1
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default 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.

  2. - Top - End - #2
    Barbarian in the Playground
     
    PaladinGuy

    Join Date
    Sep 2016

    Default 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.

  3. - Top - End - #3
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: program to log into an SFTP site and load files

    Quote Originally Posted by jayem View Post
    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.
    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
    But the only thing written to the log is a file on my C drive it says is running.

    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
    So I assume it wasn't actually working and was just taking a while to get the 'did not connect' message.

    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')
    Could the issue be that I'm doing SFTP, not FTP? I know SAS at least treats those very differently, so maybe Python does as well? Or is it being a Citrix Sharefile SFTP, as opposed to something named SFTP.blah.blah?



    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.

  4. - Top - End - #4
    Bugbear in the Playground
     
    Whoracle's Avatar

    Join Date
    Jul 2004
    Location
    Freiburg, germany
    Gender
    Male

    Default 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:

    Spoiler
    Show
    1. Generate a keypair:
    Code:
    someuser@somebox ~ > ssh-keygen
    Answer the questions that come up like this:
    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
    Note the extension .pub here!
    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
    Copy the complete output of the above command onto your computer in a new file called my_sas_key. Make sure you saved the file successfully and then delete the one on the server:
    Code:
    rm ~/.ssh/my_sas_key
    !!! The private key is NOT recoverable. You'll have to start the whole process over if you delete it without having it somewhere !!!

    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 with
    Code:
    python3 -m pip
    Does the same thing basically.
    - 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!

  5. - Top - End - #5
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default 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.

  6. - Top - End - #6
    Bugbear in the Playground
     
    Whoracle's Avatar

    Join Date
    Jul 2004
    Location
    Freiburg, germany
    Gender
    Male

    Default 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

  7. - Top - End - #7
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default 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'
    I think the above is working. If I do print(filename), it seems that the // are resolving correctly.

    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()
    The error it gets is error_perm: 500 Unrecognized command 'TESTING.CSV'.

    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'
    Any idea what's up? I tried Project Platform//MyState and Project Platform\\MyState, as well as the above, to see if the escape character was part of it.

    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.

  8. - Top - End - #8
    Bugbear in the Playground
     
    Whoracle's Avatar

    Join Date
    Jul 2004
    Location
    Freiburg, germany
    Gender
    Male

    Default 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.

  9. - Top - End - #9
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default 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.

  10. - Top - End - #10
    Bugbear in the Playground
     
    Whoracle's Avatar

    Join Date
    Jul 2004
    Location
    Freiburg, germany
    Gender
    Male

    Default 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.

  11. - Top - End - #11
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default 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)
    Saves whatever file I have as filename (full path there) as myfile.txt on the secure site.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •