Python无法从fold中的多个excel文件中提取数据

Question

我正在尝试编写一个脚本，该脚本将打开/查看文件夹中的每个excel工作簿，从每个工作簿中提取特定值，然后将这些值粘贴到新的csv中

我的脚本（见下文）和所有49个工作簿位于以下路径：C:\Users\user.name\Desktop\Excel Test

import pandas
import os

info_headers = ['Production Name', 'Data size (GBs)', 'Billable Data size (GBs)']
info = []

files = [file for file in os.listdir('C:\\Users\\user.name\\Desktop\\Excel Test')]
for file in files:
    df = pandas.read_excel(file)
    size = df['Unnamed: 2'].loc[df['QC Checklist'] == 'Data Size (GB):'].values[0]
    name = df['Unnamed: 2'].loc[df['QC Checklist'] == 'Production Volume Name'].values[0]
    bill_size = df['Unnamed: 2'].loc[df['QC Checklist'] == 'Billable Data Size (GB):'].values[0]
    info.append([name, size, bill_size])

output = pandas.DataFrame(info, columns=info_headers)
output.to_csv('C:\\Users\\user.name\\Excel Test')  # This will output a csv in your current directory

尝试运行此操作时，我收到以下错误：

Traceback (most recent call last):
  File "C:/Users/user.name/Desktop/Excel Test/exceltest.py", line 9, in <module>
    df = pandas.read_excel(file)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\util\_decorators.py", line 188, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\util\_decorators.py", line 188, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel.py", line 350, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel.py", line 653, in __init__
    self._reader = self._engines[engine](self._io)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel.py", line 424, in __init__
    self.book = xlrd.open_workbook(filepath_or_buffer)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\xlrd\__init__.py", line 157, in open_workbook
    ragged_rows=ragged_rows,
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\xlrd\book.py", line 92, in open_workbook_xls
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\xlrd\book.py", line 1278, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
  File "C:\Users\user.name\AppData\Local\Programs\Python\Python37\lib\site-packages\xlrd\book.py", line 1272, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'import p'

Answer 1

可能有几个问题。它可能试图打开目录中的非excel文件。调用os.listdir（）后，请尝试仅筛选excel文件

或者您的excel文件可能格式不正确