Python: How to handle excel data from web without saving file

Question

I'm new to python and having trouble dealing with excel manpulation in python.

So here's my situation: I'm using requests to get a .xls file from a web server. After that I'm using xlrd to save the content in excel file. I'm only interested in one value of that file, and there are thousands of files im retrieving from different url addresses.

I want to know how could i handle the contents i get from request in some other way rather than creating a new file.

Besides, i've included my code my comments on how could I improve it. Besides, it doesn't work, since i'm trying to save new content in an already created excel file (but i couldnt figure out how to delete the contents of that file for my code to work (even if its not efficient)).

import requests
import xlrd
d={}
for year in string_of_years:
    for month in string_of_months:  
        dls=" http://.../name_year_month.xls"
        resp = requests.get(dls)
        output = open('temp.xls', 'wb')
        output.write(resp.content)
        output.close()
        workbook = xlrd.open_workbook('temp.xls')
        worksheet = workbook.sheet_by_name(mysheet_name)
        num_rows = worksheet.nrows
        for k in range(num_rows):
            if condition I'm looking for:
                w={key_year_month:worksheet.cell_value(k,0)}
                dic.update(w)
                break

Robᵩ · Accepted Answer · 2015-06-16 21:13:46Z

5

xlrd.open_workbook can accept a string for the file data instead of the file name. Your code could pass the contents of the XLS, rather than creating a file and passing its name.

Try this:

    # UNTESTED
    resp = requests.get(dls)
    workbook = xlrd.open_workbook(file_contents=resp.content)

Reference: xlrd.open_workbook documentation

answered Jun 16, 2015 at 21:13

Robᵩ

170k20 gold badges251 silver badges323 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hugovdberg Over a year ago

pd.read_excel(xlrd.open_workbook(file_contents=resp.content), engine='xlrd') can also be used to directly transform it into a pandas DataFrame.

Thuruv · Accepted Answer · 2015-06-16 21:14:53Z

-1

Save it and then delete the file readily on each loop after the work with os.

import os
#Your Stuff here
os.remove(#path to temp_file)

answered Jun 16, 2015 at 21:14

Thuruv

789 bronze badges

1 Comment

hugovdberg Over a year ago

The question states 'without saving file', and also why not use the tempfile module to create a temporary file if you are going to save it? That module can even help with cleaning up after the file was used.

Collectives™ on Stack Overflow

Python: How to handle excel data from web without saving file

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related