时间戳

首页 > 解决方案 > Python - 从文件夹中的多个 excel 文件中提取数据时遇到问题

python - Python - 从文件夹中的多个 excel 文件中提取数据时遇到问题

问题描述

我正在尝试编写一个脚本，该脚本将打开/查看文件夹中的每个 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'

标签： pythonpandas

解决方案

可能有几个问题。它可能正在尝试打开目录中的非 excel 文件。调用 os.listdir() 后，尝试仅过滤 excel 文件。

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

推荐阅读