CODEWITHSUNDEEP
pythonparamiko
banana_99
banana_99

Reputation: 641

Upload excel file to sftp server

I have the below code, which uploads a df in csv format into a sftp server.

with sftp.open(mypath + timestr + '.csv', "w") as f:
    f.write(df_pdcs_ec.to_csv(index=False))

However: is there a way to upload that same df but in excel format?

Update: I'm trying to generate the file with 2 tabs as per @furas's answer, but only getting one.

with sftp.open(mypath + timestr + '.xlsx', "wb") as f:
    df_pdcs_ec.to_excel(f, sheet_name = 'Test1', index=False)
    df_pdcs_ec.to_excel(f, sheet_name = 'Test2', index=False)

Upvotes: 1

Views: 1055

Answers (1)

furas
furas

Reputation: 142681

I can't test it but in Pandas all functions should work with file handlers or file-like objects which have function write() so you can open file or connection and use to_csv(file_handler) to_excel(file_handler), etc.

But for excel you have to write it in bytes mode - wb

with sftp.open(mypath + timestr + '.csv', "wb") as f:
    df_pdcs_ec.to_csv(f, index=False)

with sftp.open(mypath + timestr + '.xlsx', "wb") as f:
    df_pdcs_ec.to_excel(f, index=False)

with sftp.open(mypath + timestr + '.html', "wb") as f:
    df_pdcs_ec.to_html(f, index=False)

EDIT:

To save many tabs you have to use ExcelWriter (see doc: to_excel)

I can't test it with sftp.open() but this should work. At least it works with standard open().

with sftp.open(mypath + timestr + '.xlsx', "wb") as f:
    with pd.ExcelWriter(f) as writer:
        df_pdcs_ec.to_excel(writer, sheet_name='Test1', index=False)
        df_pdcs_ec.to_excel(writer, sheet_name='Test2', index=False)

Upvotes: 2

Related Questions