5

How to create a .csv or .xls file on ftp directly from Pandas dataframe as I do not want to create one on local machine and then push it on ftp. Many thanks in advance :)

trader
  • 65
  • 1
  • 6

1 Answers1

10

As the docs say:

Store a file in binary transfer mode. cmd should be an appropriate STOR command: "STOR filename". file is a file object (opened in binary mode) which is read until EOF using its read() method in blocks of size blocksize to provide the data to be stored…

So, you need to give it a file-like object with an appropriate read method.

A string is not a file-like object, but an io.BytesIO is. So:

from ftplib import *
from StringIO import StringIO
import pandas
import io

ftp = FTP('ftp.mysite.com')
ftp.login('un', 'pw')
ftp.cwd('/')
buffer = StringIO.StringIO()
your_pandas_df.to_csv(buffer)
text = buffer.getvalue()
bio = io.BytesIO(str.encode(text))

ftp.storbinary('STOR filename.csv', bio)

for reference you can refer Python write create file directly in FTP

Anurag Misra
  • 1,516
  • 18
  • 24
  • Hi Anurag, thanks for very quick answer. I can create the file with this line:session.storbinary('STOR myfile', StringIO.StringIO(ResultsToFtp.to_csv('testfile'))) but don't know why the file is always empty. I have checked the dataframe ResultsToFtp has data inside but for some reason file is empty with None on cell A1 – trader Sep 06 '17 at 12:21
  • first try to write file into local system (if possible). I think this will give clear picture is there some problem in `FTP` or producing file like object (i.e `StringIO` object) – Anurag Misra Sep 06 '17 at 12:28
  • I did it locally from the same dataframe and works fine – trader Sep 06 '17 at 12:30
  • use `ftp.storbinary('STOR myfile', StringIO.StringIO(your_pandas_df))` because [python stringIO documentation](https://docs.python.org/2/library/stringio.html) as it need memory object not a file. and `dataframe.to_csv` does return anything – Anurag Misra Sep 06 '17 at 12:41
  • can I use something to reformat it in more organized way because the csv or xls file looks like string of dataframe in one col in excel – trader Sep 06 '17 at 12:57
  • AttributeError: 'str' object has no attribute 'read' pops up this error – trader Sep 06 '17 at 13:51
  • 1
    buffer = StringIO.StringIO() Hi, this is working perfectly now :) ResultsToFtp.to_csv(buffer) text = buffer.getvalue() bio = io.BytesIO(str.encode(text)) session.storbinary('STOR test'+xxx+'.csv', bio) – trader Sep 11 '17 at 08:33
  • For someone like me, In python 3, you need to import io, StringIO is gone for custom file name, 'STOR ' + your_file_name – Abdullah Raihan Bhuiyan Jul 14 '20 at 15:45