Using Pandas to read excel from url

Question

I am working on a personal project to analyze COVID19 data. Presently, I am download the excel sheet provided by ourworldindata.org, available at this url -> https://github.com/owid/covid-19-data/blob/master/public/data/owid-covid-data.xlsx

However, when i try to execute the command in pandas (below), I get a list of errors. What could be the root cause ?

url = 'https://github.com/owid/covid-19-data/blob/master/public/data/owid-covid-data.xlsx'
df = pd.read_excel(url, sheet_name='Sheet1')

Error

    Traceback (most recent call last):   File "<input>", line 1, in <module>   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\pandas\io\excel\_base.py", line 304, in read_excel
io = ExcelFile(io, engine=engine)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\pandas\io\excel\_base.py", line 824, in __init__
self._reader = self._engines[engine](self._io)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\pandas\io\excel\_xlrd.py", line 21, in __init__
super().__init__(filepath_or_buffer)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\pandas\io\excel\_base.py", line 351, in __init__
self.book = self.load_workbook(filepath_or_buffer)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\pandas\io\excel\_xlrd.py", line 34, in load_workbook
return open_workbook(file_contents=data)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\xlrd\__init__.py", line 157, in open_workbook
ragged_rows=ragged_rows,   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\xlrd\book.py", line 92, in open_workbook_xls
biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\xlrd\book.py", line 1278, in getbof
bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])   File "C:\Users\masoom.kumar\PycharmProjects\ReadingINCA_Data\venv\lib\site-packages\xlrd\book.py", line 1272, in bof_error
raise XLRDError('Unsupported format, or corrupt file: ' + msg) xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'\n\n\n\n\n<!D'

Please not that pandas can read the excel if I download it on my computer

you have to do it with requests.get().content

Ahmed Sunny
– Ahmed Sunny

2020-06-04 12:30:12 +00:00
Commented Jun 4, 2020 at 12:30 — Ahmed Sunny
– Ahmed Sunny, Commented Jun 4, 2020 at 12:30
download raw file , url=url.replace('blob','raw')

Shijith
– Shijith

2020-06-04 12:43:12 +00:00
Commented Jun 4, 2020 at 12:43 — Shijith
– Shijith, Commented Jun 4, 2020 at 12:43

gbruenjes · Accepted Answer · 2020-06-04 12:30:41Z

4

Try the link to raw excel file:

import pandas as pd
url='https://github.com/owid/covid-19-data/blob/master/public/data/owid-covid-data.xlsx?raw=true'
df=pd.read_excel(url, sheet_name='Sheet1')

answered Jun 4, 2020 at 12:30

gbruenjes

4,2251 gold badge18 silver badges32 bronze badges

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

Comments

Ahmed Sunny · Accepted Answer · 2020-06-04 12:31:35Z

1

You can do it with requests

import pandas as pd
import io
import requests

url = 'https://github.com/owid/covid-19-data/blob/master/public/data/owid-covid-data.xlsx'

get_content = requests.get(url).content

df = pd.read_csv(io.StringIO(get_content .decode('utf-8')))

I do this to avoid using local drive or google drive , and saves time of connection.

answered Jun 4, 2020 at 12:31

Ahmed Sunny

2,2431 gold badge24 silver badges31 bronze badges

1 Comment

Masoom Kumar Over a year ago

Thanks for the response. This presented some errors, however the method suggested by @luigigi worked fine

Collectives™ on Stack Overflow

Using Pandas to read excel from url

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related