s666 s666 - 6 months ago 149
Python Question

using Pandas to read in excel file from URL - XLRDError

I am trying to read in excel files to Pandas from the following URLs:

url1 = 'https://cib.societegenerale.com/fileadmin/indices_feeds/CTA_Historical.xls'

url2 = 'https://cib.societegenerale.com/fileadmin/indices_feeds/STTI_Historical.xls'


using the code:

pd.read_excel(url1)


However it doesn't work and I get the error:

XLRDError: Unsupported format, or corrupt file: Expected BOF record; found '2000/01/'


After searching on Google it seems that sometimes .xls files offered through URLs are actually held in a different file format behind the scenes such as html or xml.

When I manually download the excel file and open it using Excel I get presented with an error message: The file format and extension don't match. The file could be corrupted or unsafe. Unless you trust it's source don't open it"

When I do open it, it appears just like a normal excel file.

I came across a post online that suggested I open the file in a text editor to see if there is any additional info held as to proper file format but I don't see any additional info when opened using notepad++.

Could someone please help me get this "xls" file read into a pandas DataFramj properly please?

Answer

It seems you can use read_csv:

import pandas as pd

df = pd.read_csv('https://cib.societegenerale.com/fileadmin/indices_feeds/CTA_Historical.xls',
                 sep='\t',
                 parse_dates=[0],
                 names=['a','b','c','d','e','f'])
print df

Then I check last column f if there are some other values as NaN:

print df[df.f.notnull()]

Empty DataFrame
Columns: [a, b, c, d, e, f]
Index: []

So there are only NaN, so you can filter last column f by parameter usecols:

import pandas as pd

df = pd.read_csv('https://cib.societegenerale.com/fileadmin/indices_feeds/CTA_Historical.xls',
                 sep='\t',
                 parse_dates=[0],
                 names=['a','b','c','d','e','f'],
                 usecols=['a','b','c','d','e'])
print df
Comments