先把话说在前头如果你手里堆着几十个Excel要合并、清洗、转成CSV或者反过来隔三差五就要接手乱七八糟的CSV文件别急着一个个打开手工弄。用Python批量处理Excel和CSV文件是我这几年做数据支持工作里复现率最高的一套活。它解决的从来不是“能不能处理”的问题而是“多久能处理完”和“会不会有人手滑改错数据”的问题。上个月部门收到20多个分公司的销售月报有的叫“销售表”有的叫“数据汇总”列名还五花八门甚至有几个文件带了合并单元格。我花了半小时写了个脚本几分钟跑完之后每个月只需要双击运行一次。接触过各种方案之后我的体会是批量处理Excel和CSV这件事真正卡住新手的往往不是Python本身而是“不知道用哪个库”“读取时有哪些坑”“编码怎么选”这些细节。这篇我把完整方案和你可能踩的坑一次性说清楚内容偏实操代码可以直接抄。1. 先选对工具再动手pandas、openpyxl和csv模块的取舍1.1 三套方案各有各的适用边界很多人一上来就装pandas这没错但你要知道pandas不是唯一的答案。我自己的习惯是动手前先想清楚这次处理的核心需求是什么然后从三套工具里挑一套合适的。场景推荐工具理由只做CSV读写、字段拼接、转义处理Python内置csv模块零依赖速度最快环境兼容性最好需要保留Excel样式、修改单元格、处理公式openpyxl精确到单元格级别的控制批量读取、筛选、合并、透视、格式转换pandas表格思维一行代码覆盖大部分需求具体来说标准库csv适合那种“从A系统导出CSV调整后导进B系统”的轻量场景它不需要安装任何第三方包也不存在版本兼容问题。openpyxl则适合改格式类需求比如批量把某个Sheet的字体颜色改掉、在指定行插入内容、修改批注。但如果你的目标是“把100个Excel读进来筛出销售额大于一万的行合在一起输出”那直接用pandas是最省事的它的read_excel、read_csv、to_excel几乎就是为这种场景设计的。我一般建议把pandas和openpyxl都装上两者配合使用pandas负责批量读取和运算需要精细操作单元格时再绕回openpyxl。标准库csv不需要装但要会读会写因为某些特殊环境里pandas可能装不上。1.2 安装与验证一次把环境配好环境准备这块先把几个高频问题按顺序排好照着做基本不会翻车。安装Python时Windows平台一定记得勾选“Add Python to PATH”否则后面命令窗口输入python会提示找不到命令。装完之后用VS Code写代码的话装好Python扩展再在右下角或命令面板里选中正确的解释器。很多所谓“环境问题”最后查出来都是解释器没选对。依赖安装很简单pip install pandas openpyxl国内网络环境下换镜像源会快很多pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple装完验证一下python -c import pandas, openpyxl; print(pandas.__version__, openpyxl.__version__)这里有个特别容易踩的坑pandas装了openpyxl没装运行read_excel会直接报“Missing optional dependency openpyxl”。字面意思已经说得很清楚了但我见过不止一个同事为了这个报错把pandas卸载重装好几次最后才反应过来是openpyxl缺失。所以装依赖的时候两个一起装最省事。1.3 版本和文件格式的兼容问题再避一个坑不要觉得“装了最新版就万事大吉”。pandas对Excel的读写依赖openpyxl或xlrd但两者支持的格式不一样。openpyxl支持.xlsx不支持老的.xlsxlrd 2.0以上版本只支持.xls不支持.xlsx我之前用xlrd读xlsx就吃过亏pandas 2.x之后有些版本的默认引擎选择也会影响读取结果。如果你的源文件是.xls结尾处理方案有两个一是让文件提供方另存为.xlsx这最省事二是额外装一个旧版xlrd并显式指定enginexlrd。我的建议是优先让文件变成.xlsx因为.xls本身是旧格式后续各种工具兼容性都差。别小看这点实际处理大批量文件时“文件格式和代码预期不一致”是报错的重灾区。2. 第一批文件怎么进来目录遍历、文件筛选与类型兜底2.1 用pathlib遍历目录不要再用os.listdir批量处理的第一步是拿到所有目标文件的路径。我推荐用pathlib的glob方法比老派的os.listdir简洁得多而且返回的是Path对象拼接路径时不用再管反斜杠和正斜杠的问题。from pathlib import Path data_dir Path(data) xlsx_files sorted(data_dir.glob(*.xlsx)) csv_files sorted(data_dir.glob(*.csv))注意我用了sorted这一步不是可有可无。不同操作系统上glob返回的文件顺序可能不一样如果不排序合并出来的行顺序每次可能都不同排查时也很容易被误导。排一下序每次跑出来的结果一致逻辑上更可控。如果文件放在多级子目录里用rglob递归遍历all_files sorted(data_dir.rglob(*.*)) files [p for p in all_files if p.suffix in (.xlsx, .xls, .csv)]这种写法会把当前目录下所有符合条件的文件全部捞出来适合文件归档比较深的场景。2.2 按文件名模式筛选目标文件实际业务经常只要“包含销售回款”的文件或者只要某几个日期段的数据。在列表推导式里直接过滤就行targets [p for p in xlsx_files if 销售 in p.stem]p.stem是去掉后缀的文件名。如果文件名本身有规律比如“2025-01-销售.xlsx”还可以用正则把日期提取出来再按日期区间过滤。这块看具体需求但思路是一致的优先在路径层面缩小处理范围而不是把文件全读进来再筛选省内存也省时间。2.3 Excel读取时强制指定类型防丢精度的一招这一步是我最想强调的。Excel里很多列看着是数字实际上是文本比如订单编号、身份证号、电话号甚至经纬度。pandas默认会自动推断类型经常把“001”读成“1”把超过15位的数字尾部变成0而这些数据一旦读错后面怎么清洗都救不回来。解决办法是读取时用dtype强制指定列的类型df pd.read_excel( file_path, engineopenpyxl, dtype{订单编号: str, 客户编码: str, 经纬度: str, 创建时间: str} )把容易出问题的列统一按字符串读进来再根据实际需要做后续转换这是“先读取后清洗”思路的关键一步。很多坐标点错位、编号溢出的问题源头就在这个读取阶段。2.4 CSV读取时关于编码和分隔符的兼容方案CSV文件最折磨人的两件事一是编码二是分隔符。中文环境的CSV常见三种编码utf-8、utf-8-sig带BOM、gbk。乱码往往不是文件坏了是编码猜错了。最稳妥的办法是尝试多个编码直到成功for enc in (utf-8-sig, gbk, utf-8): try: df pd.read_csv(file_path, encodingenc) break except UnicodeDecodeError: continue分隔符问题也一样。有些系统导出的CSV用逗号有些用分号有些用制表符甚至同一个文件里既有逗号又有分号。想兼容多种情况可以直接用正则df pd.read_csv(file_path, sepr\s*[,;]\s*, enginepython)这招在接手来历不明的CSV时特别管用。不管你是处理电力负荷数据、气象CSV还是某个手机价格预测.csv的数据集读取阶段把编码和分隔符这两关过了后面的分析才有意义。3. 真正折磨人的是单元格细节公式、文本数字和坐标精度3.1 data_onlyTrue只是看起来简单其实有条件如果你处理的Excel里有公式比如每张分表里“合计”列是算出来的用openpyxl直接读通常拿不到值。load_workbook默认把公式作为字符串读进来只有当文件之前被Excel或其他表格软件打开并保存过、缓存了计算结果load_workbook(data_onlyTrue)才读得到数值。from openpyxl import load_workbook wb load_workbook(file_path, data_onlyTrue) ws wb.active print(ws[H10].value)如果发现data_onlyTrue读出来是None别慌八成是文件生成时没有计算过公式。这种情况在程序自动生成的Excel里特别常见因为生成工具只写了公式没有触发重算。最稳妥的方案是能用pandas的场景尽量不用openpyxl读公式值必须读缓存值时先用Excel把源文件打开保存一次或者要求源头直接输出数值版本。处理公式还有个思路是直接从源头上绕开如果你是脚本生成Excel写入的本来就是计算后的数值那别人读取时就不会遇到公式问题。这条经验在处理“由程序导出、再由程序导入”的表格时很实用。3.2 文本型数字、千分符和科学计数法Excel里左上角带绿三角的“文本数字”到pandas里就是字符串。对这种字符串转数字不能直接astype得先去分隔符和单位。def clean_numeric(series): s series.astype(str).str.replace(,, , regexFalse) s s.str.replace(元, , regexFalse).str.strip() return pd.to_numeric(s, errorscoerce)errorscoerce表示转不过去的变成NaN这样后续能直观看到哪行数据有问题而不是直接让整个脚本崩掉。这种“数字列里混了千分符”的情况在财务导出文件里简直是大头。我经常看到有人把列转成字符串之后直接去掉逗号但忘了同一列里可能还有“元”“¥”这种单位后缀。清洗的时候多看一眼实际数据长什么样比无脑replace要靠谱。科学计数法也是一个高频问题身份证号、长数字读进Excel会显示成1.23457E17。根源是Excel的单元格格式和浮点数精度限制。Python这边把列指定为字符串通常能缓解但如果最终输出Excel给别人建议把这类列在Excel里设成文本格式再写入否则表格一打开又是科学计数法对方还是会来问你。3.3 经纬度和ID列精度丢失一次坐标点错位的排查有几次做地图相关数据时我发现Excel里存的经纬度被pandas读出来以后最后几位全变了比如106.714328读成106.71432799999999。原因是Excel数字默认按双精度浮点存储15位有效数字内一般没事但经纬度要精确到6位小数时浮点误差就可能让坐标点位置不对。如果你在arcmap或GIS工具里导入Excel坐标点发现点位偏移、聚集到一个奇怪的位置经常不是软件操作问题而是导入时经纬度列被当作数值、发生了精度舍入。解决方案很简单读取时把经纬度列指定为字符串或者用Decimal保存不让pandas做浮点数推断。df pd.read_excel( points.xlsx, dtype{lng: str, lat: str} )这样坐标点的值才会原样保留后续要算距离、画图时再转float也不迟。记住一个原则像编号、坐标、电话号码这种“看起来像数字但不能丢精度”的列一律先当字符串读。3.4 合并单元格和“提取拼音”这类文本操作批量清洗数据时合并单元格是隐藏大坑。pandas读Excel不会自动把合并单元格展开会得到一部分NaN。遇到连续的合并单元格可以用前向填充处理df[[区域]] df[[区域]].fillna(methodffill)这个办法只对连续区域的合并有效。如果表格结构本身很乱我建议让业务方提供标准的二维表而不是自己花半小时去猜合并规则。批量处理的终极价值是省时间不是用来跟糟糕的表格结构较劲。至于提取拼音、拼音首字母这类需求用pypinyin三行代码就能解决常在姓名、单位名整理时用到。不过它和表格批处理是两件事这里不展开。真正高频的文本处理还是字段拆分比如把“北京市朝阳区XX路”拆成省市区df[[省, 市, 区]] df[地址].str.split(n2, expandTrue).iloc[:, :3]这种操作配合批量读取能省掉大量手工复制粘贴。4. Excel和CSV互转的边界问题编码、分隔符与导库前置准备4.1 写CSV后在Excel打开乱码根源是编码标记一个经典场景用Python把处理结果写成CSV客户双击打开Excel所有中文变成乱码。原因不是保存错了而是Excel默认用系统ANSI代码页打开CSVWindows中文环境默认是GBK你保存成标准UTF-8Excel没识别出来。解决方案保存CSV时用utf-8-sig编码也就是带BOMExcel就能正确识别。df.to_csv(result.csv, indexFalse, encodingutf-8-sig)另一个细节是indexFalse不然第一列会多出一列行号几乎人人踩过。这个顺手写成习惯就不会忘。4.2 给DBeaver、SQL Server导入CSV时的前置准备如果你要把CSV导入到SQL Server、DBeaver、MySQL这类系统情况又不太一样。数据库连接工具通常按你指定的编码和分隔符读CSV导入报错时优先检查三样编码、首行标题、分隔符。工具或场景常见问题对策SQL Server导入CSV中文乱码指定Codepage65001对应UTF-8DBeaver导入CSV列错位、列名变第一行检查Header和Delimiter设置是否匹配各种ETL工具长文本/换行破坏字段结构用csv模块的writer生成标准转义格式SQL Server导入时如果中文是UTF-8要选Codepage65001乱码时换936重试。DBeaver导入对话框里“Header”和“Delimiter”选项要跟文件实际情况一一对应错一个导进去就会乱。有些系统一定要分号或制表符当分隔符导出时用sep;或sep\t生成导入成功率会高很多。有些CSV里包含长文本和换行常规的字符串拼接会破坏字段结构最好用csv模块的writer它会正确处理引号转义import csv with open(export.csv, w, newline, encodingutf-8-sig) as f: writer csv.writer(f) writer.writerows(rows)4.3 大批量CSV合并进同一个Excel文件把几百个CSV合并到一个Excel很多人想的是循环逐个to_excel其实更高效的是先逐个读进DataFrame统一列名后写入同一个Excel的不同Sheetimport pandas as pd from pathlib import Path out_path merged.xlsx with pd.ExcelWriter(out_path, engineopenpyxl) as writer: for i, f in enumerate(sorted(Path(csv_dir).glob(*.csv)), 1): df pd.read_csv(f, encodingutf-8-sig) df.to_excel(writer, sheet_namef表{i}, indexFalse)这个模式在处理“一个文件夹几十个CSV想集中到一个Excel里发给别人”时特别好用。Sheet名直接用表1、表2别人打开也不用一个个找文件。4.4 “外部表不是预期的格式”到底是怎么回事还有一类非常常见的报错叫“外部表不是预期的格式”通常出现在你试图用数据库工具或外部程序加载一个Excel文件时。最常见的原因有两个。第一个是扩展名和实际文件格式不一致文件内容其实是CSV或HTML表格但扩展名被改成了.xlsx工具按Excel格式去解析自然失败。第二个是xls和xlsx混用工具预期xlsx你给的是老xls。解决思路很简单不要只看扩展名先打开文件确认真实格式或者用Python读一遍看pandas能不能识别。识别不了就别强行导入先转换格式。我自己的习惯是遇到这种问题先写两行代码with open(problem.xlsx, rb) as f: print(f.read(8))如果是xlsx文件头是PK如果是CSV开头直接就是文本内容。这一步十秒钟就能判断文件真实格式比在工具里反复换配置快得多。5. 批量合并与拆分高频需求的两种标准写法5.1 多Excel文件按列合并关键是列名统一批量处理里出现频率最高的需求之一就是多个Excel合并成一个。直接看代码import pandas as pd from pathlib import Path standard_cols [日期, 区域, 销售额, 备注] all_parts [] for f in sorted(Path(reports).glob(*.xlsx)): df pd.read_excel(f, engineopenpyxl, dtypestr) df.columns [c.strip() for c in df.columns] # 统一列名缺失的列补上多出的列丢弃 for c in standard_cols: if c not in df.columns: df[c] pd.NA df df[standard_cols] # 把销售额统一成数值 df[销售额] pd.to_numeric(df[销售额], errorscoerce) all_parts.append(df) result pd.concat(all_parts, ignore_indexTrue) result.to_excel(汇总.xlsx, indexFalse)关键点有两个。一是先做列名统一如果20个文件里有两个列名不一致concat会直接多出两列而不是报错最后汇总表结构就乱了。二是所有文件统一用dtypestr读进来避免同一列在这个文件是数字、在那个文件是文本时类型合并出错。这两点做到了合并基本不会出大问题。5.2 按列值拆分文件名的非法字符必须处理反过来把一个“总表”按区域、按月份拆成多个文件用groupby就行。by_area result.groupby(区域, dropnaFalse) for area, group in by_area: safe str(area).replace(/, _).replace(\\, _) group.to_excel(f分表_{safe}.xlsx, indexFalse)文件名的清洗必须做。因为你根本不知道区域名里会出现什么字符Windows的文件名不允许/、\、:、*、?、、、、|任何一个都有可能导致to_excel报错。更稳的做法是用列表推导式把所有非法字符一并替换而不是只替换一两个。5.3 一个Excel拆成多个CSVsheet_nameNone真的省事还有一种常见需求一个Excel里有12个月每个Sheet一个月度数据需要批量拆成12个CSV。用sheet_nameNone可以一次性把所有Sheet读成一个字典excel_file 2025年数据.xlsx sheets pd.read_excel(excel_file, sheet_nameNone, engineopenpyxl) for name, df in sheets.items(): safe_name str(name).strip().replace( , _) df.to_csv(fby_sheet_{safe_name}.csv, indexFalse, encodingutf-8-sig)pd.read_excel(sheet_nameNone)返回的字典key是Sheet名value是DataFrame这个接口在处理多Sheet场景时真的省事。配合前面的按列拆分基本可以覆盖日常70%以上的合并拆分需求。6. 让脚本稳定跑完报错速查、日志追踪与函数封装6.1 高频报错与对策速查批量处理过程中有几个报错我遇到的频率最高整理成一张表方便你对照。报错现场可能原因处理建议Missing optional dependency openpyxlpandas读Excel缺引擎pip install openpyxlUnicodeDecodeErrorCSV编码猜错依次尝试utf-8-sig、gbkBad line / Error tokenizing data分隔符选错或脏数据多列用sep正则自动识别或先看文件头部外部表不是预期的格式文件格式和扩展名不一致确认真实格式先另存为xlsx再导入data_onlyTrue读到None公式没有缓存值用Excel打开保存一次或要求源头给值拿到报错时先别急着改代码打印前几行数据看看print(df.head()) print(df.dtypes)大部分问题在数据层面就能看出来比如列错位、值变成NaN、类型不对。定位到具体问题再改逻辑比盲目重跑更高效。6.2 批量处理必须加日志不然出事没法查批量处理最忌讳的是跑到第50个文件报错前面的49个白做又不知道是哪个文件出的问题。所以脚本一开头就要加日志。import logging logging.basicConfig( filenamebatch.log, levellogging.INFO, format%(asctime)s %(levelname)s %(message)s, encodingutf-8 ) for f in files: try: df pd.read_excel(f, engineopenpyxl) # 处理逻辑 logging.info(f完成: {f.name}, 行数{len(df)}) except Exception as e: logging.exception(f失败: {f.name}, 原因{e}) continuelogging.exception会在日志里自动带上堆栈信息排查时非常有用。单个文件失败不中断整体是稳定批量脚本的底线要求。任何批量处理都要让程序具备“坏一个文件不影响其他文件”的能力。6.3 把脚本封装成可复用函数处理同样一批文件达到三次以上就该考虑封装了。我通常把“读取—清洗—输出”变成三个函数再写一个main串起来。def load_file(path): if path.suffix .csv: return pd.read_csv(path, encodingutf-8-sig, dtypestr) return pd.read_excel(path, engineopenpyxl, dtypestr) def clean_data(df): # 统一列名、清理文本、类型转换 return df def save_output(df, out_path): df.to_excel(out_path, indexFalse) def main(): for path in get_target_files(): try: df load_file(path) df clean_data(df) save_output(df, out_path_for(path)) logging.info(f完成: {path.name}) except Exception as e: logging.exception(f失败: {path.name}, 原因{e}) if __name__ __main__: main()这样后面清洗规则变了只改clean_data一个函数其他部分不用动。批量处理Excel和CSV的脚本想要长期复用这个封装习惯越早养成越好。最后再分享一个小细节我习惯在脚本输出目录里放一个readme.txt记录这份数据是什么时候生成的、脚本在哪里、清洗逻辑是什么。三个月后再打开这个目录你会感谢当初的自己。批量处理Excel和CSV的脚本从来不是跑完就结束的东西能维护、能复现、能解释才算真正交付。