python xlrd unsupported format or corrupt file

Question

My code    import xlrd wb   xlrd open workbook  Z   Data  Locates  3 8 locates xls   sh   wb sheet by index 0  print sh cell 0 0  value   The error    Traceback  most recent call last   File  Z  Wilson tradedStockStatus py   line 18  in  lt module gt  wb   xlrd open workbook  Z   Data  Locates  3 8 locates xls   File  C  Python27 lib site-packages xlrd   init   py   line 429  in open workbook biff version   bk getbof XL WORKBOOK GLOBALS  File  C  Python27 lib site-packages xlrd   init   py   line 1545  in getbof bof error  Expected BOF record  found  r    self mem savpos savpos 8   File  C  Python27 lib site-packages xlrd   init   py   line 1539  in bof error raise XLRDError  Unsupported format  or corrupt file      msg  xlrd biffh XLRDError  Unsupported format  or corrupt file  Expected BOF record  found   lt table r     The file doesn t seem to be corrupted or of a different format   Anything to help find the source of the issue would be great

User · Accepted Answer

You say       The file doesn t seem to be corrupted or of a different format    However as the error message says  the first 8 bytes of the file are   lt table r      that is definitely not Excel  xls format  Open it with a text editor  e g  Notepad  that won t take any notice of the  incorrect   xls extension and see for yourself

User · Answer

there s nothing wrong with your file  xlrd does not yet support xlsx  excel 2007   files although it s purported to have supported this for some time   Simplistix github  2-days ago they committed a pre-alpha version to their git which integrates xlsx support  Other forums suggest that you use a DOM parser for xlsx files since the xlsx file type is just a zip archive containing XML  I have not tried this  there is another package with similar functionality as xlrd and this is called openpyxl which you can get from easy install or pip  I have not tried this either  however  its API is supposed to be similar to xlrd

User · Answer

I had faced the same xlrd biffh XLRDError  Unsupported format  or corrupt file  Expected BOF record  error and solved it by writing an XML to XLSX converter  The reason is that actually  xlrd does not support XML Spreadsheet    xml  i e  NOT in XLS or XLSX format   import pandas as pd from bs4 import BeautifulSoup  def convert to xlsx        with open  sample xls   as xml file          soup   BeautifulSoup xml file read     xml           writer   pd ExcelWriter  sample xlsx           for sheet in soup findAll  Worksheet                sheet as list                  for row in sheet findAll  Row                    sheet as list append  cell Data text if cell Data else    for cell in row findAll  Cell                 pd DataFrame sheet as list  to excel writer  sheet name sheet attrs  ss Name    index False  header False           writer save

User · Answer

Open in google sheets and then download from sheets as CSV and then reupload to drive  Then you can Open CSV file from python

User · Answer

I met this problem too I opened this file by excel and saved it as other formats such as excel 97-2003 and finally I solved this problem

User · Answer

I had the same issue  Those old files are formatted like a tab-delimited file  I ve been able to open my problem files with read table  ie df   pd read table  trouble maker xls

User · Answer

I found the similar problem when downloading  xls file and opened it using xlrd library  Then I tried out the solution of converting  xls into  xlsx as detailed here  how to convert xls to xlsx  It works like a charm and rather than opening  xls  I am working with  xlsx file now using openpyxl library   Hope it helps to solve your issue

User · Answer

Worked on the same issue   finally done this is top for the question so just putting what i did   Observation -  1 -The file was not actually XLS i renamed to txt and noticed HTML text in file   2 - Renamed the file to html and tried reading pd read html  Failed   3- Added  as it was not there in txt file  removed style to ensure that table is displaying in browser from local  and WORKED   Below is the code may help someone     import pandas as pd import os import shutil import html5lib import requests from bs4 import BeautifulSoup import re import time  shutil copy  your xls   file html   shutil copy  file html   file txt   time sleep 2   txt   open  file txt   r   read      Modify the text to ensure the data display in html page  delete style  txt   str txt  replace   lt style gt   text   mso-number-format        lt  script gt          Add head and body if it is not there in HTML text  txt with head     lt html gt  lt head gt  lt  head gt  lt body gt   txt   lt  body gt  lt  html gt      Save the file as HTML  html file   open  output html   w   html file write txt with head     Use beautiful soup to read  url   r C  Users hitesh kumar PycharmProjects OEM ML output html  page   open url  soup   BeautifulSoup page read    features  lxml   my table   soup find  table  attrs   border    1     frame   pd read html str my table   0  print frame head    frame to excel  testoutput xlsx  sheet name  sheet1   index False

User · Answer

This will happen to some files while also open in Excel

User · Answer

I know there should be a proper way to solve it but just to save time  I uploaded my xlsx sheet to Google Sheets and then again downloaded it from Google Sheets it working now  If you don t have time to solve the problem  you can try this

User · Answer

I meet the same problem   it lies in the  xls file itself - it looks like an Excel file however it isn t   see if there s a pop up when you plainly open the  xls from Excel   sjmachin commented on Jan 19  2013 from https   github com python-excel xlrd issues 26 helps

User · Answer

Sometimes help to add  raw true at the end of a file path  For example  wb   xlrd open workbook  quot Z   Data  Locates  3 8 locates xls raw true quot

User · Answer

In my case  after opening the file with a text editor as  john-machin suggested  I realized the file is not encrypted as an Excel file is supposed to but it s in the CSV format and was saved as an Excel file  What I did was renamed the file and its extension and used read csv function instead    os rename  sample file xls    sample file csv   csv   pd read csv  sample file csv   error bad lines False

User · Answer

I had a similar problem and it was related to the version  In a python terminal check    gt  gt  import xlrd  gt  gt  xlrd   VERSION     If you have  0 9 0  you can open almost all files  If you have  0 6 0  which was what I found on Ubuntu  you may have problems with newest Excel files  You can download the latest version of xlrd using the Distutils standard

User · Answer

Try to open it with pandas   import pandas as pd data   pd read html  filename xls     Or try any other html python parser   That s not a proper excel file  but an html readable with excel

User · Answer

I just downloaded xlrd  created an excel document  excel 2007  for testing and got the same error  message says  found PK x03 x04 x14 x00 x06 x00    Extension is a xlsx  Tried saving it to an older  xls format and error disappears

[python] python xlrd unsupported format, or corrupt file.

Examples related to python

Examples related to excel

Examples related to xlrd