CSV file data to Excel (Remove csv file after workbook save not working)-CodePudding

I'm having trouble removing the csv file after the data is integrated into the workbook. I'm getting a message

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

and I tried closing the file before I am applying the os.remove syntax to my code. I am curretly stuck in what I should do. I've tried a few methods, but the end statement keeps popping up.

# 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))

#Results:

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!

CodePudding user response：

Unless you need to do line by line operations on your three .csv files (and it doesn't seem to be the case here), there is no need to use python's built-in funciton open with pandas.read_csv.

Try this :

import pandas as pd
import os

csv_1 = 'SearchResults.csv'
csv_2 = 'SearchResults (1).csv'
csv_3 = 'SearchResults (2).csv'
  
# merging three csv files
df = pd.concat(map(pd.read_csv,[csv_1,csv_2,csv_3]), ignore_index=True)
    
with pd.ExcelWriter('DB_1.xlsx', engine='xlsxwriter') as writer :
    #Exports csv files to excel sheet on DB_1.xlsx
    df.to_excel(writer, sheet_name='sheetname')

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))

CodePudding user response：

Using pathlib.glob to find all files, concatenate the csv files with a generator to excel. Finally delete csv files.

import contextlib
from pathlib import Path

import pandas as pd


def concatenate_csvs(path: str) -> None:
    pd.concat(
        (pd.read_csv(x) for x in Path(f"{path}/").glob("SearchResults*.csv")), ignore_index=True
    ).to_excel(f"{path}/DB_1.xlsx", index=False, sheet_name="sheetname")

    with contextlib.suppress(PermissionError):
        [Path(x).unlink() for x in Path(f"{path}/").glob("SearchResults*.csv")]


concatenate_csvs("/path/to/files")