Python处理CSV转Excel时占用8GB内存且无法完成，求解决方案

我用以下Python代码把四个CSV文件的数据写入现有Excel文件，但运行时内存占了8GB，还一直完不成：

import openpyxl
import pandas as pd

def copy_csv_to_xlsx(shipment_all, shipment_truck, stockflow_all, stockflow_truck, xlsx_file):

    shipment_all_df = pd.read_csv(shipment_all)
    shipment_truck_df = pd.read_csv(shipment_truck)
    stockflow_all_df = pd.read_csv(stockflow_all)
    stockflow_truck_df = pd.read_csv(stockflow_truck)
    
    
    wb = openpyxl.load_workbook(xlsx_file)
    
    
    ws = wb["0. Shipment"]
    ws.append(shipment_all_df.columns)
    for row in shipment_all_df.itertuples(index=False):
        ws.append(row)
    
    ws = wb["0. Truck Shipment"]
    ws.append(shipment_shave_care_df.columns)
    for row in shipment_shave_care_df.itertuples(index=False):
        ws.append(row)
        
    ws = wb["0. SF"]
    ws.append(stockflow_all_df.columns)
    for row in stockflow_all_df.itertuples(index=False):
        ws.append(row)
        
    ws = wb["0. Truck SF"]
    ws.append(stockflow_truck_df.columns)
    for row in stockflow_truck_df.itertuples(index=False):
        ws.append(row)
    
    
    wb.save(xlsx_file)
    

copy_csv_to_xlsx("Shipment All.csv", "Shipment Trucks.csv", "Stockflow All.csv", "Stockflow Truck.csv", "Business Split Data.xlsx")

原因分析 #

• 一次性加载全部CSV到内存：如果四个CSV数据量很大，直接用pd.read_csv全读进DataFrame会瞬间占满内存，8GB内存被占用就是这个核心原因。
• openpyxl逐行写入效率极低：用itertuples循环逐行调用ws.append()，每一次append都要操作Excel对象，大文件下会产生巨量IO和内存开销，导致程序卡死。
• 代码存在变量名错误：代码里的shipment_shave_care_df是未定义的，应该是shipment_truck_df，这个错误会直接导致程序报错，若你实际运行的是修正后的版本，主要问题还是前两点。

解决方法 #

1. 用pandas直接写入Excel（最优方案） #

pandas的ExcelWriter可以直接将DataFrame写入指定工作表，效率比openpyxl逐行写高几个量级，还能保留Excel原有内容，通过mode='a'和if_sheet_exists='replace'覆盖目标工作表即可。

2. 分块读取大CSV（超大数据量时补充） #

如果CSV文件大到单块加载仍占内存，用chunksize参数分批读取，再逐块写入Excel，避免一次性占满内存。

3. 修正变量名错误 #

把代码里的shipment_shave_care_df全部替换成shipment_truck_df。

优化后的代码 #

常规数据量版本 #

import pandas as pd

def copy_csv_to_xlsx(shipment_all, shipment_truck, stockflow_all, stockflow_truck, xlsx_file):
    # 打开现有Excel，指定追加模式并替换目标工作表
    with pd.ExcelWriter(xlsx_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        # 直接写入每个CSV到对应工作表
        pd.read_csv(shipment_all).to_excel(writer, sheet_name="0. Shipment", index=False)
        pd.read_csv(shipment_truck).to_excel(writer, sheet_name="0. Truck Shipment", index=False)
        pd.read_csv(stockflow_all).to_excel(writer, sheet_name="0. SF", index=False)
        pd.read_csv(stockflow_truck).to_excel(writer, sheet_name="0. Truck SF", index=False)

copy_csv_to_xlsx("Shipment All.csv", "Shipment Trucks.csv", "Stockflow All.csv", "Stockflow Truck.csv", "Business Split Data.xlsx")

超大数据量分块版本 #

如果单个CSV文件超过100万行，用分块读取避免内存溢出：

import pandas as pd

def write_csv_to_sheet(writer, csv_path, sheet_name):
    # 每块读取10000行，可根据内存调整
    chunk_size = 10000
    first_chunk = True
    for chunk in pd.read_csv(csv_path, chunksize=chunk_size):
        # 仅第一块写入表头
        chunk.to_excel(writer, sheet_name=sheet_name, index=False, header=first_chunk)
        first_chunk = False

def copy_csv_to_xlsx(shipment_all, shipment_truck, stockflow_all, stockflow_truck, xlsx_file):
    with pd.ExcelWriter(xlsx_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        write_csv_to_sheet(writer, shipment_all, "0. Shipment")
        write_csv_to_sheet(writer, shipment_truck, "0. Truck Shipment")
        write_csv_to_sheet(writer, stockflow_all, "0. SF")
        write_csv_to_sheet(writer, stockflow_truck, "0. Truck SF")

copy_csv_to_xlsx("Shipment All.csv", "Shipment Trucks.csv", "Stockflow All.csv", "Stockflow Truck.csv", "Business Split Data.xlsx")

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