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

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.

CodePudding user response：

Each csv as seperate excel file

import glob
import pandas as pd
import os

csv_files = glob.glob('*.csv')
for filename in csv_files:
    sheet_name = os.path.split(filename)[-1].replace('.csv', '.xlsx')
    df = pd.read_csv(filename)
    df.to_excel(sheet_name, index=False)

All csv in same excel in different sheet

import glob
import pandas as pd
import os

# Create excel file
writer = pd.ExcelWriter('all_csv.xlsx')

csv_files = glob.glob('*.csv')
for filename in csv_files:
    sheet_name = os.path.split(filename)[-1].replace('.csv', '')
    df = pd.read_csv(filename)
    # Append each csv as sheet
    df.to_excel(writer, sheet_name=sheet_name, index=False)
writer.save()

CodePudding user response：

Hi Annachiara welcome to StackOverflow,

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=';')

CodePudding user response：

Assuming you would like to keep the same structure of your code, I just fixed some technical issues in your code to make it work (please change the folders path to your own):

import csv

import openpyxl as xl

import glob, os, os.path

directory= 'input'

folder= '../output' # Since 'input' would be my cwd, need to step back a directory to reach 'output'

# Using your function, just passing different arguments for convinient.
def csv_to_excel(f_path, f_name):

    csv_data=[]

    with open(f_path, 'r') 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, f_name   ".xlsx"))


def main():
    os.chdir(directory) # Defining input directory as your cwd
    # Searching for all files with csv extention and sending each to your function
    for file in glob.glob("*.csv"):
        f_path = os.getcwd()   '\\'   file # Saving the absolute path to the file
        f_name = (os.path.splitext(file)[0]) # Saving the name of the file
        csv_to_excel(f_path, f_name) 

if __name__=="__main__":
    main()

P.S: Please avoid iterating a definition of a function since you only need to define a function once.