pandas报Error tokenizing data字段数不匹配错误排查方案

核心报错根因 #

触发Error tokenizing data. C error: Expected 5 fields in line 93, saw 8的根本原因是使用读取CSV纯文本文件的pd.read_csv()方法读取二进制格式的.xlsx Excel文件，两种文件的解析逻辑完全不兼容：

• read_csv会把xlsx的二进制压缩内容按纯文本规则拆分，解析出来的内容包含大量乱码，自然会出现字段数不匹配的报错，和Excel里存储的业务数据没有任何关系，不需要浪费时间修改Excel第93行的内容
• 原代码里给read_csv传的sep=','、encoding='cp437'参数都是针对CSV文本文件的，对xlsx格式完全无效

其余待修复的隐藏问题 #

原代码除了读取方法错误，还有三个会触发后续报错的问题：

1. INSERT写入语句语法错误：INSERT的字段列表里不需要写nvarchar(10) primary key、int这类类型定义，这类定义只在CREATE TABLE阶段使用，插入时只需要传入字段名即可，否则执行插入时会报SQL语法错
2. 逐行循环插入性能极差，万行以上数据写入耗时会是批量写入的几十上百倍
3. 额外写df = pd.DataFrame(data)属于冗余代码，pandas的文件读取方法返回值本身就是DataFrame对象

前置依赖安装 #

先安装读取Excel和SQL连接需要的依赖，执行命令：

pip install pandas openpyxl pyodbc sqlalchemy

修复后完整可运行代码 #

import pandas as pd
import pyodbc
from sqlalchemy import create_engine

# 1. 正确读取Excel文件，使用read_excel方法
df = pd.read_excel(
    r"C:\Users\c.stembridge\OneDrive - NEWREST GROUP SERVICES\Overtime Forecast Report.xlsx",
    engine="openpyxl"
)

# 按需对齐列名，和目标SQL表字段一一对应，可根据Excel实际表头调整
df.columns = [
    "date", "day", "hrs", "dl", "catered", "hrs_diff_btwn_last_day",
    "catered_flight_diff", "employees_OT_count", "carriers", "full",
    "half", "total_carts"
]

# 2. 连接SQL Server建表
conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost;UID=SA;PWD=Working@2022;DATABASE=testdb;Encrypt=no;TrustServerCertificate=yes")
cursor = conn.cursor()

# 先判断表是否存在，避免重复建表报错
cursor.execute("IF OBJECT_ID('Overtime_Forecast', 'U') IS NOT NULL DROP TABLE Overtime_Forecast")
cursor.execute('''
        CREATE TABLE Overtime_Forecast (
            date nvarchar(10) primary key,
            day nvarchar(9),
            hrs int,
            dl int,
            catered int,
            hrs_diff_btwn_last_day int,
            catered_flight_diff int,
            employees_OT_count int,
            carriers int,
            full int,
            half int,
            total_carts int
        )
''')
conn.commit()

# 3. 批量写入数据，替代逐行循环插入
engine = create_engine("mssql+pyodbc://SA:Working@2022@localhost:1433/testdb?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=no&TrustServerCertificate=yes")
df.to_sql("Overtime_Forecast", engine, if_exists="append", index=False)

# 关闭连接
cursor.close()
conn.close()

特殊场景排查（实际使用CSV文件时） #

如果你是把Excel另存为CSV格式后再读取，仍然报字段数不匹配的错误，按以下步骤排查：

• 用记事本打开CSV文件，跳转到报错提示的对应行，检查该行是否存在未被双引号包裹的逗号（比如单元格内容里本身带逗号，会被识别成分隔符），导致拆分后的字段数和表头不一致
• 读取时临时加on_bad_lines='skip'参数跳过格式异常的行，先把文件读入后再定位异常数据：

  df = pd.read_csv("你的文件路径.csv", sep=',', encoding='utf-8', on_bad_lines='skip')
  

• 不要随意使用cp437编码，国内CSV文件常用编码为utf-8、gbk，可依次替换encoding参数测试读取效果

内容的提问来源于stack exchange，提问作者user18495643