How to automatically name Excel files just generated from csv files with Python

Question

I need to transform csv files into Excel files in an automatic way. I am failing in naming Excel files with the name of the corresponding csv file. I saved csv files as 'Trials_1', 'Trials_2', Trilas_3' but with the code that I wrote Python gives me an error and asks me for csv file named 'Trials_4'. Then, if I rename csv file 'Trials_1' into 'Trials_4' the program works and generates an Excel file named 'Trials_1'. How can I correct my code?

'''

import csv

import openpyxl as xl

import os, os.path

directory=r'C:\\Users\\PycharmProjects\\input\\'

folder=r'C:\\Users\\PycharmProjects\\output\\'

for csv_file in os.listdir(directory):

def csv_to_excel(csv_file, excel_file):

    csv_data=[]

    with open(os.path.join(directory, csv_file)) as file_obj:

        reader=csv.reader(file_obj)

        for row in reader:

            csv_data.append(row)

    workbook= xl.Workbook()

    sheet=workbook.active

    for row in csv_data:

        sheet.append(row)

        workbook.save(os.path.join(folder,excel_file))


if __name__=="__main__":
    m = sum(1 for f in os.listdir(directory) if os.path.isfile(os.path.join(directory, f)))
    new_name = "{}Trial_{}.csv".format(directory, m + 1)
    k = sum(1 for file in os.listdir(folder) if os.path.isfile(os.path.join(folder, file)))
    new_name_e = "{}Trial_{}.xlsx".format(folder, k + 1)
    csv_to_excel(new_name,new_name_e)

'''

Thanks.

Answer 1

I would modify the "csv_to_excel" function by using only pandas.

Before that you should install 'xlsxwriter' with:

pip install XlsxWriter

Then the function would be like this:

def csv_to_excel(csv_file,excel_file,csv_sep=';'):

    # read the csv file with pandas
    df=pd.read_csv(csv_file,sep=csv_sep)
    # create the excel file
    writer=pd.ExcelWriter(excel_file, engine='xlsxwriter')
    # copy the csv content (df) into the excel file
    df.to_excel(writer,index=False)
    # save the excel file
    writer.save()
    # print what you converted for reference
    print(f'csv file {csv_file} saved as excel in {excel_file}')

Just only make sure that the csv is read correctly: I added just the separator parameter, but you might want to add all the other parameters (like parse dates etc.)

Then you can convert the list of csv files with a for loop (I used more steps to make it clearer)

dir_in=r'C:\\Users\\PycharmProjects\\input\\'

dir_out=r'C:\\Users\\PycharmProjects\\output\\'

csvs_to_convert=os.listdir(dir_in)

for csv_file_in in csvs_to_convert:
    
    # remove extension from csv files
    file_name_no_extension=os.path.splitext(csv_file_in)[0]
    # add excel extension .xlsx
    excel_name_out=file_name_no_extension+'.xlsx'
    # write names with their directories
    complete_excel_name_out=os.path.join(dir_out,excel_name_out)
    complete_csv_name_in=os.path.join(dir_in,csv_file_in)
    # convert csv file to excel file
    csv_to_excel(complete_csv_name_in,complete_excel_name_out,csv_sep=';')