Python批量处理Excel与CSV：实用指南

手头堆着几十个结构一模一样的Excel表格每回都要打开、复制、粘贴、另存为循环往复到怀疑人生。还有那种从系统里导出来的CSV文件一打开全是乱码要么就是数字前面带个’号处理起来比手工记账还费劲。这篇文章就围绕怎么用Python批量处理Excel和CSV文件这回事展开把最近实际项目中摸出来的流程、参数和坑都梳理一遍适合那些每天要和表格打交道、想从重复劳动里解脱出来的朋友参考从安装环境到跑通第一个批量脚本一条线讲清楚。1. 整体思路与工具选型为什么偏偏是Python批量处理表格这件事说实话方案不止一个。Excel自带的VBA能做Power Query也能做甚至某些线上工具点两下也算处理了。但真到了一定规模——比如上百个文件、几十万行数据、还需要跨格式转换的时候VBA就显得有点力不从心线上工具又容易在数据隐私上让人不放心。Python这套组合拳的优势在于免费、脚本可复用、跑完留痕而且从一个文件到一万个文件代码逻辑基本不用变。我自己的选择很简单处理Excel和CSV核心库就三个——pandas、openpyxl、csv。pandas负责读和写的主体逻辑openpyxl是pandas读写Excel时底层真正干活的引擎csv模块则是处理纯CSV文件时更轻量、更可控的工具。很多人一上来就纠结什么xlrd、xlsxwriter、pyxlsb其实在批处理场景里xlrd早就不支持xlsx了xlsxwriter主要是写复杂格式用的日常批量任务用pandas配openpyxl完全够。1.1 用pandas统一数据入口的好处pandas最打动我的地方是它把Excel和CSV之间的差异抹平了。对一个数据工程师来说管你是xlsx还是csv读进来都是DataFrame剩下的操作逻辑完全通用——筛选、去重、合并、分组、改列名一套方法两边都能用。这就意味着你写好的批处理脚本今天处理CSV明天换成Excel只需要改文件路径和读写参数核心逻辑一行不用动。而且pandas对编码处理的容忍度比较高。CSV文件最恶心的就是编码问题同一批文件里可能混着utf-8和gbk直接用记事本打开会看到一片乱码。pandas在读取时可以逐文件尝试编码配上一个简单的循环判断就能把这种混乱局面统一起来。这种细节在实际项目里太重要了尤其是从不同供应商、不同系统导出来的文件编码风格五花八门。另外pandas在处理大文件时也有分块读取chunksize这样的手段。我处理过几个单文件就超过1GB的CSV直接read_csv一次性读完的话内存直接爆掉用分块循环配合批量聚合就能平稳消化。这套方案在数据量和单机资源之间找到了一个务实的平衡点不用上什么分布式框架个人电脑就能搞定。1.2 环境准备装好工具再干活准备工作其实很简单但很多人卡在了安装这一步或者装完了不知道装没装对。我推荐直接用Anaconda发行版或者用官方Python配pip来装。如果机器上已经有Python了打开命令行执行一行命令就行pip install pandas openpyxlpandas会自动带上numpy这个底层依赖openpyxl则是为了读写xlsx格式。装完之后建议在Python交互环境里快速验证一下import pandas as pd import openpyxl print(pd.__version__)能打印出版本号就说明环境OK了。这里有个小建议不要图省事用Python 2.x现在pandas的生态基本都往3.x走很多新特性不再向后兼容老老实实用Python 3.8以上版本最省心。工作目录的组织也值得一提。我一般会建一个清晰的目录结构把原始文件、处理脚本、输出结果分开存放。比如data/raw放原始文件data/output放处理后的结果脚本本身放在项目根目录。这个小习惯在批处理场景里价值极大因为脚本跑完你总需要检查结果如果输出文件和源文件混在一起很快就会分不清谁是谁了。提示在处理文件之前先备份一份原始数据到另一个目录这个习惯我吃了亏之后才养成的。批量操作意味着错误也会“批量”发生没有备份的话几百个文件可能瞬间就被处理得面目全非。2. 核心操作细节读写参数与批量匹配工具选好了接下来就得搞清楚pandas读写Excel和CSV时的关键参数。这些参数看着不起眼但直接决定了你处理出来的数据质量。2.1 读取Excel和CSV的常用写法读取Excel最常见的情况是处理多个sheet或者指定某一列作为数据类型。我平时用得最多的读取方式是import pandas as pd df pd.read_excel(data/raw/销售数据_2024.xlsx, sheet_name0, dtypestr)sheet_name可以是数字第几个sheet也可以是sheet名字符串如果写成一个列表就能一次读多个sheet返回的是一个字典。dtypestr这个参数在早期给了我很大帮助——Excel里的数字可能有各种格式比如手机号、身份证号、数值型编码pandas默认会自作聪明地推断类型导致后面的0被丢掉转成str之后就能保留原始的字符串内容后续要清洗再统一转换。读取CSV时最关键的参数是encoding和sepdf pd.read_csv(data/raw/某系统导出.csv, encodinggbk, sep,)sep不一定要是逗号有时候从数据库导出的反而是制表符\t或分号;read_csv会根据sep自动切分省去手工转换delimiter的麻烦。encoding这个参数如果写错了读出来就是乱码或直接报错后面我专门整理过几种排查思路。还有个很容易踩坑的地方——列名不一致。不同批次的文件里同一列可能叫“销售额”、“销售金额”、“amount”如果直接合并数据会变成两列后面的统计就全乱了。处理办法是在读取时直接把列名统一掉df df.rename(columns{销售金额: 销售额, amount: 销售额})2.2 用glob批量匹配文件路径批处理的第一步往往是要把几十上百个文件名都找出来。手动一个一个写在脚本里显然不现实这时候用标准库里的glob模块就能省事不少import glob excel_files glob.glob(data/raw/*.xlsx) csv_files glob.glob(data/raw/*.csv)拿到文件列表之后用for循环挨个处理。match这个模式里还可以带路径前缀、日期范围之类的过滤条件比如只处理文件名含“2024”的Exceltarget_files glob.glob(data/raw/*2024*.xlsx)glob返回的文件顺序在Windows上可能是乱的如果处理逻辑对顺序有要求用sorted()包一层就行。这一招在处理大量文件时特别有用能让脚本有序执行结果文件按顺序输出后续核对起来方便得多。2.3 写出结果时的几个关键选择写入Excel时最常见的是两个需求一是把多个DataFrame写到一个文件的不同sheet里二是覆盖原有的表结构。pandas里的ExcelWriter配合 mode 参数就能搞定with pd.ExcelWriter(data/output/汇总结果.xlsx, engineopenpyxl, modew) as writer: df1.to_excel(writer, sheet_name第一季度, indexFalse) df2.to_excel(writer, sheet_name第二季度, indexFalse)indexFalse这个参数务必要记得写否则pandas会把DataFrame的行索引当成一列数据塞进Excel白白多出一列没用的数字。另外如果文件已存在modew是覆盖modea则是追加追加的时候可以通过if_sheet_exists参数决定是替换sheet还是新增sheet。这个细节在日志式的批处理任务里非常实用。写CSV时最常见的问题又是编码。如果希望Excel双击打开不乱码写CSV时建议用utf-8-sig编码或者直接输出gbkdf.to_csv(data/output/清洗结果.csv, indexFalse, encodingutf-8-sig)utf-8-sig会在文件开头加一个BOM头Excel能正确识别并显示中文如果用纯utf-8写Excel打开就会把中文显示成乱码。这个坑我当初踩了好几次才搞明白想起来就心痛。3. 实操场景批量合并与批量清洗的完整流程理论说完了直接上两个真实项目里反复用到的场景连代码带注释一起给出方便直接抄作业。3.1 场景一合并几十个结构相同的Excel文件实际工作中经常遇到的情况是财务每个月导出的报表、运营每天产生的统计表都存在同一个目录里文件名可能带日期或序号但表头的列名完全一样。这种场景下的目标就是把它们全部读进来并成一个总表。先看读取部分的实现import glob import pandas as pd file_list glob.glob(data/raw/销售报表_*.xlsx) print(f共找到 {len(file_list)} 个文件) all_data pd.DataFrame() for file in file_list: df pd.read_excel(file, sheet_name0, dtypestr) # 给每条数据打上来源文件的标签方便后续追踪 df[来源文件] file.split(/)[-1] all_data pd.concat([all_data, df], ignore_indexTrue) print(f合并完成总行数{len(all_data)})这里用到了pd.concat它和append的区别在于concat是每次生成一个新的DataFrame而append则是在原对象上追加。循环次数多的情况下concat性能更稳定而且用ignore_indexTrue可以让合并后的行索引重新从0开始排列不会残留原始文件的索引编号。加上“来源文件”这一列是批处理中一个极其实用的细节一旦后续发现某一行数据有异常直接看这列就能定位回原始文件省去大海捞针式的排查。合并完成之后别忘了检查一下表头是否一致。文件多的时候偶尔会出现某个月份的表多了一列“备注”直接合并会把总表结构破坏掉。一个稳妥的做法是在循环里先检查列名集合expected_columns [日期, 区域, 销售额, 订单量] for file in file_list: df pd.read_excel(file, dtypestr) if not set(expected_columns).issubset(df.columns): print(f警告{file} 的表头不一致已跳过) continue all_data pd.concat([all_data, df], ignore_indexTrue)这种防御式编程处理方式能让批处理脚本在面对“脏数据”时不至于崩溃而是跳过有问题的文件并留下警告后续再人工核查。最后把合并结果分别写入Excel多sheet和CSVwith pd.ExcelWriter(data/output/全量汇总.xlsx, engineopenpyxl) as writer: all_data.to_excel(writer, sheet_name全部数据, indexFalse) # 再按区域做一个汇总sheet summary all_data.groupby(区域, as_indexFalse)[销售额].sum() summary.to_excel(writer, sheet_name区域汇总, indexFalse) all_data.to_csv(data/output/全量汇总.csv, indexFalse, encodingutf-8-sig)区域汇总用的是groupby加sum如果不设置as_indexFalse分组的列就会变成索引而不是普通列写进Excel时会多出一列索引。这种小细节多看几眼就记住了。3.2 场景二CSV文件的批量清洗与格式统一CSV文件往往是从业务系统里直接导出的常见的问题有乱码、字段前后带空格、日期格式不统一、数字被识别成了文本。批量清洗就是把这些脏东西处理干净。import pandas as pd import glob csv_files glob.glob(data/raw/api导出_*.csv) results [] for file in csv_files: # 先尝试utf-8失败就退回gbk try: df pd.read_csv(file, dtypestr, encodingutf-8) except UnicodeDecodeError: df pd.read_csv(file, dtypestr, encodinggbk) # 用strip去掉列名和字段中的空格 df.columns df.columns.str.strip() for col in df.columns: if df[col].dtype object: df[col] df[col].str.strip() # 日期格式统一成YYYY-MM-DD if 日期 in df.columns: df[日期] pd.to_datetime(df[日期], errorscoerce).dt.strftime(%Y-%m-%d) # 删除全空行 df df.dropna(howall) results.append(df) print(f{file} 清洗完成剩余 {len(df)} 行) final_df pd.concat(results, ignore_indexTrue) final_df.to_csv(data/output/清洗合并.csv, indexFalse, encodingutf-8-sig)这段代码里最值得关注的是pd.to_datetime加errorscoerce的组合。errorscoerce的意思是如果某个日期格式实在解析不了就置为NaT缺失值而不是直接抛异常中断整个脚本。批处理场景下这个参数就是救命的因为几十个文件里总有一两个“刺头”一个坏值导致脚本中断的话前面的处理就全白干了。清理空格那段循环里加了一个类型判断。因为pandas读取时有些列可能是数字类型对数字列调用str.strip()会报错。判断出object类型才处理就避免了这种问题。这也是很多初学者跑批处理脚本时最容易遇到的坑之一——总以为给每列都处理一遍就行但忽略了列本身的类型。注意遇到解析不了的日期可以先保留成一个单独的“异常列”看看而不是直接扔掉。我遇到过日期格式千奇百怪的情况有的带时分秒有的写的是“2024/5/1”还有的干脆是Excel序列号数字。用errorscoerce处理之后凡是变成NaT的行我再单独导出做人工确认比一次性硬处理更能防止数据丢失。4. 常见问题与排查技巧实录批处理脚本写过一段时间之后我发现来来回回碰到的问题也就那么几个。这里按问题出现的频率整理成一张速查表后面再针对几个重点问题展开说一下。常见问题可能的原因快速排查方法解决办法读取CSV乱码编码判断错误打印文件前几行或尝试多种编码用try/except循环尝试utf-8和gbkExcel读取报错文件不是xlsx格式检查文件扩展名和真实格式统一转成xlsx或用xlrd读取xls合并后行数对不上表头不一致导致数据错位检查各文件列名集合合并前校验列名跳过异常文件中文列名读取后变成UnicodeExcel列名特殊字符或空格打印df.columns查看实际内容用rename或columns.str.strip清洗日期列变成数值Excel单元格格式问题查看dtype和样本值读取时指定parse_dates或用pd.to_datetime大CSV文件内存溢出read_csv一次性读入全部数据查看原始文件大小和内存用量用chunksize分块读取后汇总写出的Excel多了一列索引没有设置indexFalse打开输出文件查看第一列写入时设置indexFalse4.1 编码问题最烦人但也最好解决CSV和Excel最大的不同在于xlsx内部是XML结构有明确的编码定义而CSV就是一个纯文本文件编码完全取决于导出它的程序。Windows上很多老系统导出的是gbk或gb2312国产软件可能导出utf-8macOS/Linux上常见utf-8。一批文件里混着两三种编码是很正常的。我用的办法是在读取时做一层异常捕获其实也就是代码里常用的try/exceptencodings [utf-8, gbk, gb18030, latin1] for enc in encodings: try: df pd.read_csv(path, encodingenc) break except (UnicodeDecodeError, UnicodeError): continue这段逻辑的运行逻辑是先试最标准的utf-8失败就换gbk还不行就换gb18030国标扩展集实在不行就latin1兜底——latin1什么都读得进来但中文很可能是乱码。这个方法能覆盖大多数情况剩下的少数异常文件单独处理即可。4.2 大文件处理分块读取的实战解法CSV文件一旦上了GB级别普通的read_csv很容易把内存直接占满。pandas官方提供了分块读取参数可以指定每次读入的行数chunk_iter pd.read_csv(big_file.csv, chunksize10000, encodingutf-8)chunksize10000表示一次读1万行返回的是一个可迭代对象每次迭代拿到一个1万行的DataFrame。内存占用被控制在一个很小的范围内对整个文件的数据统计可以在循环里逐步累加total_sales 0 row_count 0 for chunk in chunk_iter: total_sales chunk[销售额].astype(float).sum() row_count len(chunk) print(f销售额总和{total_sales}总行数{row_count})分块后要注意一个问题如果后续要对全量数据做排序或去重分块处理会变得麻烦因为单个chunk内部的去重结果并不是整体去重的最终结果。这时候要么先用分块把数据压缩成中间文件要么直接考虑用数据库来处理。如果是单机数据量不大比如100万行以内老老实实一次性读进来反而更简单。4.3 Excel里公式和格式的坑openpyxl默认读Excel时对于单元格里是公式的情况有个很尴尬的行为——读出来可能是公式本身而不是公式计算后的结果。原因是openpyxl有data_only参数默认False时返回公式字符串设为True时返回缓存的值。但如果这个Excel从来没用Excel软件打开过、没有缓存计算结果data_onlyTrue也可能读不到数返回None。遇到这种情况我的处理策略是如果必须拿到计算结果先用Excel本身的VBA或openpyxl把公式强制计算一遍并保存再读取缓存值。如果只是想复制表格结构或者做格式调整直接用openpyxl操作单元格会比较顺手。另外pandas在读取Excel时会默认把“日期”类型的单元格解析成datetime对象但如果你在读取时用了dtypestr日期就会变成字符串形式。这两种处理各有适用场景分析计算用datetime对象数据搬运用字符串保留原格式看情况取舍。5. 扩展玩法从批处理到自动化流水线批处理脚本写好了完全可以往自动化的方向再走一步。我有几个小经验花很少的功夫就能让脚本的价值倍增。5.1 按内容自动拆分大表除了合并拆表也是高频需求。比如一张全量客户表想按“省份”字段拆成各省份单独的文件用groupby遍历一下就够了for province, group in df.groupby(省份): group.to_excel(fdata/output/按省份拆分/{province}.xlsx, indexFalse) print(f{province}{len(group)} 条)配合目录自动创建就能实现一条命令拆几十个文件的效果。这里的groupby不仅能做汇总计算还能当作拆分文件的利器很多人没往这个方向想。5.2 定时任务跑批如果数据每天固定时间从系统导出批处理脚本完全可以挂到定时任务里做到无人值守。Windows上用任务计划程序macOS/Linux上用cron核心就是一行命令python batch_process.py这里说个经验教训在定时任务里跑Python脚本一定要在脚本开头把工作目录切到脚本所在目录或者使用绝对的路径。否则定时任务触发时的工作目录可能跟你手动执行时不一样文件路径直接找不到。还有一点定时任务里最好用绝对路径的Python解释器路径不然系统可能用了别的Python版本。这些细节看着琐碎但真能让你少掉很多头发。5.3 配合日志留下处理痕迹无人值守的任务如果出错了没有日志你是完全不知道的。在脚本里加一个简单的logging配置把处理进度和异常信息写到文件里import logging logging.basicConfig(filenamebatch_process.log, levellogging.INFO, format%(asctime)s - %(levelname)s - %(message)s) for file in file_list: try: df pd.read_excel(file) logging.info(f成功处理{file}) except Exception as e: logging.error(f处理失败{file}错误信息{e})这样的脚本跑完你不需要守在电脑前瞪着眼睛看屏幕过段时间翻日志就知道哪些文件成功了、哪些失败了、失败原因是什么。这比在命令行看输出可靠得多尤其是处理几十上百个文件的时候。6. 写在最后的一点实用心得批量处理Excel和CSV这件事技巧和代码都在前面写得差不多了最后想分享一点我自己的习惯和体会。头一个建议是任何批处理脚本都先用两三个文件试跑一遍确认逻辑没问题再放开到全量文件。我见过太多人写好了脚本直接跑全量结果逻辑里有个隐藏问题几百个文件全都处理错再想挽回就只能靠备份。我有一次就是没检查就跑了全量替换输出文件全变空白还好那之前备份了原始数据不然真的要怀疑人生。另一个体会是文件命名的规范会直接决定批处理脚本的复杂程度。如果有可能和业务方约定好导出文件的命名规则比如统一用“模块_日期_序号”这种结构glob匹配的时候只要一个模式就能搞定。有一回我接过一个项目文件名是完全没有规律的“新建文档(1).xlsx”、“副本(2).xlsx”这种光匹配文件就写了快五十行代码。从那以后我都建议在文件生成源头就把命名规范定好这比事后在脚本里各种取巧都高效。还有就是在处理敏感数据的时候输出文件建议不要直接在原目录覆盖写。我习惯把处理结果放到单独的output目录定期清理。这样不管怎么折腾原始数据始终是安全的脚本错了也能随时重来。批处理的本质是提升效率但如果因为贪快而丢掉了数据的可追溯性效率再高也得不偿失。