1

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)
banana_99
  • 591
  • 5
  • 15

1 Answers1

2

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)
furas
  • 134,197
  • 12
  • 106
  • 148
  • 2
    +1 Though you will want to set also `bufsize` parameter. See [Writing to a file on SFTP server opened using pysftp “open” method is slow](https://stackoverflow.com/q/58111798/850848) (it's about pysftp, but that's just a wrapper around Paramiko, which has the same problem). – Martin Prikryl Jun 25 '21 at 13:41
  • If I try that, my format breaks to PK?a]I:O[Content_Types].xml­”ËnÂ0E÷ýŠÈÛ*1tQUEË©ô\{B,Ûòþ¾“ðP[Q ‚M¬dîÜsÇŽ<-—- ¡ ¾ý¢'2ð:맥xŸ<çw"CRÞ(<”b(FëÁd3nöXŠš(ÞK‰º†Fa"x®T!5Šø5MeTz¦¦ oz½[©ƒ'ð”Së!†ƒG¨ÔÜQö´äÏë How can I fix this? – banana_99 Jun 25 '21 at 13:53
  • 2
    how did you open it ? Excel is not text file but compressed file and you can't open it in editor or display like `.csv`. Maybe it needs extension `.xlsx` because as I remeber `.xlsx` is `xml` compressed by `zip` and in your text I see `.xml` and `PK` which can means `zip` compression. – furas Jun 25 '21 at 13:55
  • @MartinPrikryl thanks for the input! What's the default for that bufsize parameter? – banana_99 Jun 25 '21 at 13:56
  • @furas that was it, I needed to save it in .xlsx. Last issue I'm facing: please see my update. – banana_99 Jun 25 '21 at 13:59
  • 1
    I'm not sure but second tab may need to open in `append mode` – furas Jun 25 '21 at 14:02
  • 1
    documentation for [to_excel](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html) shows `If you wish to write to more than one sheet in the workbook, it is necessary to specify an ExcelWriter object:`. – furas Jun 25 '21 at 14:05
  • @furas so, there is no option to upload a multiple tab excel to a sftp server? – banana_99 Jun 25 '21 at 14:07
  • 1
    I can't test it but maybe you will need `with sftp.open(...) as f: with pd.ExcelWriter(f) as writer: df.to_excel(writer, ...)`. OR you should write in `io.BytesIO` and send this `io.BytesIO` – furas Jun 25 '21 at 14:08
  • That was exactly was I was hoping for. Thanks a lot! – banana_99 Jun 25 '21 at 14:19