如何用Python修复因字段含逗号导致列数不一致的Excel文件

问题分析 #

你遇到的是转格式时的字段拆分问题：原ODS文件中ProductName字段包含逗号（比如reg (.com,.net)），转成XLSX后，这些逗号被错误识别为列分隔符，导致原本属于同一单元格的内容被拆成多列，最终读入DataFrame时列数混乱。

自动化修复方案 #

下面提供两种实用的修复思路，针对你的大文件场景（30-50万行/文件）优化了内存占用：

方法一：逐行读取合并错位字段（推荐）

利用openpyxl的只读模式逐行读取文件，手动识别并合并被拆分的ProductName字段，保证每行列数统一为4列。

import glob
import pandas as pd
from openpyxl import load_workbook

# 定义目标列名（与原表头一致）
TARGET_COLS = ['ProductName', 'Product Code', 'Term', 'Amnt']
TARGET_COL_COUNT = len(TARGET_COLS)

def fix_and_read_excel(file_path):
    # 以只读模式加载Excel，节省内存
    wb = load_workbook(file_path, read_only=True)
    ws = wb.active
    rows = []
    
    # 跳过表头行（如果你的文件表头在第一行）
    next(ws)
    
    for row in ws:
        # 提取当前行所有单元格的值
        cell_vals = [cell.value for cell in row]
        # 统计非空单元格数量
        non_empty_count = len([v for v in cell_vals if v is not None])
        
        if non_empty_count > TARGET_COL_COUNT:
            # 计算需要合并的列数：多余的列数+1（因为要合并回ProductName）
            merge_cols = non_empty_count - TARGET_COL_COUNT + 1
            # 合并前面的列作为ProductName，空值自动忽略
            merged_name = ' '.join([str(v) for v in cell_vals[:merge_cols] if v is not None])
            # 提取剩余的列，补空值到目标列数
            rest_cols = cell_vals[merge_cols:merge_cols + TARGET_COL_COUNT - 1]
            rest_cols += [None] * (TARGET_COL_COUNT - 1 - len(rest_cols))
            new_row = [merged_name] + rest_cols
        elif non_empty_count < TARGET_COL_COUNT:
            # 列数不足时补空值
            new_row = cell_vals + [None] * (TARGET_COL_COUNT - non_empty_count)
        else:
            new_row = cell_vals
        
        rows.append(new_row)
    
    # 转成DataFrame并指定列名
    return pd.DataFrame(rows, columns=TARGET_COLS)

# 批量处理所有文件
data_frame_list = []
files_in_folder = glob.glob('drive/MyDrive/partialdataset/*')

for file in files_in_folder:
    print(f"处理文件：{file}")
    df_fixed = fix_and_read_excel(file)
    data_frame_list.append(df_fixed)

# 合并所有DataFrame
final_df = pd.concat(data_frame_list, ignore_index=True)
final_df

说明：

• 只读模式加载Excel避免了大文件内存溢出问题
• 合并逻辑默认针对ProductName字段，如果你的错位字段是其他列，只需调整merge_cols的计算和合并位置即可
• 自动处理列数不足的行，补全空值保证列一致性

方法二：尝试以CSV格式读取（如果转格式时实际存为CSV）

如果你的XLSX文件其实是CSV文件改了后缀（比如转格式时错误选择了逗号分隔），可以直接用pandas的CSV读取规则，自动识别引号内的逗号：

import glob
import pandas as pd

TARGET_COLS = ['ProductName', 'Product Code', 'Term', 'Amnt']

data_frame_list = []
files_in_folder = glob.glob('drive/MyDrive/partialdataset/*')

for file in files_in_folder:
    print(f"处理文件：{file}")
    # 用CSV规则读取，引号内的逗号不会被当作分隔符
    df = pd.read_csv(file, sep=',', quotechar='"', header=0, names=TARGET_COLS)
    data_frame_list.append(df)

final_df = pd.concat(data_frame_list, ignore_index=True)
final_df

说明：这种方法更高效，但仅适用于转格式时实际生成的是CSV内容的情况，如果是标准XLSX文件则无效。

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