How I can use Pandas to import an old format XLS file (Not XLSX)?
I downloaded an XLS file from United Nation Exchange Rate (https://treasury.un.org/operationalrates/OpRatesExport.php)
Even trying different methods, I cannot get Pandas to import the file. I had tried these: import pandas as pd import xlrd df = pd.read_excel('D:\000_DOWNLOAD\downloaded.xls', skiprows=1) df error code: Excel file format cannot be determined, you must specify an engine manually.
It has a different error code when I added 'engine='xlrd': df = pd.read_excel('D:\000_DOWNLOAD\downloaded.xls', skiprows=1,engine='xlrd') df XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'\r\nAfghan'
I have the same XLRDError code even when changing skiprows=1 to skiprows=[0,1,2]
I would be grateful for any help! Thanks
The download file from that site appears to be a .xml
file (not .xls
). What combination of options are you selecting to get .xls
?
I was able to download a file that could be read in via pandas.read_xml
.
Here is the code that worked for me
import pandas as pd
df = pd.read_xml('downloaded.xml')
# then if you want to save it in a different format:
df.to_csv('downloaded.csv')