Trouble closing a csv to delete it

Question

I've got a daily task that is a perfect candidate for automation so I decided to use it as a project to learn Python. What I need to do is convert a few .xlsx files to .csv and then email each to a specific email address.

Below is what I've got and it works just fine up until near the end. I want it to delete the csv copies after they've been sent. File1.csv gets deleted but file2.csv does not because it is still open in another process.

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\Drop\file2.csv'

So it's clear the csv needs to be closed but I just can't manage to figure out which process has it open and how to close it.

import os
from datetime import datetime
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders

files = []

drop_path = 'C:\\Data Drop\\'
path = os.chdir(drop_path)
datestamp =  datetime.now().strftime(' (%m-%d-%Y)')

#Make a CSV copy of each file
for c in os.listdir(path):
    file_name, file_ext = os.path.splitext(c)
    xlsx = pd.read_excel(file_name+file_ext)
    xlsx.to_csv(file_name+'.csv', encoding='utf-8')
    files.append(file_name+'.csv')

print('CSV copies created\n')

#Send to appropriate email addresses
recipient = ''
for s in files:

    print('Sending ',s)

    if s == 'file1.csv':
        recipient = '<email1@gmail.com>'
    elif s == 'file2.csv':
        recipient = '<email2@gmail.com>'

    email_user = 'sender@gmail.com'
    email_password = 'password'
    email_send = recipient

    msg = MIMEMultipart()
    msg['From'] = email_user
    msg['To'] = email_send
    msg['Subject'] = "Data transmittal"

    body = 'Data transmittal attached'
    msg.attach(MIMEText(body,'plain'))

    attached_file = s
    attachment  = open(attached_file,'rb')

    part = MIMEBase('application','octet-stream')
    part.set_payload((attachment).read())
    encoders.encode_base64(part)
    part.add_header('Content-Disposition',"attachment; filename= "+attached_file)

    msg.attach(part)
    text = msg.as_string()
    server = smtplib.SMTP('smtp.gmail.com',587)
    server.starttls()
    server.login(email_user,email_password)


    server.sendmail(email_user,email_send,text)
    print(s,'sent.\n')
    server.quit()

print('All data has been sent.\n')

#Remove CSV files once sent.
for files in os.listdir(drop_path):
    if files.endswith('.csv'):
        os.remove(drop_path + files)

print('CSV files cleared.\n')

#Add the date to the end of each xlsx file name
for f in os.listdir(path):
    file_name, file_ext = os.path.splitext(f)
    if file_ext==".csv":
        continue
    else:
        new_name = file_name+datestamp+file_ext
        os.rename(f, new_name)

print('Dates added to file names.\n')

print('\nAll operations are complete.')

Answer 1

your error is opening attachment and never closing it.

instead of : attachment = open(attached_file,'rb')

use with open() context manager:

with open(attached_file,'rb') as attachment:
    part = MIMEBase('application','octet-stream')
    part.set_payload((attachment).read())
# here attachment is closed automatically
encoders.encode_base64(part)
part.add_header('Content-Disposition',"attachment; filename= "+attached_file)