Python FTP: Read .xlsx as pandas dataframe from FTP without writting .xlsx to disk

Question

I want to read .xlsx file as pandas dataframe from an FTP connection, However I want to do this on memory without writing the .xlsx to my local disk.

Here is my current code:

filename = 'my_excel.xlsx'
localfile = open(filename, 'wb')
ftp.retrbinary('RETR ' + filename, localfile.write, 1024)
ftp.quit()
localfile.close()

After this section I can read .xlsx from my local disk as pandas dataframe however I want to do handle this in buffer memory.

How can I do this. Thanks for help=)

Answer 1

You can use an io.BytesIO object to write the data stream into, then read it into pandas.

import pandas as pd
from io import BytesIO

excel_fp = BytesIO()
filename = 'my_excel.xlsx'
ftp.retrbinary('RETR ' + filename, excel_fp.write, 1024)

# reset the cursor to the start of the file stream
excel_fp.seek(0)
df = pd.read_excel(excel_fp)