XLRD：不支持的格式或损坏的文件：预期的 BOF 记录;找到 b'\x00\x00\x00\x01Bud1'

Question

我正在尝试合并一些excel文件，结果出现以下错误。

注意：这段代码在windows中运行良好，我正试图在mac上运行同样的代码，但我收到了这个错误。

代码：

import pandas as pd
import os

all_data=pd.DataFrame() <br/>
temp1=pd.DataFrame()<br/>
temp2=pd.DataFrame()
FL=os.listdir("Files/")
for i in FL:<br/>
temp1=pd.read_excel("Files/"+i)<br/>
print("Reading file "+ i)<br/>
temp1.loc[:, "Sub Query"] = i[0:i.find(".")]<br/>
print("working on file "+ i)<br/>
temp2 = pd.concat([temp2,temp1], sort=False)<br/>
print("file "+ i +" is over, moving on...")<br/>
writer = pd.ExcelWriter("output/Mastersheet.xlsx", engine='xlsxwriter', options={'strings_to_urls': False})<br/>
temp2.to_excel(writer, index=False)<br/>
writer.close()<br/>

**Error:**
File "<ipython-input-9-1e7da69236eb>", line 1, in <module>
runfile('/Users/simplify360/Desktop/TVS/Consolidate/consolidate.py', wdir='/Users/simplify360/Desktop/TVS/Consolidate')
File "/Applications/anaconda3/lib/python3.7/site-packages/spyder_kernels/customize/spydercustomize.py", line 827, in runfile
execfile(filename, namespace)
File "/Applications/anaconda3/lib/python3.7/site-packages/spyder_kernels/customize/spydercustomize.py", line 110, in execfile
exec(compile(f.read(), filename, 'exec'), namespace)
File "/Users/simplify360/Desktop/TVS/Consolidate/consolidate.py", line 19, in <module>
temp1=pd.read_excel("Files/"+i)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/util/_decorators.py", line 208, in wrapper
return func(*args, **kwargs)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 310, in read_excel
io = ExcelFile(io, engine=engine)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 819, in __init__
self._reader = self._engines[engine](self._io)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py", line 21, in __init__
super().__init__(filepath_or_buffer)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 359, in __init__
self.book = self.load_workbook(filepath_or_buffer)
File "/Applications/anaconda3/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py", line 36, in load_workbook
return open_workbook(filepath_or_buffer)
File "/Applications/anaconda3/lib/python3.7/site-packages/xlrd/__init__.py", line 157, in open_workbook
ragged_rows=ragged_rows,
File "/Applications/anaconda3/lib/python3.7/site-packages/xlrd/book.py", line 92, in open_workbook_xls
biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
File "/Applications/anaconda3/lib/python3.7/site-packages/xlrd/book.py", line 1278, in getbof
bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
File "/Applications/anaconda3/lib/python3.7/site-packages/xlrd/book.py", line 1272, in bof_error
raise XLRDError('Unsupported format, or corrupt file: ' + msg)
XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'x00x00x00x01Bud1'

我尝试过不同的方法，比如从excel文件中删除一些字段，重命名excel文件，但仍然不起作用。请帮忙！谢谢

Answer 1

我也遇到过同样的问题，我刚刚解决了它。问题是Mac上的每个目录中都有一个名为".DS_Store"的隐藏文件。尝试更改此代码块：

for i in FL:<br/>
temp1=pd.read_excel("Files/"+i)<br/>
print("Reading file "+ i)<br/>
temp1.loc[:, "Sub Query"] = i[0:i.find(".")]<br/>
print("working on file "+ i)<br/>
temp2 = pd.concat([temp2,temp1], sort=False)<br/>
print("file "+ i +" is over, moving on...")<br/>

对此：

for i in FL:<br/>
if(i != '.DS_Store'):
temp1=pd.read_excel("Files/"+i)<br/>
print("Reading file "+ i)<br/>
temp1.loc[:, "Sub Query"] = i[0:i.find(".")]<br/>
print("working on file "+ i)<br/>
temp2 = pd.concat([temp2,temp1], sort=False)<br/>
print("file "+ i +" is over, moving on...")<br/>