Download Binary File from SFTP in Notebook

Thank you! That link gave me the clue I needed. I'm downloaded data from SFTP using:

## Main SFTP Function

def download_files_from_sftp(hostname, port, username, password, local_directory, remote_directory):
    ## Initialize the SSH client
    ssh_client = paramiko.SSHClient()
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    
    try:
        ## Connect to the server
        ssh_client.connect(hostname, port=port, username=username, password=password)

        ## Initialize SFTP client
        with ssh_client.open_sftp() as sftp:
            ## List files in the specified directory
            file_list = sftp.listdir(f"{remote_directory}")
            for file in file_list:
                remote_filepath = f"{remote_directory}/{file}"
                local_filepath = os.path.join(local_directory, file)

                ## Check if it is a file and not a directory
                if sftp.stat(remote_filepath).st_mode & 0o170000 == 0o100000: # 0o100000 is the mask for a regular file
                    ## Download the file
                    ## Reference lakehouse as local storage using: "/lakehouse/default/Files/FolderName/filename.csv"
                    sftp.get(remote_filepath, local_filepath)
                    print(f"Downloaded {file}")

    except Exception as e:
        print(f"Error: {e}")

    finally:
        ## Close the SSH client
        if ssh_client: ssh_client.close()

The missing part was correctly referencing the attached default lakehouse as if it was local storage.

By using a path in the format: "/lakehouse/default/Files/FolderName/filename.csv" the problems I was having went away!

Obviously if you're using the code above make sure you're storing the passwords etc as secrets in Azure Key Vault and access them using

sftp_password = mssparkutils.credentials.getSecret("https://keyvaultname.vault.azure.net/", "Secret-Name")