CSV转Excel后无法删除原CSV文件：文件被占用问题求助

问题场景 #

将多个CSV文件合并到Excel工作簿后，尝试删除原CSV文件时报错：

The process cannot access the file because it is being used by another process!

已尝试在执行os.remove前手动关闭文件，但问题依然存在。原代码及运行结果如下：

原代码：

# importing pandas
#importing os
import pandas as pd
import os


csv_1 = open('SearchResults.csv', 'r')

csv_2 = open('SearchResults (1).csv', 'r')

csv_3 = open('SearchResults (2).csv', 'r')
  
writer = pd.ExcelWriter('DB_1.xlsx', engine='xlsxwriter')
# merging three csv files
df = pd.concat(map(pd.read_csv,[csv_1,csv_2,csv_3]), ignore_index=True)

#Exports csv files to excel sheet on DB_1.xlsx
df.to_excel(writer, sheet_name='sheetname')

csv_1.close()
csv_2.close()
csv_3.close()

writer.save()

try:
    os.remove('SearchResults.csv')
    print("The file: {} is deleted!".format('SearchResults.csv'))
except OSError as e:
    print("Error: {} - {}!".format(e.filename, e.strerror))
    

try:
    os.remove('SearchResults (1).csv')
    print("The file: {} is deleted!".format('SearchResults (1).csv'))
except OSError as e:
    print("Error: {} - {}!".format(e.filename, e.strerror))
    
try:
    os.remove('SearchResults (2).csv')
    print("The file: {} is deleted!".format('SearchResults (2).csv'))
except OSError as e:
    print("Error: {} - {}!".format(e.filename, e.strerror))

运行结果：

Error: SearchResults.csv - The process cannot access the file because it is being used by another process!
Error: SearchResults (1).csv - The process cannot access the file because it is being used by another process!
Error: SearchResults (2).csv - The process cannot access the file because it is being used by another process!

问题原因 #

手动打开文件对象并传给pd.read_csv后，pandas内部可能仍持有文件句柄未释放，即便手动调用close()，也无法确保系统层面完全释放文件资源，导致删除时提示文件被占用。

解决方案 #

方案1：直接传递文件名给pandas（推荐）

让pandas自行处理文件的打开与关闭，能确保资源正确释放：

import pandas as pd
import os

# 直接传入文件名列表，pandas自动管理文件生命周期
df = pd.concat(
    map(pd.read_csv, ['SearchResults.csv', 'SearchResults (1).csv', 'SearchResults (2).csv']),
    ignore_index=True
)

# 使用with语句管理ExcelWriter，自动完成保存与资源释放
with pd.ExcelWriter('DB_1.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='sheetname')

# 批量删除文件
files_to_delete = ['SearchResults.csv', 'SearchResults (1).csv', 'SearchResults (2).csv']
for file in files_to_delete:
    try:
        os.remove(file)
        print(f"文件: {file} 已删除!")
    except OSError as e:
        print(f"错误: {e.filename} - {e.strerror}!")

方案2：用with语句管理文件对象

如果需要手动操作文件对象，使用with语句可自动确保文件读取完成后立即关闭：

import pandas as pd
import os

dfs = []
# 逐个读取文件，with语句自动关闭文件
for file_path in ['SearchResults.csv', 'SearchResults (1).csv', 'SearchResults (2).csv']:
    with open(file_path, 'r') as f:
        dfs.append(pd.read_csv(f))

df = pd.concat(dfs, ignore_index=True)

with pd.ExcelWriter('DB_1.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='sheetname')

# 执行删除
for file_path in ['SearchResults.csv', 'SearchResults (1).csv', 'SearchResults (2).csv']:
    try:
        os.remove(file_path)
        print(f"文件: {file_path} 已删除!")
    except OSError as e:
        print(f"错误: {e.filename} - {e.strerror}!")

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