我处理过的最夸张的一次需求是帮朋友合并三个月的门店日报。三百多个Excel文件文件名还是日报-3月(1).xlsx日报-3月(2).xlsx这种毫无规律的格式手工复制粘贴加调整格式至少熬两个通宵。用Python批量处理Excel和CSV文件脚本十分钟跑完还包括了清洗、去重和汇总统计。这篇文章要聊的就是用Python批量处理Excel和CSV这件事——不是学院派的理论而是我实际工作中反复使用的套路。这篇文章适合谁手里有成堆Excel/CSV文件需要合并、清洗、转换格式的运营、财务、数据分析岗的同学每天跟数据报表打交道但不想靠手工点鼠标的重复劳动者以及刚学Python没多久想找个真实场景练手的人。读完你应该能自己搭一套读文件→批量处理→写结果的流程碰到真实需求直接改改就能用。1. 为什么这件体力活值得用Python重新做一遍先别急着敲代码。我见过太多人一上来就问用哪个库读Excel结果装了一堆东西还是不知道怎么用。问题的根源在于没搞清楚批量处理的本质到底是什么。所谓批量处理核心就三件事批量发现文件、批量执行同一套操作、批量写回结果。手工做的时候一次处理一个文件用Python做的时候你只需要让程序知道文件在哪、长什么样、要做什么操作剩下的交给循环体就行。这背后的价值不仅是省时间更是把业务规则变成了可复现的代码。人工处理一份报表时脑子里的判断标准是模糊的脚本文档化的规则是精确的。为什么不用Excel自带的功能Excel本身确实有合并工作簿跨表引用这类能力但在文件数量上百、列结构不统一、还要附带清洗规则的场景下Excel的操作路径非常笨拙——录宏也好Power Query也好都有各自的学习曲线和兼容性限制。更现实的问题是大多数需要批量的场景文件格式并不规整。有的Excel带合并单元格有的CSV是GBK编码有的列名在一个文件里叫日期、在另一个文件里叫时间。这种差异Excel自带功能很难优雅处理而Python配合pandas可以把处理逻辑和文件来源彻底解耦。为什么不用VBAVBA确实能做很多事情但它绑定在Office环境里离开Excel就跑不了同事换了WPS宏还经常不兼容。Python脚本可以脱离Office运行还能和后续的数据库导入、报表生成、邮件发送流程无缝衔接。再直白一点VBA的生态已经基本冻结而pandas、openpyxl这些库背后是庞大的开源社区遇到问题搜一下几乎都有答案。为什么不用PHP有人搜excel批量处理php我只能说PHP处理Excel的生态相对薄弱而且PHP更适合做服务端Web应用做本地的数据处理脚本门槛和手感都不如Python。Python在这件事上的优势是可以组合出一套极简工具箱pandas专注表格数据处理openpyxl专注Excel文件读写glob负责找文件三个组合基本覆盖了80%以上的日常工作场景。2. 开工前的准备环境安装、依赖选型和文件纪律这一步没什么高深的但很多人就是在这里翻车的。2.1 环境与依赖到Python官网下载安装包一路下一步记得勾选Add Python to PATH。如果已经装了Python但命令行输入python没反应大概率就是PATH没配上。新手不建议一上来就折腾虚拟环境装个Anaconda或者直接用系统Python都行关键是先把pandas用起来。安装依赖只需要一条命令pip install pandas openpyxlpandas负责表格数据的读取、清洗、合并openpyxl是pandas读写Excel的后端引擎。如果你还需要精细控制Excel样式后面可以再装xlsxwriter需要操作图片、图表的话openpyxl也有对应能力但日常批量处理这两个库够了。2.2 文件纪律文件纪律是很多人忽略的关键点。我的习惯是每个批量任务建一个工作目录目录下分input和output两个子文件夹脚本放在根目录。输入文件统一放input处理后输出的结果全部进output。这样做的好处有三个脚本里的路径全部是相对路径换一台机器不会报错input和output分开哪怕处理逻辑写错也不会污染原始数据排查问题时一眼就知道哪些是源文件、哪些是产物。2.3 动手前先抽样看文件还有一个容易被忽略的动作动手写代码之前先抽样看三到五个文件。用文本编辑器打开CSV用Excel打开一个工作簿搞清楚三件事——表头在第几行、每列是什么类型、文件编码是什么。CSV文件用记事本看如果中文乱码说明大概率是GBK/GB2312编码不乱码就多半是UTF-8。Excel文件则要看有没有多Sheet、有没有合并单元格、有没有金额列带着符号和千分位逗号这种脏数据。编码问题值得单独说中文环境下老系统导出的CSV大多是GBK新系统多为UTF-8。pandas读CSV默认编码是UTF-8碰到GBK文件会直接报UnicodeDecodeError。解决办法是read_csv里指定encodinggbk或者更稳妥地用chardet检测编码。我实际工作的习惯是优先问生成文件的人是什么编码问不到就先试gbk不行换utf-8这两种能覆盖90%的情况。3. 读文件的核心姿势pandas如何拿捏Excel与CSV读一个文件谁都会读上百个文件就有门道了。3.1 关键读写参数先看最基础的读取。读CSVimport pandas as pd df pd.read_csv(input/2023年1月.csv, encodingutf-8)读Exceldf pd.read_excel(input/门店日报.xlsx, sheet_nameSheet1)真正批量处理时至少还要掌握几个关键参数。第一个是usecols列筛选。我见过太多人把整个Excel读进来里面一半是无关的备注和杂项内存白白浪费。批量处理时建议显式指定需要的列df pd.read_excel(input/门店日报.xlsx, usecols[门店, 日期, 销售额])第二个是dtype数据类型。这是最容易踩坑的地方。Excel里看到的00123读进pandas可能变成整数123或者因为混入空值被识别成float的123.0。像手机号、身份证号、工号这类需要保持前导零的字段必须在读取时就强制指定为字符串df pd.read_csv(input/员工信息.csv, dtype{工号: str, 手机号: str})第三个是header表头位置。有些Excel文件第一行是标题第二行才是真正的表头有些报表顶部还有几行说明文字。遇到这种文件用header2或者skiprows1处理df pd.read_excel(input/报表.xlsx, header2)第四个是sheet_name工作表名。如果一份Excel里有多个Sheet且结构不同可以遍历sheet_name把它们各自读到独立的DataFrame里xl pd.ExcelFile(input/汇总表.xlsx) for sheet in xl.sheet_names: df pd.read_excel(xl, sheet_namesheet) print(sheet, df.shape)3.2 用glob批量发现文件接下来是批量读取的核心——配合glob发现文件。glob是Python标准库用来按通配符匹配文件名import glob files glob.glob(input/*.xlsx) print(files)这段代码会把input文件夹下所有.xlsx文件的路径列出来。想只处理特定前缀的文件改成glob.glob(input/日报*.xlsx)就行。拿到文件列表以后循环读取all_data [] for f in files: df pd.read_excel(f, usecols[门店, 日期, 销售额]) all_data.append(df) merged pd.concat(all_data, ignore_indexTrue)pd.concat负责把多个DataFrame纵向拼接成一个大DataFrameignore_indexTrue表示重新生成连续索引避免各份文件原来的行号混在一起。3.3 合并前的列名统一这里有一个我反复踩到的现实问题各文件的列名不统一。3月文件叫门店4月叫店铺合并之前不处理就会产生两列。我的处理方式是在循环里加一个标准列名映射表column_map {门店: 门店, 店铺: 门店, 分店: 门店} df df.rename(columnscolumn_map)这样不管源文件里叫什么都无所谓进入合并结果时都是统一的门店。4. 批量处理的经典动作合并、清洗、查找与转换文件读完重头戏才开始。批量处理的价值不在于能读而在于读完之后能自动完成一套有业务含义的操作。4.1 合并纵向concat与横向merge前面用pd.concat演示了多个同结构文件的纵向合并。还有一种场景是横向合并两个文件有共同的键比如门店ID需要把各自的字段拼成更宽的表。这就要用mergedf_shop pd.read_excel(input/门店信息.xlsx) df_sales pd.read_excel(input/销售数据.xlsx) result df_sales.merge(df_shop, on门店ID, howleft)merge相当于Excel里的VLOOKUP但比VLOOKUP好用得多——可以指定多个连接键on参数传列表可以选择保留方式howleft保留左边全部记录howinner只保留两边都有的。实际合并前先确认连接键的类型一致。一个文件里门店ID是字符串001另一个是整数1合并结果会留下一堆NaN。4.2 清洗去重、补空、格式还原清洗这个词听起来抽象实际上就是处理那几类脏数据空值、重复值、错误格式、文本中的空格和符号。去重最常见df df.drop_duplicates(subset[订单号])空值填充按需处理df[备注] df[备注].fillna(无) df df.dropna(subset[销售额])销售额带符号的问题也很容易碰到。Excel里1,234.56这种格式pandas读进来是字符串不能直接计算。需要先去掉千分位逗号和货币符号再转floatdf[销售额] df[销售额].str.replace(, , regexFalse) df[销售额] df[销售额].str.replace(,, , regexFalse) df[销售额] pd.to_numeric(df[销售额], errorscoerce)errorscoerce很关键如果某个单元格无法转成数字pandas不会报错中断而是把它变成NaN。事后排查就很方便。日期列的处理同样高频。Excel里的日期有时候是2023/1/5有时候是2023年1月5日还有时候是真正的datetime单元格。统一转换成标准格式df[日期] pd.to_datetime(df[日期], format%Y/%m/%d, errorscoerce)如果不想指定格式直接pd.to_datetime(df[日期])也可以pandas会自动推断多种常见格式。4.3 查找与转换从contains到pivot_tablepython查找excel中字符串这种需求本质上就是contains做条件筛选。比如找出所有备注里包含退款的订单refunds df[df[备注].str.contains(退款, naFalse)]naFalse保证备注为空时不会报错而是被过滤掉。如果要按多个关键词筛选可以用正则keyword 退款|退货|取消 bad_orders df[df[备注].str.contains(keyword, naFalse, regexTrue)]转换动作也很常用。比如把明细表转成透视表格式pt df.pivot_table(index门店, columns品类, values销售额, aggfuncsum, fill_value0)一行代码就能把门店品类销售额的长表转成行是门店、列是品类的宽表。这在做汇总分析时非常常用。跨格式转换也经常遇到。很多人搜markdown表格转换excel说明文档里的表格要迁移到Excel。markdown表格本质上就是带|分隔的纯文本用pandas处理非常合适把markdown表格粘贴到文本文件里读取时用分隔符|df pd.read_csv(input/table.md, sep|, skipinitialspaceTrue) df df.dropna(axis1, howall) # 去掉空列然后to_excel写出去就行。反过来Excel转markdown表格也只靠简单字符串拼接。5. 结果写回快速导出与保留格式两条路线怎么选处理完的数据要落盘这里有两个完全不同的需求方向很多新手混在一起。5.1 快速导出路线第一种是数据要能再次被程序读取或者要导入数据库、发给别人继续加工。这种场景用CSV或标准Excel表格就够了追求数据准确、体积小、兼容性好。用to_csvmerged.to_csv(output/合并结果.csv, indexFalse, encodingutf-8-sig)注意这个utf-8-sig。如果直接写utf-8用Excel打开含中文的CSV文件时大概率会乱码。utf-8-sig会在文件开头加一个BOM标记Excel能正确识别成UTF-8编码处理含中文的CSV时基本是必选项。to_excel则是merged.to_excel(output/合并结果.xlsx, indexFalse, sheet_name汇总)5.2 保留格式路线第二种是给领导看、要保留Excel原有格式的场景。这时候直接to_excel覆盖不行因为pandas写出来的Excel没有格式。如果你的需求是在原有Excel基础上改动一些单元格、或者给结果加样式就要用openpyxl。比如只想给汇总表插入一列同时不破坏原来的列宽、字体和颜色可以这样from openpyxl import load_workbook wb load_workbook(input/原始报表.xlsx) ws wb.active ws[H1] 新增列 # 这里可以按业务逻辑填写H列的值 wb.save(output/带格式结果.xlsx)openpyxl的API比较底层适合精确控制单元格的场景。如果只是要pandas输出时带一点简单的表头样式xlsxwriter引擎会更顺手with pd.ExcelWriter(output/带样式.xlsx, enginexlsxwriter) as writer: merged.to_excel(writer, sheet_name汇总, indexFalse) workbook writer.book worksheet writer.sheets[汇总] header_fmt workbook.add_format({bold: True, bg_color: #D9E1F2}) worksheet.set_row(0, None, header_fmt)5.3 多Sheet输出多Sheet输出是高频需求。把多个DataFrame写进同一个Excel的不同工作表with pd.ExcelWriter(output/多表汇总.xlsx) as writer: summary.to_excel(writer, sheet_name汇总, indexFalse) detail.to_excel(writer, sheet_name明细, indexFalse) bad.to_excel(writer, sheet_name异常单, indexFalse)一个文件搞定所有结果领导和同事只需要下载一个附件。我做报表的习惯是把最终文件分成汇总页和异常页两个Sheet——正常情况下只看汇总页有问题才去翻异常页。6. 一个完整实战门店日报合并清理从零到可用原理讲了这么多不如直接跑一个完整例子。这是我做过的真实需求的简化版合并一个月的门店销售日报做数据清洗输出一个带汇总和多Sheet的结果文件。6.1 需求拆解运营每天发一份Excel叫店报-20230601.xlsx里面是当天各门店各品类的销售明细。一个月30份要合成一张总表同时统计各门店总销售额把异常数据单独列出来。6.2 完整脚本第一步目录和文件准备。把30份日报都放进input文件夹用glob匹配import pandas as pd import glob files sorted(glob.glob(input/店报-*.xlsx))第二步循环读取并做统一的列名映射和清洗column_map {门店: 门店, 店名: 门店, 分店: 门店} all_data [] for f in files: df pd.read_excel(f, usecols[门店, 品类, 销售额, 订单数], dtype{订单数: int}) df df.rename(columnscolumn_map) df[销售额] pd.to_numeric(df[销售额], errorscoerce) df df.dropna(subset[销售额]) all_data.append(df)第三步合并所有文件merged pd.concat(all_data, ignore_indexTrue)第四步识别异常数据。销售额为负数的退款记录、订单数为0但销售额不为0的记录negative_sales merged[merged[销售额] 0] zero_order_sales merged[(merged[订单数] 0) (merged[销售额] ! 0)] bad_rows pd.concat([negative_sales, zero_order_sales]).drop_duplicates()第五步清洗后的正常数据用于汇总clean merged.drop(indexbad_rows.index) summary clean.groupby(门店, as_indexFalse).agg({销售额: sum, 订单数: sum})第六步写回结果with pd.ExcelWriter(output/6月门店汇总.xlsx) as writer: summary.to_excel(writer, sheet_name门店汇总, indexFalse) merged.to_excel(writer, sheet_name全部明细, indexFalse) bad_rows.to_excel(writer, sheet_name异常记录, indexFalse) print(f共处理 {len(files)} 个文件总记录数 {len(merged)}异常记录 {len(bad_rows)})6.3 运行效果与细节说明整个脚本不到四十行跑一次不到三秒。以前人工处理30份日报算上复制粘贴、公式核对、异常筛选至少一个下午现在每天下班前跑一遍脚本就能出数。更重要的是脚本逻辑固定不会像人手操作那样时好时坏换个月份只需改一下文件名匹配规则。补充一个真实细节运营发的日报有时候某天文件名是店报-20230601.xlsx有时候是店报-20230601(1).xlsx同一天生成多份。glob的*通配符能匹配到这些文件但排序时要小心。我用了sorted()保证处理顺序按文件名排序这样即使某天出现重复文件顺序也是稳定的。7. 最容易翻车的五个坑以及绕坑习惯最后分享一些我在批量处理里踩过的坑每一个都是真实代价换来的。7.1 编码问题读写CSV的隐形杀手除了前面提的UTF-8和GBK读取报错写CSV时也有对应的坑——用utf-8写出的CSV在Excel里打开中文乱码。我的习惯是写入CSV永远用encodingutf-8-sig。另外有些文件本身就是UTF-8 BOM编码pandas读取时第一列列名会带上\ufeff字符处理方法是read_csv时指定encodingutf-8-sig会自动去掉BOM。7.2 前导零丢失工号00123变成123看起来是小事但用这类字段做关联匹配时会完全匹配不上。对应策略就是前面说的dtype{工号: str}。如果已经读进来了才发现补救办法是df[工号] df[工号].astype(str).str.zfill(5)zfill(5)表示补足至少5位但需要先知道正确的位数。所以最好的办法还是在读取时就锁死类型。7.3 空文件和全空Sheet批量处理几十个文件时偶尔会有一个文件只有表头没有数据。pd.concat空DataFrame通常没问题但如果对这个空DataFrame做了某些操作比如取第一行就会报错或产生诡异结果。我的习惯是在循环里加一个判断if df.empty: print(f警告{f} 是空文件) continue7.4 内存问题几百MB的CSV一次性pd.read_csv全部读进内存笔记本可能会卡死。遇到大文件可以分块读取chunks pd.read_csv(input/超大文件.csv, chunksize100000) result_parts [] for chunk in chunks: result_parts.append(process(chunk)) result pd.concat(result_parts)chunksize表示每次读取10万行处理完再读下一批。实际经验是文件超过200MB就建议分块处理否则机器会风扇狂转。7.5 路径问题脚本里写input/xxx.csv依赖的是当前工作目录。在Jupyter Notebook里跑当前目录可能是Notebook所在目录在命令行跑可能是执行python时的目录。最稳妥的做法是用pathlib构造相对于脚本文件的路径from pathlib import Path BASE_DIR Path(__file__).parent input_dir BASE_DIR / input用Path对象拼路径在Windows和macOS/Linux上都能正常工作不会踩斜杠方向不一致的坑。8. 我长期做数据处理脚本沉淀的几个习惯最后说几点我踩过多次坑之后沉淀下来的习惯对刚开始写批量处理脚本的人应该有点用。第一数据操作之前永远留一份原始文件的备份。input和output分离的目录结构本身就是一种保障——脚本只读取input只写入output原始文件永远不会被覆盖。如果你确实需要修改原始文件也先复制一份到backup目录。第二把处理逻辑封装成函数。我第一次写合并脚本时所有代码都堆在顶层能跑通但换一个需求就要重写。后来养成习惯把读文件并清洗合并去重生成汇总分别写成函数每个函数只干一件事。这样不同批次的报表只需要调用同一个函数传文件名就行。函数化的脚本还有一个好处测试方便。对函数传入造好的小DataFrame看输出是否符合预期比跑整个脚本快得多。第三脚本里加打印进度。处理几十上百个文件时干等着心里没底。我会在循环里加一个计数器每处理10个文件打印一条进度全部处理完再打印总记录数和异常记录数。跑完看日志就知道哪些文件被跳过、哪些数据被清洗掉了。加一行print的成本极低排查问题时收益非常大。第四先在小样本上测试。不管脚本看起来多正确第一次跑的时候我都会先只匹配前3个文件确认输出没问题了再放全部文件跑。这能避免最尴尬的情况30份文件全部合并完才发现有一份的列名映射错了又要从头跑一遍。第五脚本要版本化。哪怕只是加个注释保存时也换个带日期的文件名。我做过一次蠢事改了一个脚本结果改坏了但原来的版本已经被覆盖花了大半天时间重新调试。从那以后我养成了命名带版本的习惯比如merge_daily_report_v2.py。一个人真正开始省时间是从写第一个批量处理脚本开始的。刚开始可能写得很慢一个简单的合并脚本要改半天但同一个脚本第二次、第三次使用几乎零成本。我到现在还会在项目里翻旧脚本复制里面的函数改一改就用——这就是长期积累的价值。如果你手头正好有一堆Excel或CSV要处理别急着复制粘贴了。先把文件放进input文件夹装好pandas用这篇文章里的套路跑通第一个合并脚本。跑通之后你会回来感谢自己。