pandasxls

How to use Pandas to read the old_Excel xls format?


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


Solution

  • 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')