在数据分析、自动化办公与智能系统开发中,表格数据(如Excel、CSV、TXT等格式)是信息最基础、最广泛的载体。所谓Python读取表格数据,即通过Python语言调用标准库或第三方库,将外部表格文件中的结构化内容加载为内存中的数据结构(如列表、字典、DataFrame),为后续清洗、分析、可视化与建模提供原始输入。
为何此步骤不可跳过?试想:企业销售报表以Excel存储,用户调研问卷结果导出为CSV,数据库导出的备份为XLSX……若无法高效、可靠地读取这些文件,整个数据处理链路将无从谈起。尤其在自动化流程中(如每日自动生成日报、定时监控异常数据),表格读取的稳定性与效率直接决定系统健壮性。
当前主流Python表格读取方案涵盖:
选择依据应综合考虑:文件格式、版本、大小、性能要求、是否需保留公式/格式、是否需交互式操作等。下文将逐层展开。
看似简单的读取操作,实则暗藏多维挑战:
csv或pandas.read_csv()openpyxl或pandas.read_excel()xlrd(≤1.2.0版)或pandas.read_excel(engine='xlrd')pyxlsb或pandas.read_excel(engine='pyxlsb')openpyxl或xlwings(后者支持实时Excel交互)pandas(简洁高效)chunksize)、列筛选(usecols)、内存映射(dtype_backend='pyarrow')| 库名 | 支持格式 | 是否保留格式 | 读取速度 | 适用场景 |
|---|---|---|---|---|
csv(内置) | .csv/.tsv | 否 | ★★★★★ | 轻量级文本数据,无依赖 |
openpyxl | .xlsx/.xlsm | 是(仅读取时可选) | ★★★☆ | 现代Excel,需保留样式时 |
xlrd(≤1.2.0) | .xls | 否 | ★★★☆ | 旧版Excel兼容 |
pandas.read_excel | 全格式(自动识别) | 否 | ★★★☆ | 数据处理首选,API统一 |
xlwings | .xlsx/.xlsm/.xls | 是 | ★★☆ | 与Excel应用交互(如点击按钮触发) |
特别提醒:自xlrd 2.0起已移除对.xlsx的支持,仅保留对.xls的支持;若需处理新格式Excel,请改用openpyxl或pandas默认引擎。
安装:pip install openpyxl
from openpyxl import load_workbook
# 加载工作簿(支持.xlsx/.xlsm)
wb = load_workbook('sales_data.xlsx', data_only=True) # data_only=True读取公式计算值,否则读取公式本身
ws = wb.active # 获取活动工作表
# 读取单元格
cell_a1 = ws['A1'].value
print(f"单元格A1值:{cell_a1}")
# 读取整行(第2行)
row_2 = [cell.value for cell in ws[2]]
print(f"第2行数据:{row_2}")
# 读取整列(B列)
col_b = [cell.value for cell in ws['B']]
print(f"B列数据:{col_b}")
# 遍历所有数据行(跳过标题)
for row in ws.iter_rows(min_row=2, values_only=True):
print(row)
注意:若单元格含日期,value可能为datetime.datetime对象,需统一转换:
from datetime import datetime
date_val = ws['C2'].value
if isinstance(date_val, datetime):
date_str = date_val.strftime('%Y-%m-%d')
Excel常见陷阱:合并单元格在openpyxl中仅第一个单元格有值,其余为None。需手动填充:
merged_ranges = ws.merged_cells.ranges # 获取所有合并区域
for merged_cell in merged_ranges:
top_left = merged_cell.min_row, merged_cell.min_col
value = ws.cell(*top_left).value
for row in range(merged_cell.min_row, merged_cell.max_row + 1):
for col in range(merged_cell.min_col, merged_cell.max_col + 1):
ws.cell(row=row, column=col).value = value
隐藏行/列在values_only=True时仍会被读取,但若需跳过隐藏行,需检查row.hidden属性:
for row in ws.iter_rows(values_only=True):
if ws.row_dimensions[row[0].row].hidden:
continue # 跳过隐藏行
process(row)
安装:pip install xlrd==1.2.0(注意版本号!)
import xlrd
wb = xlrd.open_workbook('legacy_data.xls')
ws = wb.sheet_by_name('Sheet1')
# 读取单元格(行号、列号从0开始)
val = ws.cell_value(0, 0) # A1
# 读取整行(返回列表)
row_data = ws.row_values(1) # 第2行
# 获取行数与列数
n_rows = ws.nrows
n_cols = ws.ncols
# 处理日期(Excel内部存储为浮点数)
date_val = ws.cell_value(2, 3)
if ws.cell_type(2, 3) == xlrd.XL_CELL_DATE:
date_tuple = xlrd.xldate_as_tuple(date_val, wb.datemode)
from datetime import datetime
dt = datetime(*date_tuple)
推荐指数:★★★★★(兼顾易用性与扩展性)
import pandas as pd
# 基础读取
df = pd.read_excel('sales_data.xlsx')
# 高级参数
df = pd.read_excel(
'sales_data.xlsx',
sheet_name='销售明细', # 指定工作表
header=0, # 第0行作为列名
usecols='A:D,F', # 仅读取A~D列及F列
skiprows=2, # 跳过前2行
dtype={'订单号': str}, # 强制列类型
na_values=['N/A', '—'], # 自定义空值标识
parse_dates=['日期'] # 解析日期列
)
读取多个工作表:
# 读取所有工作表为字典
all_sheets = pd.read_excel('report.xlsx', sheet_name=None)
# 读取指定多个工作表
multi_sheets = pd.read_excel('report.xlsx', sheet_name=['汇总', '明细'])
处理超大Excel(分块读取):
chunks = pd.read_excel('big_file.xlsx', chunksize=50000)
for chunk in chunks:
process(chunk) # 逐块处理,避免内存溢出
适用于简单、标准化的分隔符文件:
import csv
with open('data.csv', 'r', encoding='utf-8-sig') as f: # utf-8-sig处理BOM
reader = csv.reader(f, delimiter=',') # delimiter可指定分隔符
header = next(reader) # 读取标题行
for row in reader:
# row为列表,如 ['00123', '张三', '25']
if row[0].startswith('0') and row[0].isdigit():
row[0] = row[0].zfill(5) # 补零保持学号格式
process(row)
写入同理(csv.writer),但注意:Excel打开UTF-8 CSV可能乱码,需用utf-8-sig编码或保存为ANSI格式。
import pandas as pd
df = pd.read_csv(
'data.csv',
encoding='utf-8', # 尝试'gbk'处理中文乱码
sep=',', # 分隔符
na_values=['', 'NA', 'null'],
dtype={'学号': str, '分数': float},
parse_dates=['日期'],
converters={'学号': lambda x: x.zfill(5) if pd.notna(x) else x} # 自定义列转换
)
常见编码问题解决方案:
encoding='utf-8' → 若报错,改utf-8-sigencoding='gbk'或gb2312chardet库自动检测编码import chardet
with open('unknown.csv', 'rb') as f:
raw = f.read(10000) # 读取前10KB
enc = chardet.detect(raw)['encoding']
df = pd.read_csv('unknown.csv', encoding=enc)
相比列表/字典,DataFrame提供:
dropna、fillna)import pandas as pd
# 1. 读取多张表
sales = pd.read_excel('sales.xlsx', sheet_name='订单')
products = pd.read_excel('products.xlsx', sheet_name='商品')
# 2. 数据清洗
# 2.1 统一列名(去除空格、转小写)
sales.columns = sales.columns.str.strip().str.lower()
products.columns = products.columns.str.strip().str.lower()
# 2.2 处理学号/订单号(补零)
sales['订单号'] = sales['订单号'].astype(str).str.zfill(8)
# 2.3 日期标准化
sales['日期'] = pd.to_datetime(sales['日期'], errors='coerce') # 无效日期转为NaT
# 2.4 填充缺失值
sales['单价'].fillna(sales['单价'].median(), inplace=True) # 中位数填充
# 3. 数据合并(类似VLOOKUP)
merged = pd.merge(sales, products[['商品编码', '品类']],
left_on='商品编码', right_on='商品编码', how='left')
# 4. 新增计算列
merged['总金额'] = merged['单价'] * merged['数量']
merged['是否大单'] = merged['总金额'] > 10000
策略1:仅读取必要列与行
df = pd.read_excel('large.xlsx',
usecols=['订单号', '日期', '金额'], # 仅3列
nrows=100000) # 仅前10万行
策略2:分块读取 + 流式处理
chunk_iter = pd.read_excel('large.xlsx', chunksize=50000)
results = []
for chunk in chunk_iter:
# 处理单块:计算均值、筛选等
avg = chunk['金额'].mean()
results.append(avg)
final_avg = sum(results) / len(results)
策略3:指定数据类型(避免自动推断开销)
df = pd.read_csv('data.csv', dtype={
'订单号': 'category',
'省份': 'category',
'金额': 'float32'
})
原因:文件实际编码非UTF-8(如GBK、ANSI)
解决步骤:
pd.read_csv()中指定对应编码:encoding='gbk'chardet检测(见前文代码)errors='ignore'跳过无法解码字符(慎用!可能丢失数据)原因:两表列名不一致(如一表用“商品编码”,另一表用“商品ID”)
解决:
# 方法1:重命名列
products = products.rename(columns={'商品ID': '商品编码'})
merged = pd.merge(sales, products, on='商品编码')
方法2:用left_index与right_on组合
Excel中混合格式(如“2023-1-5”与“5/1/2023”混杂)
解决:
# 方案1:强制转换 + 错误值处理
df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d', errors='coerce')
# 方案2:分列处理(先拆分再合并)
df['日期'] = pd.to_datetime(df['日期'].str.replace('/', '-'))
原因:单次加载超大文件(如2GB CSV)
解决:
chunksize分块读取dtype指定更小的数值类型(如float32替代float64)pyarrow引擎(需pip install pyarrow):pd.read_csv('data.csv', dtype_backend='pyarrow')测试环境:i7-12700H + 32GB RAM + SSD
| 方案 | 耗时 | 内存占用 |
|---|---|---|
| 默认pandas | 2.8s | 145MB |
| 指定dtype(category+float32) | 1.9s | 68MB |
| 分块读取(chunksize=20000) | 2.3s | 22MB |
| pyarrow引擎 | 0.7s | 41MB |
usecols)可减少50%+内存category类型比字符串节省70%内存.parquet(二进制格式,读取快3倍)# 保存为Parquet(需pip install pyarrow)
df.to_parquet('clean_data.parquet')
# 读取Parquet(比CSV快3倍)
df = pd.read_parquet('clean_data.parquet')
需求:每日将销售Excel更新至MySQL
import pandas as pd
from sqlalchemy import create_engine
# 读取Excel
df = pd.read_excel('daily_sales.xlsx')
# 连接数据库
engine = create_engine('mysql+pymysql://user:pwd@localhost/sales_db')
# 替换数据(truncate + insert)
df.to_sql('sales', engine, if_exists='replace', index=False)
注意事项:
if_exists='append'易重复)DATE字段需datetime.date类型)网页表格常为HTML格式,可用pandas直接读取:
import pandas as pd
# 读取网页中的所有表格
tables = pd.read_html('https://example.com/data')
# tables为列表,每个元素是DataFrame
df = tables[0] # 读取第一个表格
注意:需安装lxml(pip install lxml),且网页需无JS动态加载(否则用Selenium)。
配合schedule库实现每日自动读取:
import schedule
import time
def daily_report():
df = pd.read_excel('sales.xlsx')
# 处理逻辑...
print(f'{time.strftime("%Y-%m-%d %H:%M:%S")} 报告生成完毕')
schedule.every().day.at("09:00").do(daily_report)
while True:
schedule.run_pending()
time.sleep(60)
生产环境建议用cron(Linux)或任务计划程序(Windows)替代。
A:用sheet_name=None参数,返回字典:
all_sheets = pd.read_excel('report.xlsx', sheet_name=None)
for sheet_name, df in all_sheets.items():
print(f"工作表:{sheet_name},行数:{len(df)}")
A:openpyxl默认仅保留合并区域左上角值,其他为None。解决:
data_only=True(仍需处理)df.fillna(method='ffill')前向填充(pandas中)A:读取时强制转为字符串:
df = pd.read_excel('data.xlsx', dtype={'学号': str})
或用converters参数:
df = pd.read_excel('data.xlsx', converters={'学号': lambda x: str(x).zfill(5)})
A:
openpyxl读取时设data_only=True → 获取公式计算结果data_only → 获取公式字符串(如"=SUM(A1:A10)")pandas.read_excel()默认读取计算值A:按顺序尝试:
encoding='utf-8-sig'(处理BOM)encoding='gbk'(中文Windows默认)chardet自动检测A:用glob遍历文件:
import glob
import pandas as pd
files = glob.glob('data/*.xlsx')
all_data = pd.concat([pd.read_excel(f) for f in files], ignore_index=True)
A:
pandas.read_excel(engine='openpyxl')而非xlrdchunksize)usecols)A:用parse_dates或xlrd.xldate_as_tuple转换:
# pandas方案
df = pd.read_excel('data.xlsx', parse_dates=['日期'])
# openpyxl方案
from datetime import datetime
date_val = ws['C2'].value
if isinstance(date_val, float):
from openpyxl.utils.datetime import from_excel
dt = from_excel(date_val)
A:
# pandas方案:读取后过滤
df = df.dropna(how='all') # 删除全空行
df = df[df['关键列'].notna()] # 删除关键列为空的行
A:标准库不支持。需用第三方工具解密(如msoffcrypto-tool):
import msoffcrypto
import io
import pandas as pd
# 解密
with open('protected.xlsx', 'rb') as f:
decrypted = io.BytesIO()
office_file = msoffcrypto.OfficeFile(f)
office_file.load_key(password='123456')
office_file.decrypt(decrypted)
# 读取解密后内容
df = pd.read_excel(decrypted)
csv与pandas.read_csv基础用法pandas.read_excel参数(usecols、dtype、parse_dates)openpyxl处理格式保留场景chunksize分块与pyarrow引擎持续关注Python读取表格数据的最新实践,可加入技术社区(如知乎、GitHub)交流。