How to download a .xls into a Python object via FTP with Python ftplib

Question

I am new to Python, so forgive me if I am asking a wrong question. I have a .xls that I would like to ftp and save it as a Python object. Currently, I am taking an awkward approach where [Step 1] I first ftp the file and save it as .txt (I know .xls->.txt is a bad idea!), and then [Step 2] open and save that .txt file in Python shell. That is,

Step 1:

## Import the necessary module
from ftplib import FTP

ftp = FTP('webftp.vancouver.ca')
ftp.login()
ftp.cwd('OpenData/xls')
filename = 'new_food_vendor_locations.xls'
ftp.retrbinary('RETR %s' % filename, open('myLovelyNewFile.txt', 'w').write)
ftp.quit()

This allows me to create a new file 'myLovelyNewFile.txt' in my current directly. In my Python shell, I have:

Step 2:

myLovelyPython = open('myLovelyNewFile.txt','r')
for line in myLovelyPython.readlines():
     print line
myLovelyPython.close()

Although Python can run all these command lines, the printed lines do not have correct inputs due to the forceful conversion from xls to txt. Furthermore, I would like to save the ftp-ed object directly as some python object (rather than first saving it as .txt outside Python). Is there easy solution to this problem?

Any reason why you don't want to download the .xls as .xls and save it as .xls? You can then load the xls using one of the solutions here: http://stackoverflow.com/questions/2942889/reading-parsing-excel-xls-files-with-python — jcoppens, Jun 04 '15 at 00:36
@jcoppens Like you say, I tried the following commands: ftp = FTP('webftp.vancouver.ca'); ftp.login(); ftp.cwd('OpenData/xls'); filename = 'new_food_vendor_locations.xls'; workbook = xlrd.open_workbook(filename); ftp.quit(); However this gives me an error statement that "[Errno 2] No such file or directory: 'new_food_vendor_locations.xls'" — FairyOnIce, Jun 04 '15 at 00:39

score 0 · Accepted Answer · answered Jun 04 '15 at 00:51

Although I was looking for a way to directly purse the ftp-ed excel file into Python object, if I allows saving the excel file in my directory and re-read it again, the following works:

ftp = FTP('webftp.vancouver.ca')
ftp.login()
ftp.cwd('OpenData/xls')
filename = 'new_food_vendor_locations.xls'
ftp.retrbinary('RETR %s' % filename, open('myLovelyNewFile.xls', 'w').write)
ftp.quit()
workbook = xlrd.open_workbook('myLovelyNewFile.xls')
worksheet = workbook.sheet_by_name('Query_vendor_food')
num_rows = worksheet.nrows - 1
curr_row = -1
while curr_row < num_rows:
    curr_row += 1
    row = worksheet.row(curr_row)
    print row

(I thank @jacoppens for his advice.)

How to download a .xls into a Python object via FTP with Python ftplib

1 Answers1